What is the fastest way to update a access database

Posted on 2011-10-07
Last Modified: 2012-05-12
Hi please could you tell me the fastest way to update an access database from and sql server.
If i run the query directly from access it only takes 25 secs to update 16,000 records.

However I fill a database with data, open a connection to the access data base and loop through each row, then close the connection at the end. But this takes 3mins and thats from when the connection opens. (Not including the fill).

The reason for filling a table is so I can use a progress bar like access does.
Question by:taz8020
    LVL 13

    Expert Comment

    The fastest way is through the query directly or export the data from sql server to CSV then import it as a local table to ms access, then run your update query.  That might be even faster.  Then if you really want you can implement your custom progress bar when you loop through each record in the local table to execute the update.
    LVL 3

    Author Comment

    Even if i query directly in my app it takes 59 secs I can not get close to the time access takes. And they give a progress bar. I have not tried exporting it to a CSV first but thought this would add time.
    Access must do it through a loop for the progess bar.
    LVL 84
    How are you opening the connection and such? Are you using Stored Procedures to somehow do this?
    LVL 13

    Accepted Solution

    I've exported 17,000 records out of SQL to CSV in less than 10 seconds.  Either way, you'll still face the same issue regardless of how you do it.  If you want to go with performance then you should go with the query and sacrifice the user friendliness because at the end of the day what matters most is how your application responds and not how it looks to the end user.
    LVL 3

    Author Closing Comment

    reopening post

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now