Bulk insert rows into Access

Topic

New Reply

Just wondering…are there any clever ways of inserting a large number of rows of data from Excel into Access at once?

I know I can start from the database side and do an Import, or use TransferSpreasheet in code

Also from Excel I can step through the rows one at a time and used DAO (or ADO) with an Add method to do a record at once picking up field values from individual cells…

but it would be nice if I had some sort of “BULK INSERT / COPY” facility available from Excel as the client.

Anyone know of any fancy ways of doing this??

Reply | Quote

Replies

Hi Jeremy,

I think you hit the nail on the head already when you supplied the first three methods for getting data into Access from Excel. All three methods you list are pretty “clever”, and will probably be the best choices around.

BTW: As long as the fields and formats are similar between Excel and Access, you could also do a Copy and Paste…since you did not mention that!

Besides what I mention above…(the obvious)… I think it is a good idea just to put the question out there…you never know if there is one other way that beats the lot. I have done it many times here in the lounge and have been surprised by the amazing answers I have recieved.

Reply | Quote

Just wondering…are there any clever ways of inserting a large number of rows of data from Excel into Access at once?

I know I can start from the database side and do an Import, or use TransferSpreasheet in code

Also from Excel I can step through the rows one at a time and used DAO (or ADO) with an Add method to do a record at once picking up field values from individual cells…

but it would be nice if I had some sort of “BULK INSERT / COPY” facility available from Excel as the client.

Anyone know of any fancy ways of doing this??

You could run Access from Excel using Automation and use one of the Access methods that you mention.

It’s probably possible to use ADO to run a SELECT INTO statement that exports data from Excel into an Access database.

Reply | Quote