M
MartinMCU
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)
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)