Solved

ADO & dBase problem with opening a recordset

Posted on 2001-08-18
6
1,854 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

860 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