Problems with DSN-less connections

[jmtitus]

I bought this book for Access VBA programming primarily to refresh my knowledge and learn some new stuff. I am currently try to learn how to use DSN-less connections. None of the examples work for me. I keep getting the "Data source name not found error".

Here is the code I'm using to connect to a text file:

Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim fld As ADODB.Field

Set conn = New ADODB.Connection

'I get the error here.
conn.Open "DRIVER={Microsoft Text Driver(*.txt;*.csv)};" & _
"Dbq=" & CurrentProject.path & "\" & _
"Extensions=asc,csv,tab,txt"

Set rst = New ADODB.Recordset
rst.Open "Select * from [Employees.txt]", conn, adOpenStatic, _
adLockReadOnly, adCmdText
Do Until rst.EOF
For Each fld In rst.Fields
Debug.Print fld.Name & "=" & fld.Value
Next fld
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
conn.Close
Set conn = Nothing

The same thing occurs for other data sources as well, dBase and SQL Server. I've tried various changes and checked directories. Also checked to see if ODBC drivers are installed. Everything appears to be ok.

Can any one help me?
Thanks,
jmtitus

 

[ByteMyzer]

That's because you are using the ODBC DSN-less connection string syntax, which will work with DAO but not necessarily with ADO.

Try the following modification to your conn.Open statement:

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
    & CurrentProject.Path _
    & "\;Extended Properties=""text;HDR=Yes;FMT=Delimited"";"

 

[jmtitus]

Thank you very much for your reply. I have had success connecting this way and there are examples in this book that does it in just this way.

Since I'm in a learning mode I was wondering how to do this by bypassing the Jet database engine. I have referenced the the Microsoft ADO Ext. 2.8 for DDL and Security in my Visual Basic environment. Have also seen a number of examples in my internet research but nothing seems to work for me.

Thanks again.