Re: Relink question

Hi Bruce

What Tony means is that when you create a linked table, part of the process
involves the file system creating a reference to the backend database. If
there is not one already from the current process, then the file will need
to be opened, a lengthy process which, for an MDB, also involves creating an
LDB file. Once the table is linked, the reference is dropped. When the
number of file system references to the backend database drops to zero, the
file closes (and the LDB is deleted).

All this happens in a single line of code:
<TableDef>.RefreshLink
or
<Database>.TableDefs.Append <TableDef>

If you are linking many tables in the same backend, then it is much faster
to open the backend database first, so that it already has a reference and
each table link will not cause a file Open/Close with all the other
associated overhead.

So, to clarify, the procedure is as follows:

1. Ascertain the path to the backend
2. Open the backend:
Dim dbBE as Database
Set dbBE = DBEngine(0).OpenDatabase(<path to backend>)
3. Link all the tables in that backend, one by one
4. Close the backend:
dbBE.Close

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

"BruceM" <bamoob@xxxxxxxxxxxxxxxx> wrote in message
news:ekOWgEkSJHA.2428@xxxxxxxxxxxxxxxxxxxxxxx
Thanks for the reply. When you say I should open a database variable
against the backend, would that be something like this?

Dim db as DAO.Database
Set db = CurrentDatabase()

or for the recordset:

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("tblMain", dbOpenDynaset)
(where tblMain is the first linked table)

If so, in either case would this be in the relinking code, and would I
close the recordset at the end of the relinking code? If not, where have
I missed the boat?

I sort of get what you are saying (although I have to say I don't
understand the "why"), but not enough to implement it with any confidence.


"Tony Toews [MVP]" <ttoews@xxxxxxxxxxxxxxx> wrote in message
news:pr87i4turpnnp0ptv5jopcjfohfn2dnq54@xxxxxxxxxx
"BruceM" <bamoob@xxxxxxxxxxxxxxxx> wrote:

Thanks again, Graham.. It will be some time before I can sort this out.
In
particular it seems I need to use something like GetPrivateProfileString
to
read from the ini file,

And the equivalent to write to the INI file once you've found the
correct BE.

Or you could use the registry. I initially chose to use INI files
because I figured that it would be easier for the users if they ever
had to manually update the file. But in over ten years I don't think
I've ever had the need.

I think I'll look for a way to
relink all tables every time and leave it at that for now.

Note that for best performance you should do either one of the
following:

1) Open a database variable against the backend
or
2) Once you've relinked the first table open a recordset variable
against that table and continue relinking all the other tables.

This will make a huge difference in performance when someone else is
already in the FE linked to the BE in question.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/



.

Relevant Pages

  • Re: Camaro or anyone: "Users logged in" in/on ULS backend
    ... Doug Steele, Microsoft Access MVP ... I've tried using a copy of my WIF in the folder with the backend database, ... but with a secure backend it gives an error saying I don't have ...
    (microsoft.public.access.setupconfig)
  • Re: Camaro or anyone: "Users logged in" in/on ULS backend
    ... I've tried using a copy of my WIF in the folder with the backend database, ... but with a secure backend it gives an error saying I don't have ...
    (microsoft.public.access.setupconfig)
  • Re: Relink question
    ... What Tony means is that when you create a linked table, part of the process involves the file system creating a reference to the backend database. ... If so, in either case would this be in the relinking code, and would I close the recordset at the end of the relinking code? ... Tony Toews, Microsoft Access MVP ...
    (microsoft.public.access.formscoding)
  • Re: Camaro or anyone: "Users logged in" in/on ULS backend
    ... "system.mdw" is a placeholder for my WIF, ... I've tried using a copy of my WIF in the folder with the backend database, ...
    (microsoft.public.access.setupconfig)
  • Re: Current User List
    ... Don't use "frmViewUsers" in the backend database to Disallow New Users. ... opens the frontend may find that it freezes on their machines. ...
    (microsoft.public.access.modulesdaovba)
Loading