Connect to dBase .dbf file without ODBC ? 
Author Message
 Connect to dBase .dbf file without ODBC ?

is it possible connect directly to dBase .dbf file using ADO , not through
ODBC  ?

    thanks in advice...



Mon, 06 Sep 2004 23:30:29 GMT  
 Connect to dBase .dbf file without ODBC ?
You can use JET.

If you drag and drop  your Dbase files onto this free browser
(www.adoanywhere.com/fab) it will open them and give connection string
details according to the version of DBase you use.

Your biggest problem with Dbase in ADO is the index files but you would have
those issues using ODBC anyway I think. Don't expect it to be fast..

--
Michael Collier Bsc (Hons)

Interactive ADO Browser Trial  www.adoanywhere.com/cnet
Free ADO Browser (FAB)  www.adoanywhere.com/fab
Home Page www.adoanywhere.com

--


Quote:
> is it possible connect directly to dBase .dbf file using ADO , not through
> ODBC  ?

>     thanks in advice...



Tue, 07 Sep 2004 00:02:32 GMT  
 Connect to dBase .dbf file without ODBC ?

is it possible connect directly to dBase .dbf file using ADO , not through
ODBC  ?

You can use the Jet OLE DB provider to do this:

Sub ADOO{*filter*}AMDatabase()

    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset

    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Program
Files\Microsoft Office\Office;Extended Properties=dBase III;"

    rst.Open "Select * from Customer", cnn, adOpenForwardOnly, adLockReadOnly

        '...

    rst.Close

    cnn.Close

End Sub


Microsoft MVP (Visual Basic)



Tue, 07 Sep 2004 04:05:02 GMT  
 Connect to dBase .dbf file without ODBC ?


Fri, 19 Jun 1992 00:00:00 GMT  
 Connect to dBase .dbf file without ODBC ?


Tue, 07 Sep 2004 13:46:36 GMT  
 Connect to dBase .dbf file without ODBC ?


Fri, 19 Jun 1992 00:00:00 GMT  
 Connect to dBase .dbf file without ODBC ?
Paul,
What is a good source to get info on connection string properties such as the
"extended properties" that you had in your example?  I only had one book that
even mentioned this property in a code example and it was an empty string value.

I am coming from a dao background and I have a new project where I am taking a
dBase III project and converting it to a windows app with an Access database and
a VB6 coded user interface.   I need to be able to import these dBase files into
the new access database using compile code as a install/update utility.  I'm
wanting to use the TransferDatabase method but I have to get connected to the
dBase files in order to do this.  I'll try your extended properties thing and see
how that does.

Thanks,
thomas

Quote:


> is it possible connect directly to dBase .dbf file using ADO , not through
> ODBC  ?
>

> You can use the Jet OLE DB provider to do this:

> Sub ADOO{*filter*}AMDatabase()

>     Dim cnn As New ADODB.Connection
>     Dim rst As New ADODB.Recordset

>     cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Program
> Files\Microsoft Office\Office;Extended Properties=dBase III;"

>     rst.Open "Select * from Customer", cnn, adOpenForwardOnly, adLockReadOnly

>         '...

>     rst.Close

>     cnn.Close

> End Sub


> Microsoft MVP (Visual Basic)



Sat, 18 Sep 2004 00:54:11 GMT  
 Connect to dBase .dbf file without ODBC ?
Paul,
Thanks for the offer of help.  I guess that I am not getting the syntax correct.
Here is what I want to do.  I want to be able to connect to dBASE III files (plus
the "officemgr.mdb")
using the jet oledb 4.0 provider with the dBase III extensions and then do a
TransferDatabase on them.
Let me give you the source and target databases and tables.

source directory = "c:\officemgr\"
source dbase file = "c:\officemgr\client.dbf"

target directory = "c:\ado_officemgr\"
target access mdb = "c:\ado_officemgr\officemgr.mdb"
target table = "tblClient"

I had tried the following and it didn't work(it complained that the data source
path was incorrect):
' form declarations
Dim loConn As Connection
Dim loConn2 As Connection
Dim loSQL As String
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Private Sub cmdTransferDbTable_Click()

loConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source='c:\officemgr\client.dbf';" _
            & "Extended Properties=dBASE III;"

' loConn2.Open "Provider=Mi
DoCmd.TransferDatabase acExport, "dBASE III", "c:\ado_officemgr\officemgr.mdb",
acTable, "c:\officemgr\client.dbf", "tblClient"
MsgBox "done transferring"
End Sub
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Private Sub Form_Load()
Set loConn = New Connection
Set loConn2 = New Connection

End Sub

Thanks,
thomas

Quote:


> is it possible connect directly to dBase .dbf file using ADO , not through
> ODBC  ?
>

> You can use the Jet OLE DB provider to do this:

> Sub ADOO{*filter*}AMDatabase()

>     Dim cnn As New ADODB.Connection
>     Dim rst As New ADODB.Recordset

>     cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Program
> Files\Microsoft Office\Office;Extended Properties=dBase III;"

>     rst.Open "Select * from Customer", cnn, adOpenForwardOnly, adLockReadOnly

>         '...

>     rst.Close

>     cnn.Close

> End Sub


> Microsoft MVP (Visual Basic)



Sat, 18 Sep 2004 21:58:48 GMT  
 Connect to dBase .dbf file without ODBC ?

Paul,
Thanks for the offer of help.  I guess that I am not getting the syntax correct.
Here is what I want to do.  I want to be able to connect to dBASE III files (plus
the "officemgr.mdb")
using the jet oledb 4.0 provider with the dBase III extensions and then do a
TransferDatabase on them.
Let me give you the source and target databases and tables.

source directory = "c:\officemgr\"
source dbase file = "c:\officemgr\client.dbf"

target directory = "c:\ado_officemgr\"
target access mdb = "c:\ado_officemgr\officemgr.mdb"
target table = "tblClient"



I had tried the following and it didn't work(it complained that the data source
path was incorrect):
' form declarations
Dim loConn As Connection
Dim loConn2 As Connection
Dim loSQL As String
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Private Sub cmdTransferDbTable_Click()

loConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source='c:\officemgr\client.dbf';" _
            & "Extended Properties=dBASE III;"

' loConn2.Open "Provider=Mi
DoCmd.TransferDatabase acExport, "dBASE III", "c:\ado_officemgr\officemgr.mdb",
acTable, "c:\officemgr\client.dbf", "tblClient"
MsgBox "done transferring"
End Sub
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Private Sub Form_Load()
Set loConn = New Connection
Set loConn2 = New Connection

End Sub

A correction to your code (the TransferDatabase arguments) was replied to via
email.


Microsoft MVP (Visual Basic)



Sun, 19 Sep 2004 02:27:29 GMT  
 Connect to dBase .dbf file without ODBC ?

Try the following:

Sub ImportdBaseToAccess()

Dim cnn As New ADODB.Connection
Dim sqlString As String

' Open the Connection
cnn.Open _
   "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=d:\My Documents\db1.mdb;"
& _
   "Jet OLEDB:Engine Type=4"

'Creates new Access table
sqlString = "SELECT * INTO [Table5] FROM [dBase III;DATABASE=d:\My
Documents\dBase].[dBase1]"
'Existing Access table
'sqlString = "INSERT INTO [Table3] SELECT * FROM [dBase III;DATABASE=d:\My
Documents\dBase].[dBase1]"
cnn.Execute sqlString

cnn.Close
Set cnn = Nothing

End Sub

Paul
~~~~

<<
Paul,
I am using VB and I was wondering if there is an equivalent VB alternative to
the TransferDatabase.  I had thought that the transferdatabase method could be
run from  VB by making an Access Objects 10.0 reference and then using the DoCmd
to
launch it; however I'm thinking that maybe the databasetransfer method may not
see the database as connected since it said the database wasn't open.

My task is to take a dos application that was developed in dBASE III and to
convert the database to access and to create the interface with VB.  My last
project had been a DAO type but I thought that this time I'd use ADO.  The
current users will need to have a utility to copy their database tables to an
access mdb.

What approach do you think would be best to copy these dBASE III files to a
access database?

Thanks and best regards,
thomas
<<


Microsoft MVP (Visual Basic)



Sun, 19 Sep 2004 04:48:20 GMT  
 Connect to dBase .dbf file without ODBC ?
Paul,
Thanks so much. Here's what I came up with.

Best regards,
thomas

'*****************************************************************************************************

Sub dBaseToAccess_Import_CreateTable(TargetDb As String, TargetTable As String, _
                                       SourceDir As String, SourceFile As String)
'  TargetDb string to pass ---------> "C:\OfficeMgr\OfficeMgr1.mdb"
'  TargetTable string to pass ------> "tblClient"
'  SourceDir string to pass --------> "c:\dBASE_Client\"
'  SourceFile string to pass -------> "CLIENT"
'  Creates a table in the specified Access table from the specified SourceDir and
SourceFile
'*****************************************************************************************************

Dim cnn As New ADODB.Connection
Dim sqlString As String

' Open the Connection
cnn.Open _
   "Provider=Microsoft.Jet.OLEDB.4.0;" _
      & "Data Source=" & TargetDb & ";" _
      & "Jet OLEDB:Engine Type=4"

' Creates new Access table and transfers data using the SELECT..INTO
sqlString = "SELECT * INTO [" & TargetTable & "] FROM " _
            & "[dBase III;DATABASE=" & SourceDir & "].[" & SourceFile & "]"

cnn.Execute sqlString
cnn.Close
Set cnn = Nothing

End Sub

'*****************************************************************************************************

Sub dBaseToAccess_Import_ExistingTable(TargetDb As String, TargetTable As String, _
                                       SourceDir As String, SourceFile As String)
'  TargetDb string to pass ---------> "C:\OfficeMgr\OfficeMgr1.mdb"
'  TargetTable string to pass ------> "tblClient"
'  SourceDir string to pass --------> "c:\dBASE_Client\"
'  SourceFile string to pass -------> "CLIENT"
'  Creates a table in the specified Access table from the specified SourceDir and
SourceFile
'*****************************************************************************************************

Dim cnn As New ADODB.Connection
Dim sqlString As String

' Open the Connection
cnn.Open _
   "Provider=Microsoft.Jet.OLEDB.4.0;" _
      & "Data Source=" & TargetDb & ";" _
      & "Jet OLEDB:Engine Type=4"

' Transfer in Existing Access table with INSERT..INTO
 sqlString = "INSERT INTO [" & TargetTable & "] SELECT * FROM " _
            & "[dBase III;DATABASE=" & SourceDir & "].[" & SourceFile & "]"

cnn.Execute sqlString

cnn.Close
Set cnn = Nothing

End Sub

===================================================================================

Quote:


> Try the following:

> Sub ImportdBaseToAccess()

> Dim cnn As New ADODB.Connection
> Dim sqlString As String

> ' Open the Connection
> cnn.Open _
>    "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=d:\My Documents\db1.mdb;"
> & _
>    "Jet OLEDB:Engine Type=4"

> 'Creates new Access table
> sqlString = "SELECT * INTO [Table5] FROM [dBase III;DATABASE=d:\My
> Documents\dBase].[dBase1]"
> 'Existing Access table
> 'sqlString = "INSERT INTO [Table3] SELECT * FROM [dBase III;DATABASE=d:\My
> Documents\dBase].[dBase1]"
> cnn.Execute sqlString

> cnn.Close
> Set cnn = Nothing

> End Sub

> Paul
> ~~~~

> <<
> Paul,
> I am using VB and I was wondering if there is an equivalent VB alternative to
> the TransferDatabase.  I had thought that the transferdatabase method could be
> run from  VB by making an Access Objects 10.0 reference and then using the DoCmd
> to
> launch it; however I'm thinking that maybe the databasetransfer method may not
> see the database as connected since it said the database wasn't open.

> My task is to take a dos application that was developed in dBASE III and to
> convert the database to access and to create the interface with VB.  My last
> project had been a DAO type but I thought that this time I'd use ADO.  The
> current users will need to have a utility to copy their database tables to an
> access mdb.

> What approach do you think would be best to copy these dBASE III files to a
> access database?

> Thanks and best regards,
> thomas
> <<


> Microsoft MVP (Visual Basic)



Mon, 20 Sep 2004 23:22:31 GMT  
 
 [ 11 post ] 

 Relevant Pages 

1. Opening .DBF (dBASE) file as ODBC datasource

2. Cant connect to dbase file using odbc

3. connecting to dBase III dbf table with VB 6

4. Problems adding new records to ODBC source (dBASE III dbf's)

5. Connecting to dbf via ODBC

6. Using dBase IV with VB via ODBC without a DSN

7. Powerbasic 3.1 sourcecode - to view dbase(*.dbf) files

8. long name dbase files (*.dbf)

9. How to delete a recordset of dBASE IV DBF file use VB4

10. Is a DBF a dBase file or FoxPro?

11. Using .DBF DBASE III files and Deleting

12. ADO connection string for Dbase III file (*.dbf)

 

 
Powered by phpBB® Forum Software