Working with dBASE files using ADO

[Vladkara]

Please, help!
Working extensively with Clipper for past 5 years.
I have several questions:
1. Can ADO do all the things with Clipper files such as
relationships, etc.?
2. OR, do I have to convert all my files into .mdb format?
3. I have already got (from this BB) the information how to
access dBase (Clipper) files, however the only commands that work so far are the .MoveNext and .MoveFirst
.MovePrevious and .MoveLast gives me an error.
4. Where do I get most of the information necessary.
All books that I have deal with examples where dBase files are not used.
Even the information about dBase data access that I got from this BB uses a command recordset.execute
>>> Set rs = cn.execute("Select * from Filename")<<<
which I cannot find in any of my books on VB6.
VEK

[paulcat]

If you are no longer going to use the Clipper & dBase files, I strongly suggest you convert them to Access, which is very easy to do. Otherwise, if you must keep them in their original format, then I have had some luck using the following format:
If the form load event, enter:
Set db = BEngine.Workspaces(0).OpenDatabase("path",False, False,"dBase III")

(where path = the path to the dbf but not the dbf name, and dBase III is the type of database - dBase IV, dbase 5.0, etc) - then,

Set rs = db.OpenRecordset("dbfname",dbOpenTable)
now you can use the recordset with most methods, although I haven't been able to use an index or seek method yet.

Good luck. You might want to check out ISBN 1-57169-104-9
It's for VB5, but it's pretty good.

[Clunietp]

Hey vlad, here's some code that uses the ADO recordset object and connects to DBASE IV file. You can fully scroll, but I have yet to see anyone that is able to update a DBF file via ADO. I've tried many different combinations of cursortypes, locations, and locktypes, but I get an error every time I try to update (via ADO cursor or SQL update command)

Oh well, at least you can read the file and scroll thru it:

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    
    Set cn = New ADODB.Connection
    
    'open connection to FOLDER with DBF file
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & ";Extended Properties=DBase IV"
    
    Set rs = New Recordset
    
   'the DBF file name goes here
    rs.Open "Select * from Customers", cn, adOpenStatic, adLockReadOnly, adCmdText
    
    MsgBox rs.Fields(1).Value
    
    rs.MoveNext 'scrolling is no problem
    
    MsgBox rs.Fields(1).Value
    
    rs.Close
    cn.Close
    
    Set rs = Nothing
    Set cn = Nothing

Tom