Results 1 to 15 of 15

I need help for VBA code

  1. #1
    nicoboss is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Sep 2017
    Location
    http://www.webagency321.com
    Posts
    62

    Question I need help for VBA code

    Hello guys,


    I have an access app which is able to extract some data from specific csv files, to automatically create mdb files and to upload the data needed from the csv file in the mdb table.

    I have attached this access app : Nicoboss csv.zip

    To run it, you need to :
    1.download it.
    2.in the same folder where the access app is, create a subfolder named "csv" and another subfolder named "mdb"
    3.in the "csv" subfolder, place the A.csv I have attached.
    4.run the access app and press "Upload data". After a few minutes, it will create the mdb file required with the data needed.

    I need to update this access app so that it can work with a slightly different csv format.
    B.csv is the new format I need to process. There is one new column in this csv file : the second one.

    The only change I need is that in the current app, the 1st field is the concatenation of the 2 first fields from the A.csv file, using this kind of concatenation : "Field1"&"_"&Field2"
    Now I need the 1st field in the access table to be the concatenation of the 3 first fields from the B.csv file : "Field1"&"_"&Field2"&"_"&Field3"

    I have tried changing things like :
    strValues = "'" & rs1.fields(0).Name & "_" & rs1.fields(1).Name & "_" & rs1.fields(2).Name & "',"
    strquery = "Insert into [" & tablename1 & "] values ('" & rs1.fields(0).Value & "_" & rs1.fields(1).Value & "_" & rs1.fields(2).Value & "',"

    I am not sure this is good as I don't know anything about VBA, but it sounded logical to me.


    Anyway, it's not appropriate or it needs additional changes in the code because when I launch it with the new B.csv file, I get an error where this line is in yellow : strval = Str(rs1.fields(count1 - 1).Value)

    And now, I guess it only needs a small change, but I have no idea what I should do... Please HELP ! :-)

    Thank you for your help.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    45,160
    Don't know when i will be able to download file. Post the relevant code for analysis.
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  3. #3
    Gicu is offline Expert
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    516
    Have a look at the updated file, I think the problem was with your data (csv files) as they seem to have an empty row at the end. I changed your line from strval = Str(rs1.fields(count1 - 1).Value) to strval = Nz(Str(rs1.fields(count1 - 1).Value),0) and it seems to work OK now.

    Cheers,
    Vlad
    Attached Files Attached Files

  4. #4
    nicoboss is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Sep 2017
    Location
    http://www.webagency321.com
    Posts
    62
    Thank you very much Gicu !

  5. #5
    nicoboss is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Sep 2017
    Location
    http://www.webagency321.com
    Posts
    62
    hey guys,

    After Gicu's update, this access app works on my PC.
    But I realize it doesn't work on my laptop (!!!???)

    I get an error '13' (incompatibilité de type)

    The line in yellow is :
    strquery = strquery & CInt(strval) & ","

  6. #6
    Gicu is offline Expert
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Posts
    516
    What is strval if you hover the mouse over it when it errors out? Is it the exact same data set or you have different csv files?

    Cheers,
    Vlad

  7. #7
    nicoboss is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Sep 2017
    Location
    http://www.webagency321.com
    Posts
    62
    I have several csv files.

    I have just tested with 3 of them.

    In one case strval was ".95", in the other case it was ".7", and ".98" in the 3rd example.

  8. #8
    Gicu is offline Expert
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Posts
    516
    So assuming .95 is 0.95, .7 is 0.7 and .98 is 0.98 all three will get rounded to 1 by CInt. What exactly are you trying to do within that line of code?
    Have a look at the attached file and see if it runs OK.

    Cheers,
    Vlad
    Attached Files Attached Files

  9. #9
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    4,439
    the problem could be in strquery, as in an incompatible date (regional settings) or similar? I doubt it's the conversion function if it runs on one pc and not another.
    Or a trusted locations issue that prevents a variable from being set, thus Null is being passed?
    Last edited by Micron; 06-18-2018 at 08:28 AM. Reason: added info
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    So, like, why do you, like, start a sentence with, like, "so"?

  10. #10
    nicoboss is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Sep 2017
    Location
    http://www.webagency321.com
    Posts
    62
    Quote Originally Posted by Gicu View Post
    So assuming .95 is 0.95, .7 is 0.7 and .98 is 0.98 all three will get rounded to 1 by CInt. What exactly are you trying to do within that line of code?
    Have a look at the attached file and see if it runs OK.

    Cheers,
    Vlad
    Hi Gicu,

    the test is still running after a few minutes.
    Unfortunately, I have launched it with a big big csv file, so it's going to take a while before it's done, but the fact that it has still not bugged is a positive sign.
    I hope the data will be good.

  11. #11
    nicoboss is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Sep 2017
    Location
    http://www.webagency321.com
    Posts
    62
    Quote Originally Posted by Micron View Post
    Or a trusted locations issue that prevents a variable from being set, thus Null is being passed?
    Hi Micron, thank you for your help. Unfortunately I have no idea how i can check this.
    Let's see if the current test with Gicu's updated formula is good.

  12. #12
    Gicu is offline Expert
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Posts
    516
    Did it work?

    Cheers,
    Vlad

  13. #13
    nicoboss is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Sep 2017
    Location
    http://www.webagency321.com
    Posts
    62
    Hi, Gicu,

    Yes it works great, thank you !
    But it's very slow on the laptop. The test took 36 hours to complete.

  14. #14
    Gicu is offline Expert
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Posts
    516
    I don't think the change I did would make it slower, have you tried it on a smaller one that you know was working in parallel with your older version?

    Cheers,
    Vlad

  15. #15
    nicoboss is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Sep 2017
    Location
    http://www.webagency321.com
    Posts
    62
    Hi Gicu,

    I was wrong : the laptop version you updated doesn't work :-(

    In fact, there are several issues :

    1. the ID field is wrong : it only contains 11 characters, whereas it should contain 16 characters. For example, if the 3 first fields are "2h 2d 2c,As,9d9c," in the csv, it will become "2h 2d 2c_As" with the laptop version instead of "2h 2d 2c_As_9d9c"
    2. the values in other fields are wrong : the decimals are missing. for example, if I run the original version on my PC, "21.97" in the csv will become "2197" which is fine, whereas with the laptop version you updated, it will become "21", which is wrong.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 20
    Last Post: 10-13-2015, 08:05 AM
  2. Replies: 3
    Last Post: 10-16-2014, 07:49 AM
  3. Replies: 2
    Last Post: 06-28-2013, 11:58 AM
  4. Replies: 7
    Last Post: 05-28-2013, 08:11 AM
  5. Replies: 1
    Last Post: 05-04-2013, 11:19 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums