vbscriptdbfdbase

Connecting to a DBF in Visual Basic


We have a Windows application that stores data in DBF. I need to connect to a certain table a_sname.dbf and extract the SN_ACCOUNT, SN_CURRBAL and SN_CRLIM columns to a text file to be imported by another application.

The text file ideally needs to look like.

SN_ACCOUNT     SN_CURRBAL     SN_CRLIM
10             100            1000
20             200            2000

The above is an example only.

At the moment I am looking to at least get connected to the DBF.

I have BASH scripting with Oracle SQL knowledge but the Windows side is all new to me. I have the drive mapped to Z:\.


Update 1: Here is the script that I'm testing.

Option Explicit

Dim sDir : sDir = "Z:\"
Dim sCS  : sCS  = Join(Array( _
        "Provider=Microsoft.Jet.OLEDB.4.0" _
      , "Data Source=" & sDir _
      , "Extended Properties=""dBASE IV;""" _
), ";")
Dim oCN : Set oCN = CreateObject("ADODB.CONNECTION")
oCN.Open sCS
Dim oRS : Set oRS = oCN.Execute("SELECT SN_ACCOUNT,SN_CURRBAL,SN_CRLIM FROM a_sname.dbf")
Do Until oRS.EOF
   WScript.Echo oRS.Fields(0).Name, oRS.Fields(0).Value
   oRS.MoveNext
Loop
oCN.Close

I am getting the error message Microsoft JET Database Engine: External table is not in the expected format..


Update 2: Here is a screenshot of the table information of a_sname.dbf.

enter image description here


Update 3: It works! Here is the query that I am using.

SELECT SN_ACCOUNT,SN_CURRBAL,SN_CRLIM FROM a_sname.dbf

Here is the echo that I am using.

WScript.Echo oRS.Fields(0).Value, oRS.Fields(1).Value, oRS.Fields(2).Value.

Is it possible to pipe all this to a text file and have the columns spaced more evenly?

enter image description here


Solution

  • According to the source your connection string for the ADODB.CONNECTION should look something like this:

    Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\FileServer01\Server VFTP Dynamic\Data;
    Extended Properties=dBASE IV;User ID=Admin;Password=;
    

    Your first (test) query could be

    SELECT * FROM [a_sname.dbf]
    

    In case of trouble, I'd

    1. map \\FileServer01\Server VFTP Dynamic to a drive letter, change the connection string accordingly and try again
    2. use Data Sources to establish a connection interactively/with help of a GUI and use this experience to build the connection string

    Added:

    Some tested code to increase your confidence:

    Option Explicit
    
    Dim sDir : sDir = "\\gent\eh\...etc...\testdata\dbf"
    Dim sCS  : sCS  = Join(Array( _
            "Provider=Microsoft.Jet.OLEDB.4.0" _
          , "Data Source=" & sDir _
          , "Extended Properties=""dBASE IV;""" _
    ), ";")
    Dim oCN : Set oCN = CreateObject("ADODB.CONNECTION")
    oCN.Open sCS
    Dim oRS : Set oRS = oCN.Execute("SELECT TOP 2 * FROM [tblAbles]")
    Do Until oRS.EOF
       WScript.Echo oRS.Fields(0).Name, oRS.Fields(0).Value
       oRS.MoveNext
    Loop
    oCN.Close
    

    output:

    cscript 22934726.vbs
    RTATID 2
    RTATID 3
    

    Troubleshooting I:

    1. In the code you published, sDir contains a file spec ("Z:\a_sname.dbf"), not a folder/directory spec
    2. Windows ??, ?? Bit (do you need to work in a 32 Bit environment?)
    3. ADO Version? WScript.Echo CreateObject("ADODB.Connection").Version

    Troubleshooting II:

    1. Make sure a_sname.dbf is a valid .dbf (of version ?) by opening it using some other tool (Excel, Access, ODBC, ...)
    2. Tweak the "dBase IV" property

    Troubleshooting III:

    1. see here for getting the version from the file header

    2. use a GUI (e.g. Data Sources) to find possible values for "dBase XX"

    Troubleshooting (not dBase!) IV:

    1. According to the source, you can/must(?) use a VFP specific connection string: maybe
      Provider=vfpoledb;Data Source=C:\MyDataDirectory\;Collating Sequence=general;

    2. Perhaps you need to download a provider DLL (be carefull wrt versions!)