How to relink Access tables faster
When you run relink procedure at application startup, and backend file is located on the network – the process can be much slower then relinking local file, even with very fast network. The trick is to open backend file before relink process and close it after. Sample code:
Dim dbData As DAO.Database
Set dbData = DBEngine.OpenDatabase(strFileName)
'Relink proc follows
...
dbData.Close
Set dbData = Nothing
posted by Alex Dybenko | 16:04
8 Comments:
Great tip!
Alex, do you know why some Access databases UNLINK all the 155 tables upon closing, and then RELINK upon opening? What is the advantage of this? The five users enter data and run reports, nothing else. There's only one back-end with tables and some queries. So no one is relinking to another back-end of data.
Thanks for your reply!
Paula, Wisconsin, USA
Hi Paula,
never saw that somebody unlink tables upon closing. Only idea - security reason, so if you open database - you will not find a path to backend
Alex, last year our dept purchased two proprietary Access databases, and they both UNLINK and RELINK upon closing/reopening. Takes a minute or so TO RELINK ON OPENING, during which time all sorts of things could happen (to my way of thinking). Never seen a database designed this way and I've developed in Access since Access2 was out. Just wondered if someone smarter than I knew why . . .
Paula
Hi,
1-2 minutes for 155 tables - perhaps not too slow, but never saw applications with such number of tables. Perhpas you can ask authors why they unlink tables and then let us know?
@Paula, only other reason other than security, would be development speed. Updating schema information in the backend, would require manually relinking of tables, and perhaps they did this to speed up the process and left it in place by mistake.
Thanks for the tip. This will be helpful for my app at work.
There are good reasons to link at runtime. We have offices on WAN all over the state that run from a central file server. The offices that have low bandwith have large tables replicated in SQL Server locally. When the application runs, it determines the office and links either locally or remotely. Also, our financial applications have to link to a different SQL Server database depending on fiscal year. Therefore, one application can support multiple locations and multiple databases, reconfiguring linked tables on the fly as needed.
there is overhead in space for each time a linked table is created and deleted, so we also compact on exit.
Post a Comment
<< Home