JaedenRuiner
Registered User.
- Local time
- Today, 02:41
- Joined
- Jun 22, 2005
- Messages
- 154
Okay,
So my system is one that has a MDB database file that is linked into the MDE application at run time. part of the system is to allow me to update the "app" side of things while leaving the data separated. However, at boot up I want to verify that all of the table links are refreshed and thus will not cause any other breaks in the code. This is also due to some tables changing between versions and other tables being added, so I have to be able to update the database on the fly to the most recent version, as well as maintain the table links, not to mention the location of the mdb could be different based upon where it was installed...
All this being said, I have two methods: RefreshLinks() and GetTableDef(). The latter of course, takes a Database and String parameters and attempts to get the TableDef from the Database. If that table def name doesn't exist, it traps the error and returns nothing. the RefreshLinks does two things. First it attaches to the external database and gets all the valid table names. It then iterates through those names in a collection and verifies that if the tabledef already exists in the CurrentDb, to refresh the connect string, and if not, to create the tabledef.
However, after I make the call to GetTableDef() my internal variable (tdf) retains the value returned by GetTableDef. the Next line is a if statement condition testing for Nothing. once I step past that line, the tdf throws the arror "Object invalid or no longer set". why is this? what is causing the tdf to lose it's value after a simple condition test?
Thanks
Jaeden "Sifo Dyas" al'Raec Ruiner
So my system is one that has a MDB database file that is linked into the MDE application at run time. part of the system is to allow me to update the "app" side of things while leaving the data separated. However, at boot up I want to verify that all of the table links are refreshed and thus will not cause any other breaks in the code. This is also due to some tables changing between versions and other tables being added, so I have to be able to update the database on the fly to the most recent version, as well as maintain the table links, not to mention the location of the mdb could be different based upon where it was installed...
All this being said, I have two methods: RefreshLinks() and GetTableDef(). The latter of course, takes a Database and String parameters and attempts to get the TableDef from the Database. If that table def name doesn't exist, it traps the error and returns nothing. the RefreshLinks does two things. First it attaches to the external database and gets all the valid table names. It then iterates through those names in a collection and verifies that if the tabledef already exists in the CurrentDb, to refresh the connect string, and if not, to create the tabledef.
However, after I make the call to GetTableDef() my internal variable (tdf) retains the value returned by GetTableDef. the Next line is a if statement condition testing for Nothing. once I step past that line, the tdf throws the arror "Object invalid or no longer set". why is this? what is causing the tdf to lose it's value after a simple condition test?
Thanks
Jaeden "Sifo Dyas" al'Raec Ruiner
Code:
Public Sub RefreshLinks()
On Error GoTo RefreshLinks_Error
Dim tdf As TableDef
Dim ConnectStr As String
Dim tables As Collection
Dim item As Variant
Dim table As String
If Me.IsValid Then
Set tables = New Collection
For Each tdf In Me.DB.TableDefs
If Left$(tdf.Name, 4) <> "MSys" And Left$(tdf.Name, 1) <> "~" Then
tables.Add tdf.Name
End If
Next
ConnectStr = ";DATABASE=" & db_Prefs("Path") & db_Prefs("DB")
For Each item In tables
table = CStr(item)
Set tdf = GetTableDef(CurrentDb, table)
If tdf Is Nothing Then 'value good here
Set tdf = CurrentDb.CreateTableDef(table, dbAttachedTable, table, ConnectStr)
[b]Else: tdf.Connect = ConnectStr 'value invalid or not set here[/b]
End If
tdf.RefreshLink
Next
End If
On Error GoTo 0
RefreshLinks_Exit:
Set tdf = Nothing
Exit Sub
RefreshLinks_Error:
MsgBox "Error " & Err.Number & " (" & Err.description & ") in procedure RefreshLinks of Module MainModule"
Resume RefreshLinks_Exit
End Sub
Public Function GetTableDef(DB As Database, Name As String) As TableDef
On Error GoTo GetTableDef_Error
Dim tdf As TableDef
Set tdf = DB.TableDefs(Name)
GetTableDef_Exit:
Set GetTableDef = tdf
Exit Function
GetTableDef_Error:
Set tdf = Nothing
Resume GetTableDef_Exit
End Function