ADO & dBase problem with opening a recordset

I have been successful in opening the connection of a dbase file using VB6 & ADO by several different methods. However, I get a error when trying to open the recordset. I have been trying to get past this error for a full day now and sure could use some advice. It gives me this error when I try to open the recordset: (ORDERS is a dbf file in the u:\drs\data\ directory
---------------------------------------------
Run-time error'-2147217900(80040e14)':

View 'ORDERS' has not been defined in catalog 'u:\drs\data'.
SQLSTATE=42502
----------------------------------------------

here is the code that I am executing:

Dim c1 As ADODB.Connection, r1 As ADODB.Recordset
Set c1 = New ADODB.Connection
Set r1 = New ADODB.Recordset
c1.Open "Provider=MSIDXS;Data Source=u:\drs\data"
r1.Open "select * from orders", c1, adOpenDynamic, adLockPessimistic



mikelpgAsked:
Who is Participating?
 
wsh2Commented:
Try This..

'  Using the ODBC Driver
   Dim strConnect As String
   strConnect = "Driver={Microsoft dBASE Driver (*.dbf)};" _
      & "DriverID=277;" _
      & "Dbq=U:\DRS\DATA;"
   oConn.Open strConnect

'  Specify the filename in the SQL statement
   Dim strSQL As String
   strSQL = "SELECT * FROM ORDERS.DBF "
   oRs.Open strSQL, oConn, , ,adCmdText

From MSDN:

MDAC 2.1 (or greater) requires the Borland Database Engine (BDE) to update dBase DBF files.
http://support.microsoft.com/support/kb/articles/Q238/4/31.ASP

dBASE Driver Programming Considerations
http://msdn.microsoft.com/library/en-us/odbc/htm/odbcjetdbase_programming_considerations.asp

 




0
 
mikelpgAuthor Commented:
wsh2,

the code you sent gets me past the error I was getting but I am using dbase III rather than dbase IV.  Does that make a difference in the connect string? (I changed the code to:  DriverID=21)

anyway, now I am getting a error trying to assigh a index.  I have a ORDERS.INF file with this in it:

[dBase III]
NDX1=ORD_NUM.NDX

[UIDX1 ORDER_NUM]

ord_num.ndx is the index name and order_num is the field that it is indexed on.

I get the following error when executing this code

Set cOrders = New ADODB.Connection

cOrders.ConnectionString = "Driver={Microsoft dBASE Driver (*.dbf)};DriverID=21;Dbq=u:\drs\data"

cOrders.Open

Set rsOrd = New ADODB.Recordset

rsOrd.Open "SELECT * FROM ORDERS.DBF ", cOrders,adOpenForwardOnly, adLockOptimistic, adCmdText

rsOrd.Index = "u:\drs\data\ORD_NUM.ndx"


Run-time error 3251':

Current provider does not support the necessary interface for Index functionality.

Mikel
0
 
wsh2Commented:
2 Questions:

1. Why do you need the Index? (Index is now somewhat old.. use the Recordset Find or Filter command instead)  

And

2. How are you accessing this file? (ReadOnly or Update)
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
wsh2Commented:
mikelpg:
This is NOT that hard.. just hang in there.. ok?... <wink>.

Oh.. and if you can elaborate a little bit more on what you want to do.. AND perhaps.. EMail me a zipped up sample of your DBF (wsh2@aol.com).. then perhaps we can get through this easier.. <smile>.

   
0
 
Anthony PerkinsCommented:
Reposted from other thread:

I think the error message is quite self-explanatory.  In any case you cannot set an index with the provider
and type of recordset you are using.  Certainly you can never set an index to a recordset that is a
Select statement, even with those providers such as Jet 4.0 (with Access 2000) that do support this
property.

So if you really need to set this property you need to change your code to:

rsOrd.CursorLocation = adClient
rsOrd.Open "ORDERS.DBF ",cOrders,adOpenForwardOnly, adLockOptimistic, adCmdTableDirect

Debug.Print rsOrd.Suports(adIndex)

This will tell you if it is going to work.

I believe there are better ways of skinning this cat, but I will need more information.
0
 
idoreyCommented:
Posted to both Threads

Add the dbase files as ODBC datasources and then connect to them?

Good Luck
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.