Relinking Backend using Common Dialog Permanently?

[Guest]

Hello all,

I am designing an inventory database, using a FE/BE model in Access 2000,
that will be used by clients on Windows 2000 and Windows XP. The backend
currently resides in a temporary network share, and will be moved to a more
permanent location at some indeterminate point in the future.

The most obvious solution to a backend that can move seems to be the Common
Dialog control. I have implemented this, as discussed in the Solutions9.mdb
file available on MSDN at

http://support.microsoft.com/kb/248674/EN-US/

At least, I think this uses the Common Dialog control. It brings up a dialog
that certainly appears to be the Common Dialog, though the code used does
differ from that in the MS Knowledgebase article

http://support.microsoft.com/default.aspx?scid=kb;en-us;209862

Regardless, the implemented code works, to a point. My front end opens,
realizes tha the backend has moved (I've merely renamed a copy of the backend
for testing purposes), and opens the dialog to let me browse to a new file.
Upon selecting the relocated backend, the frontend opens successfully, linked
to the new backend.

My problem arises the next time I open the front end. Instead of remembering
that the backend has moved, it seems to look in the old location. Opening the
Linked Table Manager would seem to confirm this, as the tables all still have
the link to the old location, and the RefreshLinks() function (posted below)
seems to only be able to temporarily update the Connect property of each
table. My "solution" to this was to create a new function to replace the
tables with new tables containing the correct link. Unfortunately, my code
for this doesn't seem to be working correctly. I have posted it below as
well, so if anyone has any suggestions, I would really appreciate it. I know
that I can use the Linked Table Manager, however, I do not want the end users
of the compiled .mde file to be able to get into the Linked Table Manager,
the main database window, etc, etc. Since I have implemented an automatic
update functionality, I suppose that I could create a new build of the
frontend with corrected links and let that distribute itself, but I would
prefer not to have to do that, as I am merely an intern here and may not be
around by the time the backend gets to its permanent location. The automatic
update technique is based on the ideas at

http://support.microsoft.com/default.aspx?scid=kb;en-us;209862

where all the necessary paths are contained in a table in the backend, as
opposed to being hard coded. Anyways, heres my code, and thanks in advance
for any and all thoughts.

Matt Martin
(e-mail address removed)

Private Function RefreshLinks(strFileName As String) As Boolean
' Refresh links to the supplied database. Return True if successful.

Dim dbs As Database
Dim tdf As TableDef

' Loop through all tables in the database.
Set dbs = CurrentDb
For Each tdf In dbs.TableDefs
' If the table has a connect string, it's a linked table.
If Len(tdf.Connect) > 0 Then
tdf.Connect = ";DATABASE=" & strFileName
Err = 0
On Error Resume Next
tdf.RefreshLink ' Relink the table.

ReplaceLinkedTable tdf.Name, strFileName

If Err <> 0 Then
RefreshLinks = False
Exit Function
End If
End If
Next tdf

RefreshLinks = True ' Relinking complete.
End Function


And my "solution" (which would be called for any table that needs to be
relinked, effectively replacing the code in the If Len(tdf.Connect) > 0 block:


Public Function ReplaceLinkedTable(strName As String, strBackend As String)

Dim dbs As Database
Dim tdfOld As TableDef
Dim tdfNew As TableDef
Dim strOldName As String
Dim strNewName As String

strOldName = strName
strNewName = "x" & strName

Set dbs = CurrentDb

Set tdfOld = dbs.TableDefs(strOldName)
tdfOld.Name = strNewName

Set tdfNew = dbs.CreateTableDef(strOldName, , strOldName, strBackend)
Debug.Print tdfNew.Name
Debug.Print tdfNew.Connect
Debug.Print tdfNew.SourceTableName
tdfNew.Name = strOldName
tdfNew.Connect = ";DATABASE=" & strBackend
tdfNew.SourceTableName = strOldName

Debug.Print tdfNew.Name
Debug.Print tdfNew.Connect
Debug.Print tdfNew.SourceTableName
Debug.Print tdfOld.Name
Debug.Print tdfOld.Connect
Debug.Print tdfOld.SourceTableName

dbs.TableDefs.Append tdfNew
dbs.TableDefs.Delete strNewName
dbs.TableDefs.Refresh

Set tdfOld = Nothing
Set tdfNew = Nothing
Set dbs = Nothing

End Function


The idea is rename the old table, create a new table with an updated link
using the old name, and then delete the old table (which now has a new name).
Also, one might wonder why I use the CreateTableDefs method with the syntax
to create a linked table, and then manually set the linking properties in the
next lines. The answer is that if I dont set it manually, I cannot append the
new TableDef to the collection, as it is empty (contains no fields). If I
Debug.Print those properties immediately afterwards, they appear to be Null
strings. I am not sure why it doesn't work, but I suspect it might be a
corrupted installation on my machine - I can only pull up help files about
half of the time, which is tremendously inconvenient. Anyways, thanks again
if you made it all the way to the end of this rather lengthy post!

Matt Martin
(e-mail address removed)

 

[Guest]

Don't know if this will help, but it works for me.

For intCount = 0 To dbs.TableDefs.Count - 1
Set tdf = dbs.TableDefs(intCount)
If tdf.Connect <> "" Then
Me.lblMsg.Caption = "Refreshing " & tdf.NAME
DoEvents
tdf.Connect = ";DATABASE=" & strNewPath
tdf.RefreshLink
End If ' tdf.Connect <> ""
Next intCount

 

[Guest]

Thanks, I'll definitely try it on Monday. I've seen the DoEvents command
elsewhere, but I'm not exactly sure what it does - that's part of the
corrupted help files I get to deal with. Anyways, thanks again!

 

[Guest]

The DoEvents passes control back to Windows so other apps get a turn to
process.

 

[Guest]

Alright, so I haven't actually tried this code yet, but I think I have
actually found the problem. I opened my FE while holding down shift, got to
the linked table manager, and pointed it to a dummy backend (just so I dont
screw up other users of the system). I then closed my FE, and reopened it
with shift, and got back into the linked table manager. In the LTM, the link
was correct and I could access my linked tables, hooray.

Now the funkiness begins. I closed and reopened my FE normally (no shift).
This time, it prompted for a new location. I started digging into my code,
and determined that my link checker routine isn't working. The code for this
is:

Public Function CheckLinks() As Boolean
' Check links to the backend database; returns True if links are OK.

Dim dbs As Database
Dim rst As Recordset

Set dbs = CurrentDb

' Open linked table to see if connection information is correct.
On Error Resume Next
Set rst = dbs.OpenRecordset("tblPaths")

' If there's no error, return True.
If Err = 0 Then
MsgBox "True"
CheckLinks = True
Else
MsgBox Err
CheckLinks = False
End If

End Function

This code, with the exception of my linked table name "tblPaths" and my
debugging MsgBox statements, was copied from the example from before, and
didn't work. Upon comparing my code with the original again, I discovered
that the Dim statements actually were:

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

So, this brings me to the question of "What's the difference?" I thought
Database and Recordset objects were DAO, as I have included the DAO 3.6
library in my references.

So, sorry I didn't actually use your code, but thanks for the help anyways!

 

[Guest]

My problem arises the next time I open the front end. Instead of
remembering

that the backend has moved, it seems to look in the old location. Opening

the

You need to fix that. Access (and computer programs in general) don't just
'remember' old locations. You have code that tells it to use the old
location.
Find and fix that code, and the problem goes away.

prefer not to have to do that, as I am merely an intern here and may not be

Set tdfNew = dbs.CreateTableDef(strOldName, , strOldName, strBackend)

Don't leave a missing field like that. It seems to work, but it doesn't
really. You will
have to put the appropriate value in.


(david)

ps:
microsoft.public.access.externaldata,
microsoft.public.access.forms,
microsoft.public.access.formscoding,
microsoft.public.access.modulesdaovba,
microsoft.public.access.multiuser

Nothing really to do with forms at all, nor with multi-users. I'll give you
formscoding and external data although if you had looked at the messages you
would have known better, but you could have just asked once: one of
modulesdaovba, modulescoding, or .access. would have been enough, or those
three if you really wanted to see your name in print. :~)

 

[Bob Howard]

My application uses the process of refreshing the links per the code in your
original post (and it works fine). I made a minor modification, however, in
that it will only update the link if the link is different (added an if
statement in the code). Otherwise, my application was refreshing all the
links every time it executed. This tended to make the front end database
get larger and larger. It still gets larger, but not as quickly...