AccessBlog.net

News, links, downloads, tips and tricks on Microsoft Access and related

About Me Search
Alex
Name:Alex Dybenko

Location:Moscow, Russia

Thursday, October 29, 2009

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

Labels: ,

8 Comments:

Blogger Tony D'Ambra said...

Great tip!

12:30 AM  
Anonymous Anonymous said...

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

5:35 PM  
Blogger Alex Dybenko said...

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

6:52 PM  
Anonymous Anonymous said...

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

7:11 PM  
Blogger Alex Dybenko said...

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?

10:36 AM  
Blogger Derek said...

@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.

9:28 PM  
Anonymous Andrew said...

Thanks for the tip. This will be helpful for my app at work.

5:20 AM  
Anonymous Anonymous said...

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.

9:42 PM  

Post a Comment

<< Home