Opening a dbf (DBASE) file using vba in AutoCad

[OceanaPolynom]

Hello

I need to access dbf files from AutoCad using vba.  Here is my code that doesn't work

'http://www.myengineeringworld.net/2013/09/read-dbf-files-using-vba.html
    Dim con         As Object
    Dim rs          As Object
    Dim sql         As String
    Dim i           As Integer
    Dim j           As Integer

    
  CommonDialog1.DialogTitle = "Open .dbf data base file."
  CommonDialog1.ShowOpen
  ft$ = CommonDialog1.FileTitle:                          'ft$ is = to filename.dbf
  fn$ = CommonDialog1.FileName
  fnn$ = fn$
  c% = InStr(1, fn$, ft$)
  fn$ = Left(fn$, c% - 2):                                'fn$ is = to the path to filename.dbf without the
                                                          'final \ character               
                                                           
    'Create the ADODB connection object.
    Set con = CreateObject("ADODB.connection")

    'Open the connection.
     con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & fn$ & ";Extended Properties=dBASE IV;"
    
    'Create the SQL statement to read the file. 
    'Note that the filename is used instead of the table name.
     sql = "SELECT * FROM " & ft$ & ";"

    'Create the ADODB recordset object.
    Set rs = CreateObject("ADODB.recordset")
    
    'Open the recordset.
    rs.Open sql, con
    

The last statement creates an error saying that the database engine could not find the object filename.dbf.  I tried leaving the trailing \ character on the path name string, but I get the same error.  Does uppercase/lowercase text have any influence?  Some of the file names and path names have lowercase letters.

Thank you

John

[Alfred.NESWADBA]

Hi,

what version of AutoCAD do you have and is your operating system 32bit or 64bit.

This is important as older AutoCAD releases use VBA based on 32bit even if AutoCAD is running as 64bit application, newer AutoCAD use VBA7 and that runs with the same bit-options as the AutoCAD itself (and so as the operating system offers).

- alfred -

[OceanaPolynom]

I am using AutoCad 2007 on a machine with 64 bit win 7 pro

John

[Alfred.NESWADBA · Accepted solution]

Hi,

First guess: the filename of your DBF has more than 8 char (+ 3 extension)?

Reduce the name to <= 8 characters length and it will work (8.3 ... old DOS restrictions)

- alfred -

[OceanaPolynom]

Thank you very much once again.  Now everything works perfectly.

John

[OceanaPolynom]

Here is all the relevant code for posthumous reference

'http://www.myengineeringworld.net/2013/09/read-dbf-files-using-vba.html
    Dim con         As Object
    Dim rs          As Object
    Dim sql         As String
    Dim i           As Integer
    Dim j           As Integer
            
  CommonDialog1.DialogTitle = "Open .dbf data base file."
  CommonDialog1.ShowOpen
  ft$ = CommonDialog1.FileTitle
  fn$ = CommonDialog1.FileName
  fnn$ = fn$
  c% = InStr(1, fn$, ft$)
  fn$ = Left(fn$, c% - 1)
  
  On Error Resume Next
    'Create the ADODB connection object.
    Set con = CreateObject("ADODB.connection")
    'Check if the object was created.
    If Err.Number <> 0 Then
        MsgBox "Connection was not created!", vbCritical, "Connection error"
        Exit Sub
    End If
    On Error GoTo 0

    'Open the connection.
    con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & fn$ & ";Extended Properties=dBASE IV;"
    
   'Create the SQL statement to read the file. Filter all the data from Canada.
    'Note that the filename is used instead of the table name.
    sql = "SELECT * FROM " & ft$ & ";"

    On Error Resume Next
    'Create the ADODB recordset object.
    Set rs = CreateObject("ADODB.recordset")
    'Check if the object was created.
    If Err.Number <> 0 Then
        MsgBox "Connection was not created!", vbCritical, "Connection error"
        Exit Sub
    End If
    On Error GoTo 0
    
    'Open the recordset.
    rs.Open sql, con
    
    
'Loop through the recordset and proccess records
    i = 1
    If Not (rs.EOF And rs.BOF) Then
        'Go to the first record.
        rs.MoveFirst
        Do Until rs.EOF = True
            ptnm$ = Trim(rs!Name)
            n$ = Trim(rs!north)
            e$ = Trim(rs!east)
            z$ = Trim(rs!Elevation)
            cd$ = Trim(rs!code)
            note$ = ""
            If Not IsNull(rs!note) Then
                note$ = Trim(rs!note)
            End If
            'proccess this point here
            
            
            'Move to the next record.
            rs.MoveNext
            i = i + 1
        Loop
    Else
        'Close the recordet and the connection.
        rs.Close
        con.Close
        'Release the objects.
        Set rs = Nothing
        Set con = Nothing
        'In case of an empty recordset display an error.
        MsgBox "There are no records in the recordset!", vbCritical, "No Records"
        Exit Sub
    End If

John