Results 1 to 8 of 8
  1. #1
    harryklein is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    48

    RS Update vs UpdateBatch

    I am trying to add the most efficient way to add a relatively high number of records to an MSAccess-table from excel vba
    I used SQL Insert into queries, but I experienced performance issues, therefore I tried to use the update method of the RecordSet.
    I noticed that there is an updateBatch method, and from the "batch" I make the conclusion that it is made for "mass upload", but I couldn't find any clear explanation, how does it work exactly.
    I made some tests based on codes I found but I couldn't see differences in terms of speed, actually UpdateBatch is a little bit slower.

    If somebody has any experience or knows any advantages of using updatebatch, I would appreciate if it would be shared with me.
    Thank you

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    not something I've used, but works with ADO subject to the type of backend - see this link https://docs.microsoft.com/en-us/sql...l-server-ver15

    note that update method in vba will always be slower than using a query.

    Have you tried docmd.transferspreadsheet function to get your data into the database? Your description says you are running this from excel, pushing data to access, rather than access pulling from excel so you'll need to add in the Microsoft Access xx.x Object Library

    performance issues can be due to a number of things - for example excel does not have indexing so if you are using joins, sorting or criteria on a large excel file in your sql query, it will be slow because it has to use sequential techniques. Alternatively it may be due to your connection - are the excel and access files local or located on a server?

    what do your insert into queries look like?

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You also might check into Ken Snell's site Importing from EXCEL Workbook Files Examples - this is using Access to read Excel spreadsheets.
    Some examples use automation. I've had good results - I modify the code quite a bit.

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I would also recommend doing this by importing the data from Excel in Access rather than exporting from Excel.
    The most reliable way in my experience is to add the Excel file(s) as linked tables, then do a two step approach.
    Import to a staging or buffer table, inspect the data and run an append query or sql statement making any modifications needed to individual fields.
    If the data is straightforward you may be able to omit the intermediary step.

    Whilst you can loop through a recordset to do this, it will be far slower especially if you have a lot of records, and there is usually no real advantage in doing so … unless you need to also make a log of the changes made.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    harryklein is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    48
    Thanks for the answer.
    We are just testing yet, and the query we are using is simple
    INSERT INTO Table1 (F1, f2, F3) Values ("a"."b","c")
    We don't intend to use joins, the situation we need to manage is to avoid storing a huge excel file on the shared drive that accessed by many users at the same time. That's why we want to use msaccess as backend. Yes, msaccess file is stored in a network, excel - it depends on the user.

  6. #6
    harryklein is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    48
    Thanks for the answer, sounds promising, I never used linked tables yet, but will give a try.

  7. #7
    harryklein is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    48
    Thanks a lot for your answer.

  8. #8
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by harryklein View Post
    ...the situation we need to manage is to avoid storing a huge excel file on the shared drive that accessed by many users at the same time. That's why we want to use msaccess as backend. Yes, msaccess file is stored in a network, excel - it depends on the user.
    Be aware:
    If you are going to use Access, be sure to split the dB into a FE (front end) & BE (back end). The BE has only tables and is on a shared network drive. The FE has everything EXCEPT tables - every user has a COPY of the FE on their own computer.
    If the FE & BE are on the network drive and everyone uses the same FE, the dB will be corrupted. It is not a matter of IF, just a matter of WHEN.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 06-21-2018, 03:16 AM
  2. Replies: 6
    Last Post: 11-05-2014, 05:27 PM
  3. Replies: 7
    Last Post: 08-26-2013, 06:05 AM
  4. Replies: 2
    Last Post: 04-23-2012, 10:13 PM
  5. Replies: 1
    Last Post: 01-22-2011, 12:23 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums