Linking Tables to a different backend

  • Thread starter Thread starter Randy
  • Start date Start date
R

Randy

Hi

I have a front end, JK.mdb. The tables are linked to
backend liveData.mdb. I want to delete the links to
liveData.mdb and link to testData.mdb (I have two backends
one for developmnet and one for live use. I want to do
this with VBA using a button. There are some tables
present in one backend only.

Thanks
Randy
 
See The ACCESS Web for code to do the relinking:
http://www.mvps.org/access/tables/tbl0009.htm

I do this via setting up a table in which is the path to the backend(s),
with an identifying number for each path. You can use the identifying number
to know which backend is the one you want for each time you do the relinking
(which you do via the code in a form module, and you run the code by
clicking a button on that form). The code reads the appropriate path from
the table using a DLookup function.
 
The path to the tables is not the problem. The problem is
there are no linked tables in the frontend mdb, they have
been deleted. So there is no reference to any tables. I
need code to link to ALL the tables in the backend mdb. I
will put the path in the code for both backend mdb's.

OR

The path to the tables is not the problem. I need code
that will delete all links in the liveData.mdb and link to
all the tables in the testData.mdb. I will hard code the
paths to the mdb's in the code.

The problem is that not all the tables are in both mdb's.
 
The path to the tables is not the problem. The problem is
there are no linked tables in the frontend mdb, they have
been deleted. So there is no reference to any tables. I
need code to link to ALL the tables in the backend mdb. I
will put the path in the code for both backend mdb's.

OR

The path to the tables is not the problem. I need code
that will delete all links in the liveData.mdb and link to
all the tables in the testData.mdb. I will hard code the
paths to the mdb's in the code.

The problem is that not all the tables are in both mdb's.
Click to expand...


The recommended approach to manipulating linked tables is
through the TableDefs Collection and Delete the ones with a
something in it's Connect property or to set the Connect
property,

To Delete the linked tables in the current db:

Dim dbFE As DAO.Database
Dim tdfFE As DAO.TableDef
Set dbFE = CurrentDb
For Each tdfFE in dbFE.TableDefs
If tdfFE.Connect <> "" Then
db.TableDefs/Delete tdf.Name
End If
Next tdf

To find all the tables in another db, open the other
database and loop through its Tabledefs collection, adding
the tabledef to your current database:

Dim dbBE As DAO Database
Dim tdfBE As DAO.TableDef
Set dbBE = OpenDatabase("path to BE db")
For Each tdfBE in dbBE.TableDefs
Set tdfFE = dbFE.CreateTableDef(TdfBE.Name, , _
TdfBE.Name, ";DATABASE=" & "path to BE db")
dbFE.TableDefs.Append tdfFE
Next tdfBE
dbFE.TableDefs.Refresh

Set tdfBE = Nothing
dbBE.Close : Set dbBE = Nothing
Set tdfFE = Nothing
Set dbFE = Nothing
 
I entered the following code into the button....
--------------------------------------------
Dim dbFE As DAO.Database
Dim tdfFE As DAO.TableDef
Set dbFE = CurrentDb
For Each tdfFE In dbFE.TableDefs
If tdfFE.Connect <> "" Then
dbFE.TableDefs.Delete tdfFE.Name
End If
Next tdfFE

'To find all the tables in another db, open the other
'database and loop through its Tabledefs collection, adding
'the tabledef to your current database:
------------------------------------------------
Dim dbBE As DAO.Database
Dim tdfBE As DAO.TableDef
Set dbBE = OpenDatabase(strDataLinkPath)
For Each tdfBE In dbBE.TableDefs
Set tdfFE = dbFE.CreateTableDef(tdfBE.Name, ,
tdfBE.Name, ";DATABASE=" & strDataLinkPath)
dbFE.TableDefs.Append tdfFE
Next tdfBE
dbFE.TableDefs.Refresh

Set tdfBE = Nothing
dbBE.Close: Set dbBE = Nothing
Set tdfFE = Nothing
Set dbFE = Nothing
-----------------------------------------
On line "dbFE.TableDefs.Append tdfFE", I get the following
message "Run-time Error 3264. No field defined. Cannot
append tabledef or index."

The delete linked tables code deletes every other table,
leaving half the tables there each time it is run.

Randy

++++++++++++++++++++++++++++++++++++++++++++++++++++++

-----Original Message-----



The recommended approach to manipulating linked tables is
through the TableDefs Collection and Delete the ones with a
something in it's Connect property or to set the Connect
property,

To Delete the linked tables in the current db:

Dim dbFE As DAO.Database
Dim tdfFE As DAO.TableDef
Set dbFE = CurrentDb
For Each tdfFE in dbFE.TableDefs
If tdfFE.Connect <> "" Then
db.TableDefs/Delete tdf.Name
End If
Next tdf

To find all the tables in another db, open the other
database and loop through its Tabledefs collection, adding
the tabledef to your current database:

Dim dbBE As DAO Database
Dim tdfBE As DAO.TableDef
Set dbBE = OpenDatabase("path to BE db")
For Each tdfBE in dbBE.TableDefs
Set tdfFE = dbFE.CreateTableDef (TdfBE.Name, , _
Click to expand...
TdfBE.Name, ";DATABASE=" & "path to BE db")
 
The reason only half your tables are getting deleted is because of what
happens when you use For Each. You're deleting the current object, so the
pointer to what object you're on has to be reset. Unfortunately, Access sets
it to the next table. That means that when you issue the Next tdfFE command,
it moves again, thus skipping every other table.

You can fix that by using the following:

Dim dbFE As DAO.Database
Dim tdfFE As DAO.TableDef
Dim intLoop As Integer

Set dbFE = CurrentDb
For intLoop = (dbFE.TableDefs - 1) To 0 Step -1
Set tdfFE = dbFE.TableDefs(intLoop)
If tdfFE.Connect <> "" Then
dbFE.TableDefs.Delete tdfFE.Name
End If
Next intLoop

I'm not 100% certain why your attempt to relink isn't working. Does it work
for some tables, and die at a certain point? I did notice that you're
attempting to link all tables, which definitely won't work. You have to
ensure that you aren't linking the system tables. See whether the following
change helps:

For Each tdfBE In dbBE.TableDefs

If (tdfBe.Attributes And dbSystemObject) = 0 Then
Set tdfFE = dbFE.CreateTableDef(tdfBE.Name, , tdfBE.Name,
";DATABASE=" & strDataLinkPath)
dbFE.TableDefs.Append tdfFE

End If
Next tdfBE
 
I entered the following code into the button....
--------------------------------------------
Dim dbFE As DAO.Database
Dim tdfFE As DAO.TableDef
Set dbFE = CurrentDb
For Each tdfFE In dbFE.TableDefs
If tdfFE.Connect <> "" Then
dbFE.TableDefs.Delete tdfFE.Name
End If
Next tdfFE

'To find all the tables in another db, open the other
'database and loop through its Tabledefs collection, adding
'the tabledef to your current database:
------------------------------------------------
Dim dbBE As DAO.Database
Dim tdfBE As DAO.TableDef
Set dbBE = OpenDatabase(strDataLinkPath)
For Each tdfBE In dbBE.TableDefs
Set tdfFE = dbFE.CreateTableDef(tdfBE.Name, ,
tdfBE.Name, ";DATABASE=" & strDataLinkPath)
dbFE.TableDefs.Append tdfFE
Next tdfBE
dbFE.TableDefs.Refresh

Set tdfBE = Nothing
dbBE.Close: Set dbBE = Nothing
Set tdfFE = Nothing
Set dbFE = Nothing
Click to expand...

It's not supposed to complain about that when you specify
the Connect property. Let's try it the long way around:

Set tdfFE = dbFE.CreateTableDef(tdfBE.Name)
tdfFE.Connect = ";DATABASE=" & strDataLinkPath
tdfFE.SourceTableName = tdfBE.Name
dbFE.TableDefs.Append tdfFE

The delete linked tables code deletes every other table,
leaving half the tables there each time it is run.
Click to expand...

Arrggghhhh, I forgot about that collection delete issue. Do
this instead:

With dbFE.TableDefs
For k = .Count - 1 To 0 Step -1
If .Item(k).Connect <> "" Then
.Delete .Item(k).Name
End If
Next k
End With

Be sure to check Help on any of the properties or methods
you're not familiar with.
 
Thank You Dramatically For Your Help! The system is now
working fine. The following code is the end product.
'-
==========================================================
Private Sub cmdTestData_Click()
'set back end data path

Dim strDataLinkPath As String
strDataLinkPath = "E:\My
Documents\Access\KeyboardService\TestBackEnd.mdb"
Call LinkTables(strDataLinkPath)

End Sub
'==========================================================
=================
Private Sub LinkTables(strDataLinkPath)
'Delete the linked tables in the current Front End and
link the current Front End DB to the
'the called for Back End Tables.

Dim dbFE As DAO.Database
Dim tdfFE As DAO.TableDef
Dim intK As Integer
Dim intProgressValue As Integer
Dim intResponse As Integer
Dim dbBE As DAO.Database
Dim tdfBE As DAO.TableDef

Set dbFE = CurrentDb
'make sure all forms that could be bound to any tables
are closed.
intResponse = MsgBox("Please close all forms first!",
vbOKCancel, "Relink Error.")
If intResponse = vbCancel Then
Exit Sub
End If
axctlProgBar.Visible = True
intProgressValue = 1
axctlProgBar.Value = intProgressValue
With dbFE.TableDefs
For intK = .Count - 1 To 0 Step -1
If .Item(intK).Connect <> "" Then
.Delete .Item(intK).Name
End If
intProgressValue = intProgressValue + 1
axctlProgBar.Value = intProgressValue
Next intK
End With

Set dbBE = OpenDatabase(strDataLinkPath)
For Each tdfBE In dbBE.TableDefs
If Not Left(tdfBE.Name, 4) = "MSys" Then
'create a table definition in FrontEnd matching
table definiton in BackEnd as linked tables.
Set tdfFE = dbFE.CreateTableDef(tdfBE.Name)
tdfFE.Connect = ";DATABASE=" & strDataLinkPath
tdfFE.SourceTableName = tdfBE.Name
'add each table to the table defintion collection
dbFE.TableDefs.Append tdfFE
intProgressValue = intProgressValue + 1
axctlProgBar.Value = intProgressValue
End If
Next tdfBE
dbFE.TableDefs.Refresh

Set tdfBE = Nothing
dbBE.Close: Set dbBE = Nothing
Set tdfFE = Nothing
Set dbFE = Nothing
axctlProgBar.Visible = False
MsgBox "All tables have been properly linked.",
vbExclamation


End Sub
"==================================================


Thanks again for you assistance.

Randy Jarvis
Jarvis Keyboards
San Diego Ca
 
Back
Top