How to create a Linked (text) table in xl vba to a .mdb
 

Hello!

I´m trying to make a linked table to a text file using Excel VBA using ADO?
But I can´t make it to work. i only got "Cant find the path"

Please help me!

Here is my code:

Function CreateExternalTable()
Dim conn As ADODB.Connection
Dim tbl As ADOX.Table
Dim cat As ADOX.Catalog

On Error Resume Next
Kill "C:\Documents and Settings\Magnus\My Documents\Teknik i
Tiden\Proj 7" & "\GOTSTG.mdb"
On Error GoTo 0


Set conn = New ADODB.Connection
With conn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Documents and Settings\Magnus\My
Documents\Teknik i Tiden\Proj 7\GOTSTG.mdb;"
'.Open
End With

Set cat = New ADOX.Catalog
cat.Create conn

Set tbl = New ADOX.Table

With tbl
.Name = "GOTST_link"
Set .ParentCatalog = cat
.Properties("Jet OLEDB:Link Datasource") = "Data Source=C:\Documents and
Settings\Magnus\My Documents\Teknik i Tiden\Proj 7\GOTSTG_link.txt"
.Properties("Jet OLEDB:Link Provider String") = "Text;DSN=GOTSTG Link
Specification;FMT=Delimited;HDR=NO;IMEX=2;Characte rSet=1252;DATABASE=C:\Documents and Settings\Magnus\My Documents\Teknik i Tiden\Proj 7;TABLE=GOTSTG#txt"
'.Properties("Jet OLEDB:Remote Table Name") = "GOTSTG_link"
.Properties("Jet OLEDB:Create Link") = True
End With
cat.Tables.Append tbl
conn.Close

End Function

Thanks in advance