ACCESS table linked to EXCEL spreadsheet. How to know which table is linked?

[Art]

Hi folks -

Could you please help. I have Excel templates that pull data from
ACCESS (make table queries).

In Excel I go to DATA -> IMPORT EXTERNAL DATA -> EDIT QUERY where i am
trying to find from the list (~100 ) of tables which table is
participationg in a query. Tables unfortunately are not named
appropriately.

The only information I have is the list of fields.

The person who created all databases and templates is not with my
company anymore. ACCESS databases and Excel templates are not
documented.

My question: Is there any easiest way to find which ACCESS table is
linked to the EXCEL template?

Thanks,

Artem

 

[Douglas J. Steele]

Try the following VBA:

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef

Set dbCurr = CurrentDb()
For Each tdfCurr In dbCurr.TableDefs
If Len(tdfCurr.Connect) > 0 Then
Debug.Print tdfCurr.Name & " is linked to " & tdfCurr.Connect
End If
Next tdfCurr

Look in the Immediate window (Ctrl-G) to see the results.

 

[usenet.ca]

Try the following VBA:

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef

Set dbCurr = CurrentDb()
For Each tdfCurr In dbCurr.TableDefs
If Len(tdfCurr.Connect) > 0 Then
Debug.Print tdfCurr.Name & " is linked to " & tdfCurr.Connect
End If
Next tdfCurr

Look in the Immediate window (Ctrl-G) to see the results.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)











- Show quoted text -

Thanks-

Sorry for a trivial question. Where I should insert the VBA code?

Artem

 

[Douglas J. Steele]

Create a sub in a module (not a class module) and then run it from the
Immediate window. No need to save it if you're not going to need to run it
again.