E
Eric I
I have some linking (relink by dropping table and linking
again) that is now failing after having applied the latest
service packs and Windows 2000 sp4. I don't know for sure
that this is what has caused the problem, but it's the
only thing I can think that must have happened, since the
code has been working for years.
I rebuilt a simplified function using the northwind MDB
for demonstration. My clients are still using the other
code to relink their mdbs just fine, but since I applied
the patches, the same code fails on my machine.
I have a reference set to Microsoft DAO 3.6 set, which is
required to get this code to compile. The code also
assumes your northwind mdb file is in the default path of:
C:\Program Files\Microsoft
Office\Office\Samples\Northwind.mdb
Here's a simplified version of the code:
Public Function LinkISAMTables() As Boolean
On Error GoTo Error_Handler
Dim sTableName(7) As String
Dim i As Integer
Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim sLocalTableName As String
Dim sSourceTableName As String
Dim sConnection As String
Set db = CurrentDb
sTableName(0) = "Categories"
sTableName(1) = "Customers"
sTableName(2) = "Employees"
sTableName(3) = "Order Details"
sTableName(4) = "Orders"
sTableName(5) = "Products"
sTableName(6) = "Shippers"
sTableName(7) = "Suppliers"
For i = 0 To 7
sLocalTableName = sTableName(i)
sSourceTableName = sTableName(i)
sConnection = ";DATABASE=C:\Program
Files\Microsoft Office\Office\Samples\Northwind.mdb"
'Remove table if it already exists
If TableExists(sLocalTableName) Then
If Len(db.TableDefs(sLocalTableName).Connect)
Then 'Linked Table
db.TableDefs.Delete sLocalTableName
End If
End If
'Create Table Link
Set tbl = db.CreateTableDef(sLocalTableName,
dbAttachSavePWD, sSourceTableName, sConnection)
'note, argument dbAttachedTable doesn't work either
'On Error Resume Next
'ERROR OCCURS ON NEXT LINE
'ERROR 3001 - INVALID ARGUMENT
db.TableDefs.Append tbl
Select Case Err.Number
Case 3001 'Invalid Argument (dbAttachSavePWD?)
'Create Table Link
MsgBox "invalid argument in attach tables
script. The tables may not attach properly."
Case 3011 'Table not found in specified
database
msgbox "table not found in source db."
End Select
On Error GoTo Error_Handler
DoEvents
Next i
db.Close
Exit_Procedure:
On Error Resume Next
Set db = Nothing
Exit Function
Error_Handler:
Select Case Err.Number
Case Else
MsgBox Err.Number & ", " & Err.Description
Resume Exit_Procedure
Resume
End Select
End Function
'No problem in this next function. It's only provided
'so the function above will compile correctly.
Function TableExists(sLocalTableName As String) As Boolean
On Error GoTo Error_Handler
'Function validates the existence of a TableDef object
in the current database.
'The result determines if an object should be appended
or its Connect property refreshed.
Dim db As DAO.Database
Dim tbl As DAO.TableDef
Set db = CurrentDb
On Error Resume Next
Set tbl = db.TableDefs(sLocalTableName)
If Err.Number = 3265 Then ' Item not found.
TableExists = False
Else
TableExists = True
End If
On Error GoTo Error_Handler
Set tbl = Nothing
db.Close
Set db = Nothing
Exit_Procedure:
Exit Function
Error_Handler:
Select Case Err.Number
Case Else
MsgBox Err.Number & ", " & Err.Description
Resume Exit_Procedure
Resume
End Select
End Function
Similar code is working fine when I run it against an ODBC
data source instead of an MDB/ISAM source, which initially
lead me to believe it was the connection string that was
causing the problem. After playing with the connection
string and pulling it directly from:
?currentdb.TableDefs("categories").Connect
....which yeilded...
;DATABASE=C:\Program Files\Microsoft
Office\Office\Samples\Northwind.mdb
....I ruled out the connection as the problem.
Any insite into this issue would be appreciated.
Thanks!
Eric I
again) that is now failing after having applied the latest
service packs and Windows 2000 sp4. I don't know for sure
that this is what has caused the problem, but it's the
only thing I can think that must have happened, since the
code has been working for years.
I rebuilt a simplified function using the northwind MDB
for demonstration. My clients are still using the other
code to relink their mdbs just fine, but since I applied
the patches, the same code fails on my machine.
I have a reference set to Microsoft DAO 3.6 set, which is
required to get this code to compile. The code also
assumes your northwind mdb file is in the default path of:
C:\Program Files\Microsoft
Office\Office\Samples\Northwind.mdb
Here's a simplified version of the code:
Public Function LinkISAMTables() As Boolean
On Error GoTo Error_Handler
Dim sTableName(7) As String
Dim i As Integer
Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim sLocalTableName As String
Dim sSourceTableName As String
Dim sConnection As String
Set db = CurrentDb
sTableName(0) = "Categories"
sTableName(1) = "Customers"
sTableName(2) = "Employees"
sTableName(3) = "Order Details"
sTableName(4) = "Orders"
sTableName(5) = "Products"
sTableName(6) = "Shippers"
sTableName(7) = "Suppliers"
For i = 0 To 7
sLocalTableName = sTableName(i)
sSourceTableName = sTableName(i)
sConnection = ";DATABASE=C:\Program
Files\Microsoft Office\Office\Samples\Northwind.mdb"
'Remove table if it already exists
If TableExists(sLocalTableName) Then
If Len(db.TableDefs(sLocalTableName).Connect)
Then 'Linked Table
db.TableDefs.Delete sLocalTableName
End If
End If
'Create Table Link
Set tbl = db.CreateTableDef(sLocalTableName,
dbAttachSavePWD, sSourceTableName, sConnection)
'note, argument dbAttachedTable doesn't work either
'On Error Resume Next
'ERROR OCCURS ON NEXT LINE
'ERROR 3001 - INVALID ARGUMENT
db.TableDefs.Append tbl
Select Case Err.Number
Case 3001 'Invalid Argument (dbAttachSavePWD?)
'Create Table Link
MsgBox "invalid argument in attach tables
script. The tables may not attach properly."
Case 3011 'Table not found in specified
database
msgbox "table not found in source db."
End Select
On Error GoTo Error_Handler
DoEvents
Next i
db.Close
Exit_Procedure:
On Error Resume Next
Set db = Nothing
Exit Function
Error_Handler:
Select Case Err.Number
Case Else
MsgBox Err.Number & ", " & Err.Description
Resume Exit_Procedure
Resume
End Select
End Function
'No problem in this next function. It's only provided
'so the function above will compile correctly.
Function TableExists(sLocalTableName As String) As Boolean
On Error GoTo Error_Handler
'Function validates the existence of a TableDef object
in the current database.
'The result determines if an object should be appended
or its Connect property refreshed.
Dim db As DAO.Database
Dim tbl As DAO.TableDef
Set db = CurrentDb
On Error Resume Next
Set tbl = db.TableDefs(sLocalTableName)
If Err.Number = 3265 Then ' Item not found.
TableExists = False
Else
TableExists = True
End If
On Error GoTo Error_Handler
Set tbl = Nothing
db.Close
Set db = Nothing
Exit_Procedure:
Exit Function
Error_Handler:
Select Case Err.Number
Case Else
MsgBox Err.Number & ", " & Err.Description
Resume Exit_Procedure
Resume
End Select
End Function
Similar code is working fine when I run it against an ODBC
data source instead of an MDB/ISAM source, which initially
lead me to believe it was the connection string that was
causing the problem. After playing with the connection
string and pulling it directly from:
?currentdb.TableDefs("categories").Connect
....which yeilded...
;DATABASE=C:\Program Files\Microsoft
Office\Office\Samples\Northwind.mdb
....I ruled out the connection as the problem.
Any insite into this issue would be appreciated.
Thanks!
Eric I
( That