Solved

ADO & dBase problem with opening a recordset

Posted on 2001-08-18
6
1,863 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Viewers will learn the different options available in the Backstage view in Excel 2013.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

752 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