Obtaining path for a linked Excel table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Anyone know of a way with ado.net to get the path for a linked Excel table in an Access DB? The following code returns a datatable with a column Table_Type which defines whether the table is linked, and a column Table_Name that holds the worksheet name within the Excel file, but I can't find a way to get the name and path of the Excel file

lcconn.Open(
Dim schemaTable As DataTable = lcconn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
New Object() {Nothing, Nothing, Nothing, Nothing}
lcconn.Close(
Return schemaTabl

Any help is greatly appreciated
 
Run a query

select connect, database as filepath, foreignname as sheetname
from MSysObjects
where name = 'mylinktablename'

does this help ?

Kalpesh
 
OleDbConnection conn = new OleDbConnection([put the connection string here]);
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.SelectCommand = new OleDbCommand([put the query that i mentioned], conn);
adapter.Fill(dataset);
return dataset.Tables[0];

I hope the code is correct, as I havent tested it
But, I assume it should work

Kalpesh
 
¤ Anyone know of a way with ado.net to get the path for a linked Excel table in an Access DB? The following code returns a datatable with a column Table_Type which defines whether the table is linked, and a column Table_Name that holds the worksheet name within the Excel file, but I can't find a way to get the name and path of the Excel file.
¤
¤ lcconn.Open()
¤ Dim schemaTable As DataTable = lcconn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, _
¤ New Object() {Nothing, Nothing, Nothing, Nothing})
¤ lcconn.Close()
¤ Return schemaTable
¤

You can also use ADOX (Microsoft ADO 2.x Ext for DDL and Security) to do this:

Dim ADOXTable As New ADOX.Table
Dim ADOXCatalog As New ADOX.Catalog
Dim ADOConnection As New ADODB.Connection
Dim LinkProviderString As String
Dim LinkDataSource As String

Try

ADOConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:\My Documents\db1.mdb;" & _
"Jet OLEDB:Engine Type=4;")

ADOXCatalog.ActiveConnection = ADOConnection

For Each ADOXTable In ADOXCatalog.Tables
If ADOXTable.Type = "LINK" Then
Console.WriteLine("DataSource: " & ADOXTable.Properties("Jet OLEDB:Link
Datasource").Value)
Console.WriteLine("Remote Table Name: " & ADOXTable.Properties("Jet OLEDB:Remote
Table Name").Value)
End If
Next
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
ADOConnection.Close()
End Try


You need to be careful about using Access system tables as they are often secured so that they
cannot be queried or modified. In addition, since access to system tables is an unsupported feature,
the ability to query these tables or the underlying structure may change.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Back
Top