?
Solved

.dbf file does not open using ADO

Posted on 2005-04-01
15
Medium Priority
?
1,130 Views
Last Modified: 2013-12-25
Hi .. I have a .dbf file, which opens in Foxpro For DOS 2.6, Excel 2003 and DBF Manager, but it does not open in Access 2003 and my VB program using ADO.

What can be the problem ? How can I open that dbf file using ADO ??
I wish I could upload the .dbf file in this forum, but I dont think there's a way to do this.

The .dbf file name is fo28MAR2005bhav.dbf.  Access 2003 and my VB program using ADO both give this error:

The Microsoft Jet database engine could not find the object 'fo28MAR2005bhav.dbf'. Make sure the object exists and that you spell its name and the path name correctly.


I am using this connection string:


    dbConn.Open "Driver={Microsoft dBASE Driver (*.dbf)};" & _
           "DriverID=277;" & _
           "Dbq=F:\Guwahati Projects\Punter\Samples"


After opening the connection, I am reading records using the following code:
    Set rs = New ADODB.Recordset
    rs.Open "SELECT * FROM fo28MAR2005bhav.dbf" , dbConn, adOpenStatic, adLockReadOnly

The line containing rs.Open gives the above error.

Any ideas ?
0
Comment
Question by:rajesh_khater
[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
  • 2
  • 2
  • +3
15 Comments
 
LVL 29

Expert Comment

by:leonstryker
ID: 13683123
What is the table name?  

SELECT * FROM myTable
0
 
LVL 33

Expert Comment

by:CarlWarner
ID: 13683153
Instead of an outdated dBASE driver, download and install the latest FoxPro ODBC driver and use that instead.  A dBASE driver will not always successfully open a FoxPro table/.dbf.

Visual FoxPro ODBC Driver
http://msdn.microsoft.com/vfoxpro/downloads/updates/odbc/default.aspx
0
 
LVL 33

Expert Comment

by:CarlWarner
ID: 13683292
From http://www.able-consulting.com/MDAC/ADO/Connection/ODBC_DSNLess.htm#ODBCDriverForVisualFoxPro

ODBC Driver for Visual FoxPro DSN-less Connection Strings

oConn.Open "Driver={Microsoft Visual FoxPro Driver};" & _
           "SourceType=DBF;" & _
           "SourceDB=c:\somepath\mySourceDbFolder;" & _
           "Exclusive=No"
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13685086
I suspect what is happening is that you are using long file names for the path (F:\Guwahati Projects\Punter\Samples) and/or the database (fo28MAR2005bhav.dbf)
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 400 total points
ID: 13685090
And I should have added they are not supported by the ODBC driver.
0
 
LVL 6

Expert Comment

by:PePi
ID: 13685431
try:

rs.Open "SELECT * FROM fo28MAR2005bhav" , dbConn, adOpenStatic, adLockReadOnly

without the .dbf. all dbfs in the path are all considered tables
0
 
LVL 6

Expert Comment

by:PePi
ID: 13685484
disregard previous comment. acperkins is correct. you cannot use long file names. it will only accept 8.3 format file names.
0
 
LVL 33

Accepted Solution

by:
CarlWarner earned 800 total points
ID: 13685645
Since you are going to use ADO any way, go ahead and use the OLE DB Provider that will easily get to that table, even with a long file name.

Microsoft OLE DB Provider for Visual FoxPro
http://www.microsoft.com/downloads/details.aspx?FamilyID=e1a87d8f-2d58-491f-a0fa-95a3289c5fd4&DisplayLang=en
0
 
LVL 4

Assisted Solution

by:rdwillett
rdwillett earned 800 total points
ID: 13686676
   Dim sCn As New ADODB.Connection
    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim ssql As String
    Dim cnstr As String
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset
             

    conn.Open "Provider=vfpoledb.1;Data Source=C:\SomeSource\;Collating Sequence=general"  'PathOnly
    'If conn.State = adStateOpen Then MsgBox "Dbf Opened"
   
    rs.Open "SELECT * FROM YourTable ", conn, adOpenForwardOnly, adLockReadOnly   'Table name without dbf


definitely download new driver
e-mail file to me at rdwillett@comcast.net if u can open it and i will figure it out
0
 
LVL 1

Author Comment

by:rajesh_khater
ID: 13687610
hi Carl .. what should be the connection string if I use the OLE DB Provider  for Visual Foxpro ? Will it be able to open .dbf created in dbase or in foxpro for DOS 2.6 ?

Hi rdwillet .. in your example of connection string:

conn.Open "Provider=vfpoledb.1;Data Source=C:\SomeSource\;Collating Sequence=general"  'PathOnly

what does .1  after vfpoledb stand for ? I have sent you the file in email.

thnx
0
 
LVL 33

Expert Comment

by:CarlWarner
ID: 13688074
You can use the actual file name of the Provider for VFP OR the English description that would show up for it in the Data Link Properties list that shows all OLE DB Providers on your PC.  I use the file name in this example where I was able to setup an active ADO connection right in the Visual FoxPro Command Window (your syntax will be slightly different outside of VFP, but the provider name will be the same):

o = NEWOBJECT('ADODB.CONNECTION')
o.Open('Provider=VFPOLEDB;Data Source=C:\Documents and Settings\Administrator\My Documents\Visual FoxPro Projects\')
ors = o.Execute("USE SSCOLS")

Here's a site reference for OLE DB Provider Connection strings:

OLE DB Provider for Visual FoxPro
http://www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#OLEDBProviderForVisualFoxPro

However, the example they show for a VFP connection is more complex than what I showed mainly because it is referencing an entire database collection of .dbfs rather than just referencing a single free table .dbf that has no master database/.dbc.
0
 
LVL 33

Expert Comment

by:CarlWarner
ID: 13688191
From OLE DB Programmer's Reference, Connection String Syntax at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/oledb/htm/oledbconnectionstringsyntax.asp

The Provider Keyword
The Provider keyword identifies the OLE DB provider to be used. To specify your OLE DB provider, set the value of the Provider keyword to the VersionIndependentProgID value of the provider.

Provider=MSDASQL

The value can also be set to the ProgID of the provider, which may have a version attached to it.

Provider=MSDASQL.1
If two versions of a provider are installed on the same system, use the ProgID to specify exactly which version to use. If two versions are installed on a system and the VersionIndependentProgID value is specified, the most recent version of the provider is used.
0
 
LVL 4

Expert Comment

by:rdwillett
ID: 13688249
E-mailed project to you.  Which demonstrates several different techniques including:
Displaying results in a grid --> which also has order by FieldName and ASC/DESC
How to refresh ADODC control and Datagrid
How to obtain FieldNames from a dbf
How to obtain recordcount
0
 
LVL 1

Author Comment

by:rajesh_khater
ID: 13789039
using the OLEDB provider solved the problem of long path names for me.

thnx carl.
rdwillett .. u mailed me the correct project file too late .. still I give you points ..
0
 
LVL 33

Expert Comment

by:CarlWarner
ID: 13791222
Great!  I have toyed with that same latest OLE DB Provider and it seems pretty solid.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
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.
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
Course of the Month8 days, 6 hours left to enroll

766 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