I'm following the book "Access 2016 Programming By Example With vba, xml and ASP. The book teaches me VBA, I'm still a noob. This chapter covers the basics of setting up a user-dns. This user-dns will be user by ms-access vba to connect via this user-dns to a .dbf-file. I'm on Windows 10 64bit with office 2016 64 bit. The code in the book results in an error: 'the driver architecture and the application do not align with the DNS'. Google nor the book provide a suitable solution. I'm thinking it has something to do with the bit versions of Windows or Office. Please advice.
The book mentions the code below, and describes to open configuration screen, odbc, user dns, select "Microsoft dBase driver (*.dbf) name it "MyDbaseFile" and finish.
Difference with book and Windows 10 is that there are two windows for ODBC settings, one 32bit and one 64bit. Only within the 32bit window, I can find the described driver name. In the 64bit window I cannot find it. The book describes only one window so I think that's based on W7 or something else.
Total code from the book:
Sub Open_AndRead_dBaseFile()
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Set conn = New ADODB.Connection
conn.Open "Provider=MSDASQL;DSN=MyDbaseFile;"
Debug.Print conn.ConnectionString
Set rst = New ADODB.Recordset
rst.Open "CUSTOMER.dbf", conn
Do Until rst.EOF
Debug.Print rst.Fields(1).Value
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
conn.Close
Set conn = Nothing
End Sub
Expecting to show in immidiate window in VBA editor the content of customer.dbf. Error message rises as mentioned earlier, this rises with the code line:
conn.Open "Provider=MSDASQL;DSN=MyDbaseFile;"
Reinstalled office 32 bit, works like a charm now.