CODEWITHSUNDEEP
sqlvbaexcelms-access
user1452705
user1452705

Reputation:

How to enter multiple records at once in VBA (Microsoft Access)

I am trying to enter thousands of records into an Access Database from a .txt file. Currently I am looping through the records and adding them one at a time. Sometime this can take over 30 minutes. Is there a way to load the records into memory like an array and then execute it at the end of the routine and have all the records entered at once?

There is this thread, but I can't get DAO to work, when I try to select the Microsoft DAO 3.6 Object Library in reference library, I get a .dll error. VBA to insert many records into access DB fast

Thanks

EDIT: If this is not possible in MS Access, can it be done with MySQL or another Database?

Upvotes: 0

Views: 1173

Answers (1)

Gustav
Gustav

Reputation: 55961

You need a reference to the Microsoft Office 15.0 Access database engine Object Library. This is the current and default engine for Access. In Access, ADODB is only used in special cases.

Then you can either link the text file as a table and run an append query to copy the records to your table, or you can read the file line by line a call AddNew ... Update for each record. Or you can run DoCmd.TransferText as mentioned by @Parfait or use the import wizard.

Upvotes: 0

Related Questions