Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

.dbf file does not open using ADO

Posted on 2005-04-01
15
Medium Priority
?
1,158 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
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

569 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