Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts
    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??

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post
    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.
    Regards,
    Rudi

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='jeremybarker' post='765306' date='13-Mar-2009 21:01']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??[/quote]
    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.

Posting Permissions

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