I am trying to access a Dbase5 database from VB6, but I'm thumping my head against the wall.
I have a DSN called GM setup in ODBC settings.
This code works, gets the entire database, and I can work with RS as needed:
Dim StrCn As String
Dim Wrkodbc As Workspace
Dim Con As Connection
Dim SQL As String
Dim Rs As Recordset
StrCn = "ODBC;DSN=GM;UID=;PWD="
Set Wrkodbc = CreateWorkspace("", "admin", "", dbUseODBC)
Set Con = Wrkodbc.OpenConnection("", , , StrCn)
SQL = "select * from CONTACT1 "
Set Rs = Con.OpenRecordset(SQL, dbOpenDynamic)
If I take the exact same code as above, with just the SQL line changed I get a 3146 DAO error on the Set RS = . (The ultimate generic error in DAO)
None of these work:
SQL = "select * from CONTACT1 Order by LASTNAME "
SQL = "select * from CONTACT1 Order by CONTACT1.LASTNAME "
SQL = "select * from CONTACT1 where LASTNAME = 'TEST' "
SQL = "select * from CONTACT1 where CONTACT1.LASTNAME = 'TEST' "
I've tried opening up the tables with DbOpenForwardOnly, dbreadonly, no change. I've tried every darn thing I can think of. Before you ask LASTNAME is a column in the Dbase file. I've also replaced LASTNAME with out 20 different column names from the CONTACT1.DBF.
As a side note, this works:
SQL = "select * from CONTACT1.DBF "
I've also gone down the ADO route, which I can get to work, however I can't make it support accessing to the DB while other programs are accessing the DB. I really need multi user and ADO seems to lock the file when working with DBASE.
My test PC has the borland BDE loaded 5.01, and I'm on the newest sp for Jet, the one included w XP SP2.