|
Hi,
I have been working on this piece of code which lists all tables in a MS Access database (including linked tables) and converts them to a DataTable with a pre-defined format. I use ADOX and Visual Basic .NET
It all works fine, except when I need to close the connection. I have read various forums on the topic, and the suggested solutions only seem to work as long as I do not try to access the ADOX.Table properties ("Jet OLEDB:Link Datasource" and "Jet OLEDB:Remote Table Name"). Because my code calls these properties, I am unable to completely close the connection to my Access database (the .ldb file stays there until I shut down the application).
I have tried implementing the solutions suggested in http://www.themssforum.com/Ado/file-stays/, http://www.pcreview.co.uk/forums/thread-1245554-2.php and http://www.thescripts.com/forum/thread559491.html without success.
I don't know where else to look for...
Thanks !
Manuel
Below is my complete code:
Dim adoCon As New ADODB.Connection
Dim adoCat As New ADOX.Catalog
Dim adoTbl As New ADOX.Table
'Connect to database
adoCon.Open("Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Engine Type=5;OLE DB Services=-4;Data Source = C:\Test.mdb")
adoCat.ActiveConnection = adoCon
Dim TblList As New System.Data.DataTable
Dim dc As New System.Data.DataColumn
dc.ColumnName = "TblName"
dc.DataType = GetType(String)
TblList.Columns.Add(dc)
dc = New DataColumn
dc.ColumnName = "TblType"
dc.DataType = GetType(String)
TblList.Columns.Add(dc)
dc = New DataColumn
dc.ColumnName = "LinkDBPath"
dc.DataType = GetType(String)
TblList.Columns.Add(dc)
dc = New DataColumn
dc.ColumnName = "LinkDBTable"
dc.DataType = GetType(String)
TblList.Columns.Add(dc)
dc.Dispose()
dc = Nothing
Dim dr As DataRow = TblList.NewRow()
'List links
For Each adoTbl In adoCat.Tables
If adoTbl.Type = "LINK" Or adoTbl.Type = "TABLE" Then
dr("TblType") = adoTbl.Type
dr("TblName") = adoTbl.Name
dr("LinkDBPath") = adoTbl.Properties("Jet OLEDB:Link Datasource").Value
dr("LinkDBTable") = adoTbl.Properties("Jet OLEDB:Remote Table Name").Value
TblList.Rows.Add(dr)
adoTbl.Properties.Refresh()
dr = TblList.NewRow()
End If
Next
dr = Nothing
adoTbl = Nothing
adoCat.ActiveConnection.Close()
adoCat = Nothing
| 
