TableDef Referencing Problem

M

MikeC

I'm developing a form that will be used to relink the linked tables to a
back end database selected in a list box. The application is being
developed in Access 2003.

The procedure, contained within the form's module, that relinks the tables
is at the bottom of this post. I'm getting error #91 "Object variable or
With block variable not set" whenever I attempt to set reference to the
tabledef for a valid linked table. I have tried several different ways to
reference the tabledef, but I keep getting the same error.

I have also:

1) Compacted and repaired the database.
2) Decompiled and recompiled the code.
3) Imported all database objects to a new database container.

The below line of code triggers the error:

Set tdf1 = dbsLocal.TableDefs(strTable)


Can anyone offer a solution to this problem?


'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Private Sub RelinkTables(pstrNewFilePath As String)
' Comments : This procedure relinks the linked tables to the specified
database.
'
' Parameters: pstrNewFilePath - String variable defining path to new
database.
'
' Created : 06/13/2006 12:11 MC
'
' Modification History
'
' Modified By Date Description
' --------------------------------------------------
'
On Error GoTo ERR_HANDLER

Dim coll As VBA.Collection
Dim dbsLocal As DAO.Database
Dim dbsBE As DAO.Database
Dim tdf1 As DAO.TableDef
Dim i As Integer
Dim strOldFilePath As String
Dim strOldFolder As String
Dim strTable As String
Dim strProdDBPath As String
Dim strProdFolder As String
Dim strArchiveFolder As String

'Get the path to the production database.
strProdDBPath = DLookup("[DBFilePath]", "[tblSysCon]", "[SysConID]=1")

'Get the path to the production database folder.
strProdFolder = Left$(strProdDBPath, InStrRev(strProdDBPath, "\"))

'Get the path to the archive folder.
strArchiveFolder = DLookup("[ArchiveFolder]", "[tblSysCon]",
"[SysConID]=1")

'Append a backslash if the path does not already end with a backslash.
If Right$(strArchiveFolder, 1) <> "\" Then strArchiveFolder =
strArchiveFolder & "\"

'Set reference to the currently running database. e.g. Front end client
application.
Set dbsLocal = CurrentDb()

'Get the collection of all linked tables.
Set coll = fnGetLinkedTables(dbsLocal)

For i = coll.Count To 1 Step -1

strOldFilePath = fnParsePath(coll(i))
strOldFolder = Left$(strOldFilePath, InStrRev(strOldFilePath, "\"))

'Check whether the strOldFilePath variable has been populated.
If Len(strOldFilePath) > 0 Then
'Compare the old folder path to the production and archive
folder paths.
'If a table is linked to the production database or to any
database in the
'archive folder, then relink the table.
If strOldFilePath = strProdDBPath _
Or strOldFolder = strArchiveFolder Then
'Set reference to the new database.
Set dbsBE = DBEngine(0).OpenDatabase(pstrNewFilePath)

'Get the table name.
strTable = fnParseTable(coll(i))

'Verify that table exists in new database.
If fnTableExists(dbsBE, strTable) Then
'Table exists, so connect to the new database.
Set tdf1 = dbsLocal.TableDefs(strTable)

With tdf1
.Connect = ";Database=" & pstrNewFilePath
.RefreshLink
coll.Remove .Name
End With
Else
Err.Raise vbObjectError + 516, "RelinkTables", "The
following linked" _
& " table does not exist in the specified database."
& vbCrLf _
& vbCrLf & "Database:" & vbTab & pstrNewFilePath _
& vbCrLf & "Table:" & vbTab & vbTab & strTable _
& vbCrLf & vbCrLf _
& "Please report this error to your application
administrator."
End If
End If
End If
Next

EXIT_PROCEDURE:
On Error Resume Next
Set coll = Nothing
Set tdf1 = Nothing
Set dbsBE = Nothing
Set dbsLocal = Nothing
Exit Sub

ERR_HANDLER:
If Err.Number <> 2501 Then
ErrorHandler Err.Number, Err.Description, "RelinkTables", Me.Name
End If
Resume EXIT_PROCEDURE

End Sub
 
M

MikeC

OK, I solved the problem myself. One partial solution was to change the
following line from:

Set tdf1 = dbsLocal.TableDefs(strTable)

To:

Set tdf1 = CurrentDb.TableDefs(strTable)

This change eliminated the "Object variable or With block variable not set"
error, but then I receive errors on some of the following lines, so I
decided to simply delete and recreate the linked tables using the
DeleteObject and TransferDatabase methods of DoCmd. This change also
allowed me to eliminate the dbsLocal and tdf1 object variables which is a
nice side benefit. The procedure works fine now.


'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Private Sub RelinkTables(pstrNewFilePath As String)
' Comments : This procedure relinks the linked tables to the specified
database.
'
' Parameters: pstrNewFilePath - String variable defining path to new
database.
'
' Created : 06/13/2006 12:11 MC
'
' Modification History
'
' Modified By Date Description
' --------------------------------------------------
'
On Error GoTo ERR_HANDLER

Dim coll As VBA.Collection
Dim dbsBE As DAO.Database
Dim i As Integer
Dim strOldFilePath As String
Dim strOldFolder As String
Dim strTable As String
Dim strProdDBPath As String
Dim strProdFolder As String
Dim strArchiveFolder As String

'Get the path to the production database.
strProdDBPath = DLookup("[DBFilePath]", "[tblSysCon]", "[SysConID]=1")

'Get the path to the production database folder.
strProdFolder = Left$(strProdDBPath, InStrRev(strProdDBPath, "\"))

'Get the path to the archive folder.
strArchiveFolder = fnGetArchiveFolder(True)

'Set reference to the new database. Also, validates database string.
Set dbsBE = DBEngine(0).OpenDatabase(pstrNewFilePath)

'Get the collection of all linked tables.
Set coll = fnGetLinkedTables(DBEngine(0)(0))

For i = coll.Count To 1 Step -1

strOldFilePath = fnParsePath(coll(i))
strOldFolder = Left$(strOldFilePath, InStrRev(strOldFilePath, "\"))

'Check whether the strOldFilePath variable has been populated.
If Len(strOldFilePath) > 0 Then
'Compare the old folder path to the production and archive
folder paths.
'If a table is linked to the production database or to any
database in the
'archive folder, then relink the table.
If strOldFilePath = strProdDBPath _
Or strOldFolder = strArchiveFolder Then

'Get the table name.
strTable = fnParseTable(coll(i))

'Verify that table exists in new database.
If fnTableExists(dbsBE, strTable) Then
'Delete the linked table.
DoCmd.DeleteObject acTable, strTable

'Re-link the linked table.
DoCmd.TransferDatabase acLink, "Microsoft Access",
pstrNewFilePath, , _
strTable, strTable
Else
Err.Raise vbObjectError + 516, "RelinkTables", "The
following linked" _
& " table does not exist in the specified database."
& vbCrLf _
& vbCrLf & "Database:" & vbTab & pstrNewFilePath _
& vbCrLf & "Table:" & vbTab & vbTab & strTable _
& vbCrLf & vbCrLf _
& "Please report this error to your application
administrator."
End If
End If
End If
Next

EXIT_PROCEDURE:
On Error Resume Next
Set coll = Nothing
Set dbsBE = Nothing
Exit Sub

ERR_HANDLER:
If Err.Number <> 2501 Then
ErrorHandler Err.Number, Err.Description, "RelinkTables", Me.Name,
True
End If
Resume EXIT_PROCEDURE

End Sub
'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

MikeC said:
I'm developing a form that will be used to relink the linked tables to a
back end database selected in a list box. The application is being
developed in Access 2003.

The procedure, contained within the form's module, that relinks the tables
is at the bottom of this post. I'm getting error #91 "Object variable or
With block variable not set" whenever I attempt to set reference to the
tabledef for a valid linked table. I have tried several different ways to
reference the tabledef, but I keep getting the same error.

I have also:

1) Compacted and repaired the database.
2) Decompiled and recompiled the code.
3) Imported all database objects to a new database container.

The below line of code triggers the error:

Set tdf1 = dbsLocal.TableDefs(strTable)


Can anyone offer a solution to this problem?


'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Private Sub RelinkTables(pstrNewFilePath As String)
' Comments : This procedure relinks the linked tables to the specified
database.
'
' Parameters: pstrNewFilePath - String variable defining path to new
database.
'
' Created : 06/13/2006 12:11 MC
'
' Modification History
'
' Modified By Date Description
' --------------------------------------------------
'
On Error GoTo ERR_HANDLER

Dim coll As VBA.Collection
Dim dbsLocal As DAO.Database
Dim dbsBE As DAO.Database
Dim tdf1 As DAO.TableDef
Dim i As Integer
Dim strOldFilePath As String
Dim strOldFolder As String
Dim strTable As String
Dim strProdDBPath As String
Dim strProdFolder As String
Dim strArchiveFolder As String

'Get the path to the production database.
strProdDBPath = DLookup("[DBFilePath]", "[tblSysCon]", "[SysConID]=1")

'Get the path to the production database folder.
strProdFolder = Left$(strProdDBPath, InStrRev(strProdDBPath, "\"))

'Get the path to the archive folder.
strArchiveFolder = DLookup("[ArchiveFolder]", "[tblSysCon]",
"[SysConID]=1")

'Append a backslash if the path does not already end with a backslash.
If Right$(strArchiveFolder, 1) <> "\" Then strArchiveFolder =
strArchiveFolder & "\"

'Set reference to the currently running database. e.g. Front end
client application.
Set dbsLocal = CurrentDb()

'Get the collection of all linked tables.
Set coll = fnGetLinkedTables(dbsLocal)

For i = coll.Count To 1 Step -1

strOldFilePath = fnParsePath(coll(i))
strOldFolder = Left$(strOldFilePath, InStrRev(strOldFilePath, "\"))

'Check whether the strOldFilePath variable has been populated.
If Len(strOldFilePath) > 0 Then
'Compare the old folder path to the production and archive
folder paths.
'If a table is linked to the production database or to any
database in the
'archive folder, then relink the table.
If strOldFilePath = strProdDBPath _
Or strOldFolder = strArchiveFolder Then
'Set reference to the new database.
Set dbsBE = DBEngine(0).OpenDatabase(pstrNewFilePath)

'Get the table name.
strTable = fnParseTable(coll(i))

'Verify that table exists in new database.
If fnTableExists(dbsBE, strTable) Then
'Table exists, so connect to the new database.
Set tdf1 = dbsLocal.TableDefs(strTable)

With tdf1
.Connect = ";Database=" & pstrNewFilePath
.RefreshLink
coll.Remove .Name
End With
Else
Err.Raise vbObjectError + 516, "RelinkTables", "The
following linked" _
& " table does not exist in the specified
database." & vbCrLf _
& vbCrLf & "Database:" & vbTab & pstrNewFilePath _
& vbCrLf & "Table:" & vbTab & vbTab & strTable _
& vbCrLf & vbCrLf _
& "Please report this error to your application
administrator."
End If
End If
End If
Next

EXIT_PROCEDURE:
On Error Resume Next
Set coll = Nothing
Set tdf1 = Nothing
Set dbsBE = Nothing
Set dbsLocal = Nothing
Exit Sub

ERR_HANDLER:
If Err.Number <> 2501 Then
ErrorHandler Err.Number, Err.Description, "RelinkTables", Me.Name
End If
Resume EXIT_PROCEDURE

End Sub
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA Export to PDF 0
Remove Identical words 0
AddPicturesToShapes 0
Working outlook via vba code with xlsx - error 0
Match style 0
Word macro that can call cells out of Excel for a find and replace 0
how to run several different functions at form open? 1
Help needed! Extracting Heading 3 and Contents from a Word Doc to Excel 0

Top