Extracting Data from Access to DBF - Xtreme Visual Basic Talk

12-11-2002, 09:15 AM

Tadhg

Guest

Posts: n/a

Extracting Data from Access to DBF

Hello!
I am trying to set up a small application in VB that will allow me to extract data from an Access database and export this data to a DBF IV file.

I can read the data from Access via ADO - and have managed to export this data to txt files.

However, I cannot find any info on how to export to DBF files anywhere.

Any help would be much appreciated.

12-11-2002, 10:48 AM

Memnoch1207 Memnoch1207 is offline

Junior Contributor

Join Date: May 2002

Location: Guess! It's really HOT!!

Posts: 314

post your code for the export routine.

__________________
He who laughs last...Thinks slowest.

12-12-2002, 10:18 AM

Tadhg

Guest

Posts: n/a

Here is a sample of the code in use. Instead of writing to a txt file, I would like to write to DBF IV format.

strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\temp\acra\261102\mig.mdb;"

rsinv.Open "SELECT InvoiceHeader.CustomerID, InvoiceHeader.InvoiceNumber," & _
& "InvoiceHeader.ShipmentDate", strConnectionString, adOpenKeyset

strInvData = rsinv.GetString(adClipString, , ",", Chr(13))

'write the invoice sanal data to a txt file
Open "c:\temp\vbtemp\sanal.txt" For Append As #1
Write #1, strInvData
Close #1

12-13-2002, 11:49 AM

Memnoch1207 Memnoch1207 is offline

Junior Contributor

Join Date: May 2002

Location: Guess! It's really HOT!!

Posts: 314

it seems that you will need to create a disconnected recordset to hold the data from the mdb database. Then run an insert statement to insert the data into the .dbf file.

__________________
He who laughs last...Thinks slowest.

12-16-2002, 05:12 AM

Tadhg

Guest

Posts: n/a

would you mind posting some sample code to do this?
Thanks for your help.

12-17-2002, 09:26 AM

Memnoch1207 Memnoch1207 is offline

Junior Contributor

Join Date: May 2002

Location: Guess! It's really HOT!!

Posts: 314

Here is some sample code.

Code:

Dim Conn As ADODB.Connection
Dim Rs As ADODB.Recordset

' Create instance of connection object and then open the
' connection.
Set Conn = New ADODB.Connection
Conn.Open "DSN=SQLServer", "sa", ""

' Create instance of recordset object and open the
' recordset object against a table.
Set Rs = New ADODB.Recordset

' Setting the cursor location to client side is important
' to get a disconnected recordset.
Rs.CursorLocation = adUseClient
Rs.Open "Select * from Table1", _
         Conn, _
         ADODB.adOpenForwardOnly, _
         ADODB.adLockBatchOptimistic

' Disconnect the recordset.
Set Rs.ActiveConnection = Nothing

' Get the value of one of the fields from the recordset
' after disconnection.
Dim v
v = Rs.Fields(0).Value
MsgBox v

Conn.Close

' Get the value of one of the fields from the recordset
' after closing the connection to ensure that you have a
' disconnected recordset.
v = Rs.Fields(0).Value
MsgBox (v)

' Now edit the value and save it.
Rs.Fields("au_lname").Value = "NewValue"

' Now reopen the connection and attach it to the recordset.  Update
Set Conn = New ADODB.Connection
Conn.Open "DSN=DBSql", "sa", ""
Rs.ActiveConnection = Conn
Rs.UpdateBatch

Rs.Close
Conn.Close
Set Rs = Nothing
Set Conn = Nothing

__________________
He who laughs last...Thinks slowest.