View original (French)

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 -
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.
Configuration: Windows XP Internet Explorer 6.0

3 answers

  1.
    yakov Posted messages 115 Status Member 77
     
    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.
    0
  2. artamys Posted messages 122 Registration date   Status Member Last intervention   7
     
    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
    0
  3. yakov Posted messages 115 Status Member 77
     
    well... but more complicated nonetheless...
    0