Access VBA test if we have a linked table
Solved
artamys
Posted messages
122
Registration date
Status
Member
Last intervention
-
yakov Posted messages 115 Status Member -
yakov Posted messages 115 Status Member -
Hello,
I have created an Access database for multiple users.
The problem is that I encountered difficulties because of the linked tables. Indeed, the seek primary key method does not work for linked tables, and the find method does not work for non-linked tables.
So I would like to know whether it is possible in VBA to test if a table is linked or not in order to incorporate this concept into a database that I can duplicate endlessly instead of needing to change the code at least twice with each modification.
Thank you.
I have created an Access database for multiple users.
The problem is that I encountered difficulties because of the linked tables. Indeed, the seek primary key method does not work for linked tables, and the find method does not work for non-linked tables.
So I would like to know whether it is possible in VBA to test if a table is linked or not in order to incorporate this concept into a database that I can duplicate endlessly instead of needing to change the code at least twice with each modification.
Thank you.
Configuration: Windows XP Internet Explorer 6.0
3 answers
I'm providing you with the small code that I use to refresh the linked tables of my database based on the concerned store.
It should help you
In DAO, the syntax is as follows to determine if a table is linked.
dim db as dao.database
dim tb as dao.tabledef
set db=opendatabase(.......)
for each tb in db.tabledefs
if tb.attributes and dbattachedtable then
........
End If
Next
BDDClose '
db.Close
Set db = Nothing
Set tb = Nothing
I hope this helps you.
It should help you
In DAO, the syntax is as follows to determine if a table is linked.
dim db as dao.database
dim tb as dao.tabledef
set db=opendatabase(.......)
for each tb in db.tabledefs
if tb.attributes and dbattachedtable then
........
End If
Next
BDDClose '
db.Close
Set db = Nothing
Set tb = Nothing
I hope this helps you.
Thank you, I found another solution, but I'll try yours.
In fact, it's done through a query that calculates the number of msysobjects. Since linked tables do not appear there (virtual), if I look for a specific table and it's not there, I find it and adapt the code accordingly.
Here is the code.
Thank you.
In my query titled NatureTableRequete
SELECT Msysobjects.Name, Msysobjects.Name
FROM Msysobjects
WHERE (((Msysobjects.Name)="TB_Projet") AND ((Msysobjects.Type)=1))
ORDER BY Msysobjects.Name;
In my code:
Dim NatureT As Integer
Dim NatureTable As Integer
NatureT = DCount("*", " NatureTableRequete")
If NatureT = 1 Then NatureTable = 1
If NatureT <> 1 Then NatureTable = 2
Set db = CurrentDb()
Set Base_modifProjet = db.OpenRecordset("TB_DEI")
If NatureTable = 2 Then Base_modifProjet.FindFirst ("NumDEI=" & ListeNumDEI & "")
If NatureTable = 1 Then Base_modifProjet.Index = "primarykey"
If NatureTable = 1 Then Base_modifProjet.Seek "=", ListeNumDEI
In fact, it's done through a query that calculates the number of msysobjects. Since linked tables do not appear there (virtual), if I look for a specific table and it's not there, I find it and adapt the code accordingly.
Here is the code.
Thank you.
In my query titled NatureTableRequete
SELECT Msysobjects.Name, Msysobjects.Name
FROM Msysobjects
WHERE (((Msysobjects.Name)="TB_Projet") AND ((Msysobjects.Type)=1))
ORDER BY Msysobjects.Name;
In my code:
Dim NatureT As Integer
Dim NatureTable As Integer
NatureT = DCount("*", " NatureTableRequete")
If NatureT = 1 Then NatureTable = 1
If NatureT <> 1 Then NatureTable = 2
Set db = CurrentDb()
Set Base_modifProjet = db.OpenRecordset("TB_DEI")
If NatureTable = 2 Then Base_modifProjet.FindFirst ("NumDEI=" & ListeNumDEI & "")
If NatureTable = 1 Then Base_modifProjet.Index = "primarykey"
If NatureTable = 1 Then Base_modifProjet.Seek "=", ListeNumDEI