Linking Tables

  • Thread starter Thread starter Nigel Bennett
  • Start date Start date
N

Nigel Bennett

I have a table in the database that contains the names of
all the tables in my data base called zztables

Currently I have a docmd funtion that deletes the link to
each table and then after it has deleted the link it then
reattachs the table, this is hard coded in

I am looking for code where it will loop thru all the
records in zztable get each table name and then remove the
link and then loop thru the table again to reattach the
tables.

Hope this is enough info

Nigel
 
That works in one instance but what I am looking for is
code that looks at each record in the table and then adds
that to a variable carries out an action and then loops to
the next record and so on.
-----Original Message-----
Nigel,

Take a look at
Click to expand...
http://www.mvps.org/access/tables/tbl0009.htm for code to
relink tables. FWIW, you don't really have to keep a table of table names
since this information can easily be found in the tables collection.
Regardless, you should be able to adapt this code to your specific criteria.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Nigel said:
I have a table in the database that contains the names of
all the tables in my data base called zztables

Currently I have a docmd funtion that deletes the link to
each table and then after it has deleted the link it then
reattachs the table, this is hard coded in

I am looking for code where it will loop thru all the
records in zztable get each table name and then remove the
link and then loop thru the table again to reattach the
tables.

Hope this is enough info

Nigel
Click to expand...


.
Click to expand...
 
Hi Nigel,

I'm not sure what you mean by "That works in one instance". That code
actually does loop through a subset of the tables collection (the function
fGetLinkedTables() weeds out the ODBC connection tables). Regardless, here
is the basic code that demonstrates how you would loop through a recordset:

You can still use the code from fRefreshLinks as a guide for what you need
to do inside the loop. Basically you need the tablename and the path to the
linked database.

Open the recordset (once)
Test for records (EOF and BOF are both true when empty),
Loop through stopping when the EOF condition is reached.
Inside the loop we do something with each record,
Move to the next record
Close the recordset
Destroy the object variables.

Public Sub WalkRecordset()
Dim rst As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb()
'tblMyTables is the name of your table
Set rst = db.OpenRecordset("Select * from tblMyTables")
With rst
If Not (.EOF And .BOF) Then
Do Until .EOF
' Here is the place to put the action you want to take on
' each record - use the code in fRefreshLinks as a guide
Debug.Print "TableName:" & .Fields("TableName")
.MoveNext
Loop
End If
.Close
End With
Set rst = Nothing
Set db = Nothing
End Sub


--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Nigel said:
That works in one instance but what I am looking for is
code that looks at each record in the table and then adds
that to a variable carries out an action and then loops to
the next record and so on.
-----Original Message-----
Nigel,

Take a look at
Click to expand...
http://www.mvps.org/access/tables/tbl0009.htm for code to
relink tables. FWIW, you don't really have to keep a table of table
names since this information can easily be found in the tables
collection. Regardless, you should be able to adapt this code to
your specific criteria.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Nigel said:
I have a table in the database that contains the names of
all the tables in my data base called zztables

Currently I have a docmd funtion that deletes the link to
each table and then after it has deleted the link it then
reattachs the table, this is hard coded in

I am looking for code where it will loop thru all the
records in zztable get each table name and then remove the
link and then loop thru the table again to reattach the
tables.

Hope this is enough info

Nigel
Click to expand...


.
Click to expand...
Click to expand...
 
THe problem I have is that i have a table called address
and then another table called address1, they come from 2
different sources, so I need to make the code look in a
particular mdb for address and another mdb for the other
address table
-----Original Message-----
Hi Nigel,

I'm not sure what you mean by "That works in one instance". That code
actually does loop through a subset of the tables collection (the function
fGetLinkedTables() weeds out the ODBC connection tables). Regardless, here
is the basic code that demonstrates how you would loop through a recordset:

You can still use the code from fRefreshLinks as a guide for what you need
to do inside the loop. Basically you need the tablename and the path to the
linked database.

Open the recordset (once)
Test for records (EOF and BOF are both true when empty),
Loop through stopping when the EOF condition is reached.
Inside the loop we do something with each record,
Move to the next record
Close the recordset
Destroy the object variables.

Public Sub WalkRecordset()
Dim rst As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb()
'tblMyTables is the name of your table
Set rst = db.OpenRecordset("Select * from tblMyTables")
With rst
If Not (.EOF And .BOF) Then
Do Until .EOF
' Here is the place to put the action you want to take on
' each record - use the code in fRefreshLinks as a guide
Debug.Print "TableName:" & .Fields ("TableName")
.MoveNext
Loop
End If
.Close
End With
Set rst = Nothing
Set db = Nothing
End Sub


--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Nigel said:
That works in one instance but what I am looking for is
code that looks at each record in the table and then adds
that to a variable carries out an action and then loops to
the next record and so on.
-----Original Message-----
Nigel,

Take a look at
Click to expand...
http://www.mvps.org/access/tables/tbl0009.htm for code to
relink tables. FWIW, you don't really have to keep a table of table
names since this information can easily be found in the tables
collection. Regardless, you should be able to adapt this code to
your specific criteria.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Nigel Bennett wrote:
I have a table in the database that contains the names of
all the tables in my data base called zztables

Currently I have a docmd funtion that deletes the link to
each table and then after it has deleted the link it then
reattachs the table, this is hard coded in

I am looking for code where it will loop thru all the
records in zztable get each table name and then remove the
link and then loop thru the table again to reattach the
tables.

Hope this is enough info

Nigel


.
Click to expand...
Click to expand...


.
Click to expand...
 
The code will still work - just modify it to get the tablename and the path
to the backend database from your table.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Nigel said:
THe problem I have is that i have a table called address
and then another table called address1, they come from 2
different sources, so I need to make the code look in a
particular mdb for address and another mdb for the other
address table
-----Original Message-----
Hi Nigel,

I'm not sure what you mean by "That works in one instance". That
code actually does loop through a subset of the tables collection
(the function fGetLinkedTables() weeds out the ODBC connection
tables). Regardless, here is the basic code that demonstrates how
you would loop through a recordset:

You can still use the code from fRefreshLinks as a guide for what
you need to do inside the loop. Basically you need the tablename and
the path to the linked database.

Open the recordset (once)
Test for records (EOF and BOF are both true when empty),
Loop through stopping when the EOF condition is reached.
Inside the loop we do something with each record,
Move to the next record
Close the recordset
Destroy the object variables.

Public Sub WalkRecordset()
Dim rst As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb()
'tblMyTables is the name of your table
Set rst = db.OpenRecordset("Select * from tblMyTables")
With rst
If Not (.EOF And .BOF) Then
Do Until .EOF
' Here is the place to put the action you want to take on
' each record - use the code in fRefreshLinks as a guide
Debug.Print "TableName:" & .Fields ("TableName")
.MoveNext
Loop
End If
.Close
End With
Set rst = Nothing
Set db = Nothing
End Sub


--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Nigel said:
That works in one instance but what I am looking for is
code that looks at each record in the table and then adds
that to a variable carries out an action and then loops to
the next record and so on.
-----Original Message-----
Nigel,

Take a look at
http://www.mvps.org/access/tables/tbl0009.htm for code to
relink tables. FWIW, you don't really have to keep a table of table
names since this information can easily be found in the tables
collection. Regardless, you should be able to adapt this code to
your specific criteria.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Nigel Bennett wrote:
I have a table in the database that contains the names of
all the tables in my data base called zztables

Currently I have a docmd funtion that deletes the link to
each table and then after it has deleted the link it then
reattachs the table, this is hard coded in

I am looking for code where it will loop thru all the
records in zztable get each table name and then remove the
link and then loop thru the table again to reattach the
tables.

Hope this is enough info

Nigel


.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top