Re Linking Tables

[Colin]

Hi

I have A Database that has many linked tables and I have
created a front end database that has a table of various
back end database connect strings eg

Billing Month Server Address Billing Month order
May 2004 \\server\Billing\Billing Archive\2004
\2004 05\Datastore May 2004.mdb 2

June 2004 \\server\Billing\Billing Archive\2004
\2004 05\Datastore Jun 2004.mdb 3

From a form the user can select which database he
connects to and I have written the following code that
will delete the existing tables and re link them to the
database selected from the dropdown list.

The code works correctly for re linking the first three
tables but when it trys link on the forth table it stops
with the following error message.

Object Invalid or no longer set

The code is as follows:

'**********************************


Sub LinkTables(filename As String)
On Error GoTo Err_LinkTables:
Dim Directory As String

Dim db As Database
Dim tbl As TableDef
Set db = CurrentDb

'create string for drive where program exists
Directory = getpath(db.name)
'delete the linked table
db.TableDefs.Delete "CDR"

'reattach table from new database
Set tbl = db.CreateTableDef("CDR")
tbl.Connect = (";DATABASE=" & filename)
tbl.SourceTableName = "CDR"
db.TableDefs.Append tbl

'create string for drive where program exists
Directory = getpath(db.name)
'delete the linked table
db.TableDefs.Delete "Billing Variables"

'reattach table from new database
Set tbl = db.CreateTableDef("Billing Variables")
tbl.Connect = (";DATABASE=" & filename)
tbl.SourceTableName = "Billing Variables"
db.TableDefs.Append tbl


'create string for drive where program exists
Directory = getpath(db.name)
'delete the linked table
db.TableDefs.Delete "BridgeConference"

'reattach table from new database
Set tbl = db.CreateTableDef("BridgeConference")
tbl.Connect = (";DATABASE=" & filename)
tbl.SourceTableName = "BridgeConference"
db.TableDefs.Append tbl
db.Close

'create string for drive where program exists
Directory = getpath(db.name)
'delete the linked table
db.TableDefs.Delete "Billing Variables"

'reattach table from new database
Set tbl = db.CreateTableDef("Billing Variables")
tbl.Connect = (";DATABASE=" & filename)
tbl.SourceTableName = "Billing Variables"
db.TableDefs.Append tbl
db.Close

Exit_LinkTables:
Exit Sub

Err_LinkTables:

If Err.Number = 53 Then
Resume Next
ElseIf Err.Number = 3265 Then
Resume Next
Else
MsgBox Err.Description
Resume Exit_LinkTables
End If

End Sub


Please accept my appologies if there is an obvious error
but Iam new to code writing and would appreciate any
assistance that you can give.

Kind regards

Colin

 

[Douglas J. Steele]

Get rid of the db.Close statement between the 3rd and 4th table (Marked in
the code below)

 

[Colin]

Cheers Doug

It takes a 3rd party to spot the Obvious

Many thanks