Insert values of array into ACCESS DB

Hi all,

I have developed the undermentioned code to insert values into an Access table. The function RtnQryStr just creates the insert string using the values in an array called UniqueArray(i) after splitting them as shown.

My issue is I have a file with about 200000+ records. And it takes ages for it to execute every value one by one in the QryString.

Is there any way to insert all these values in one go?
If there is a way, would QryString be able to hold that large chunk of data strings to insert?

Please Login or Register  to view this content.

Please Login or Register  to view this content.

In case anyone is wondering, I cannot use recordsets because I am on Win 64 bit, and for some reason ADO is not recognized so I have to access the database using the methods available in Access.Application object.

AFAIK Access has no bulk insert facility. To speed things up, check a few things:

• You are using DAO rather than ADO
• You are not closing and re-opening the connection for each insert
• You aren't doing any looping of the worksheet
• All your excel code is efficient as possible

1) I am using the methods available via Access.Application object.
2) I am not closing and reopening the connections. In fact Access.Application object works on whatever Databases are opened at the time of code execution so I am using

Please Login or Register  to view this content.

to open the database I have the table in.
3) I am not doing any looping of the worksheet.
4) The only thing that is not efficient is me inserting rows one by one, the reason that i am here.

Then no, Access doesn't support bulk inserts through sql.

The other solution would be to use the text import wizard and do it that way

Is there any way for me to use the ACCESS text import wizard using vba?

Probably. I don't have access though. A quick google turned this up

http://www.ozgrid.com/forum/showthread.php?t=32942