Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ADO & dBase problem with opening a recordset

Posted on 2001-08-18
6
Medium Priority
?
1,884 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 800 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

722 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