[Webinar] Learn how to a build a cloud-first strategyRegister Now


What is the fastest way to update a access database

Posted on 2011-10-07
Medium Priority
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
  • 2
  • 2
LVL 13

Expert Comment

ID: 36930923
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.

Author Comment

ID: 36930957
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 85
ID: 36931017
How are you opening the connection and such? Are you using Stored Procedures to somehow do this?
LVL 13

Accepted Solution

Lucas earned 1500 total points
ID: 36955607
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.

Author Closing Comment

ID: 36980889
reopening post

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Viewers will learn how the fundamental information of how to create a table.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

864 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