Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Mar 2002
    Location
    St Albans, Hertfordshire, England
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using EVAL and ado recordset 'additem' (Access 2k)

    I have enclosed a function that reads in a csv file which is badly formatted, and then identifies the max number of fields per record, and max field size.

    Using ADO I then create a memory based recordset, and read in the data line by line into the relevant fields; however, I receive an error when using the EVAL function as part of the additem command.

    I have attached the full module's code so you can see what is happening - just copy into a db Module, and run in break mode - you'll need to call it from the immediate window, passing in the name and location of any csv format file.

    If anybody can identify why the eval function doesn't work when used in the following context, I would be over the moon (this code is taken from Step 4 of the enclosed module)

    <pre> With rst
    .Open
    .AddNew _
    Eval("Array(" & strFieldList & ")"), _
    Eval("Array(" & strFieldValues & ")")
    .UpdateBatch
    End With
    </pre>

    Kind regards
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using EVAL and ado recordset 'additem' (Access 2k)

    Without the Search facility I can't locate it, but there was a thread some weeks or months ago about Eval and arrays in code in either the Excel or the VBA forum, and the conclusion was that it doesn't work. Instead, just loop through the fields and set the values one by one.

    There are some other issues with your code:
    - You keep on opening the recordset in the loop, that is not allowed.
    - You never close the recordset.
    - You forgot to set fso_Input_File to Nothing at the end.
    - You can't set an Integer variable to Null.
    - There is no error handling.

    I have attached a modified version of the code. It works on a small data file I created for testing. Modifications are marked with comments ' HV:
    Attached Files Attached Files

  3. #3
    Lounger
    Join Date
    Mar 2002
    Location
    St Albans, Hertfordshire, England
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using EVAL and ado recordset 'additem' (Access 2k)

    Hans,

    Thanks for the great response - I had been thinking about having to inrorporate the field identification into the recordset update loop, and you proved the point to me.

    As for not closing the recordset, there are other stages to follow, which are still being written.

    Kind regards

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using EVAL and ado recordset 'additem' (Access 2k)

    Once you have created the fields in your recordset and you know the number of fields, you can add them to a regular array dimmed for 0 to the number of fields. Then you can use the field names in that array to provide the fields to create the individual record arrays in the recordset. I've attached a sample from a demo database I built that does exactly this. It does not use EVAL, it takes a different approach. The demo allowed the user to create an array of arbitrary fields, define their data type and then build an empty recordset in memory. Then it allowed the user to specify the values for each field for as many records as they chose to enter. Unfortunately, the demo is just slightly too large to post here but if you email me at the address in my profile, I can send you a copy.
    Attached Files Attached Files
    Charlotte

  5. #5
    Lounger
    Join Date
    Mar 2002
    Location
    St Albans, Hertfordshire, England
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using EVAL and ado recordset 'additem' (Access 2k)

    Hi Charlotte,

    I would love to be able to send you an email, but unfortunately, we were hit with MSBlast, and as a result, all external emails (including ISP based emails) have been shut down to facilitate clean up - amazing when you think that our entire IT opperation is run by a very large Management Consultancy (who will remain nameless to prevent legal action <img src=/S/hushmouth.gif border=0 alt=hushmouth width=16 height=16> )!!!

    Can you send your sample db to me here ? I can pick it up tongiht when I get home.

    Kind regards

    Chris

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •