How can I determine if a table Def is Linked to an Excel File using VBA?

[ions]

Hello,

I am writing code to Refresh table links. I only want to refresh the Access table links and ignore the Excel Table links. Is there a way to determine in VBA if the table link is pointing to an Excel file?

Thank you

 

[Rx_]

Did a quick search, this code looks reasonable, can you test it for the benefit of others?

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 "Table " & tdfCurr.Name & " is linked."
Else
Debug.Print "Table " & tdfCurr.Name & " is not linked."
End If
Next tdfCurr

If there are multiple sources, it might be helpful to look it up in the MSysObjects table:

Function IsLinked(TableName As String) As Boolean
IsLinked = Nz(DLookup("Type", "MSysObjects", _
"Name = '" & TableName & "'"), 0) <> 1
End Function

Non-linked tables have a type of 1,
tables linked using ODBC have a type of 4
other linked tables have a type of 6

 

[Rx_]

Looked for a way to get the Provider name out of msysobjects.
Came across this function that discloses the Path of the back end Linked Table:
http://www.hitechcoach.com/index.ph...back-end-database-location&catid=65&Itemid=30

 

[ions]

Thanks for the Code RX.

I am already using the If Len(tdfCurr.Connect) > 0 Then to determine if the table is linked in my code. I need a finer distinction: Is the link to an Access table or Excel spreadsheet?

My current workaround is to hardcode the Excel Linked Table names in my code and ignore them during the Relink but if i can distinguish between an Access table link vs. Excel Table link that would better.

Thanks

 

[ions]

The Hitechcoach link is good. I can look at the file suffix of the backend source and determine if it's .xls .xlsx vs .mdb .accdb.

Thanks RX

 

[TJPoorman]

I attached a database with a form that I use for relinking tables. It only relinks access tables.

 

[Rx_]

With your dB open in the Immediate Window (in the Code view)
My table name is Rx_33 See if this gives you a more direct indication.
The table I chose is SQL Server - would bet it comes up different for Excel ... Show me yours (of course, we expect you to change the Password and User Names).

? CurrentDb.TableDefs("Rx_33").Properties(4)
ODBC;DRIVER=SQL Server Native Client 11.0;SERVER=densql\denr;UID=DataRx;PWD=YourMama;APP=Microsoft Office 2010;DATABASE=YourMomaDB;

 

[ions]

Very Nice RX.

? CurrentDb.TableDefs("tblOntColSitesImport").Properties(4)

Excel 8.0;HDR=YES;IMEX=2;DATABASE=C:\myTest.xls

 

[ions]

I used the Properties in my code and it works excellent. Thanks again RX. Exactly what I was looking.

 If Not Left(tdf.Properties(4), 5) = "Excel" Then