rajesh_khater
asked on
.dbf file does not open using ADO
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 ?
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 ?
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
Visual FoxPro ODBC Driver
http://msdn.microsoft.com/vfoxpro/downloads/updates/odbc/default.aspx
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\mySo
"Exclusive=No"
ODBC Driver for Visual FoxPro DSN-less Connection Strings
oConn.Open "Driver={Microsoft Visual FoxPro Driver};" & _
"SourceType=DBF;" & _
"SourceDB=c:\somepath\mySo
"Exclusive=No"
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)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
try:
rs.Open "SELECT * FROM fo28MAR2005bhav" , dbConn, adOpenStatic, adLockReadOnly
without the .dbf. all dbfs in the path are all considered tables
rs.Open "SELECT * FROM fo28MAR2005bhav" , dbConn, adOpenStatic, adLockReadOnly
without the .dbf. all dbfs in the path are all considered tables
disregard previous comment. acperkins is correct. you cannot use long file names. it will only accept 8.3 format file names.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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\;Coll
what does .1 after vfpoledb stand for ? I have sent you the file in email.
thnx
Hi rdwillet .. in your example of connection string:
conn.Open "Provider=vfpoledb.1;Data Source=C:\SomeSource\;Coll
what does .1 after vfpoledb stand for ? I have sent you the file in email.
thnx
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.CONNECTIO
o.Open('Provider=VFPOLEDB;
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.
o = NEWOBJECT('ADODB.CONNECTIO
o.Open('Provider=VFPOLEDB;
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.
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.
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.
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
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
ASKER
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 ..
thnx carl.
rdwillett .. u mailed me the correct project file too late .. still I give you points ..
Great! I have toyed with that same latest OLE DB Provider and it seems pretty solid.
SELECT * FROM myTable