Solved

ADO & dBase problem with opening a recordset

Posted on 2001-08-18
6
1,845 Views
Last Modified: 2013-11-24
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



0
Comment
Question by:mikelpg
6 Comments
 
LVL 14

Accepted Solution

by:
wsh2 earned 200 total points
ID: 6402848
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
 

Author Comment

by:mikelpg
ID: 6404638
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
 
LVL 14

Expert Comment

by:wsh2
ID: 6404662
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 14

Expert Comment

by:wsh2
ID: 6404674
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6404682
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
 
LVL 2

Expert Comment

by:idorey
ID: 6405229
Posted to both Threads

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

Good Luck
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now