CODEWITHSUNDEEP
excelvbaoledbjetdbase
Bernhard Moser
Bernhard Moser

Reputation: 11

OLEDB JET not connecting to .dbf

I need to pull some data from a .dbf file which has 2 many rows to open the full file in Excel. I have VBA code which runs but the SQL query is not populating. The Recordcount output comes back as -1.

Sub ImportDBFWithSQL()

  Dim cn As Object, rs As Object
  Dim strFile As String, strSQL As String, strConn As String

  strSQL = "SELECT TOP 10 * FROM reptrack"

  ' Set up the connection string for dBASE files
  strConn = "Provider=Microsoft.JET.OLEDB.4.0; Data Source = C:\; Extended Properties=""dBase IV;"";"

  ' Create connection and recordset objects
  Set cn = New ADODB.Connection
  Set rs = New ADODB.Recordset

  ' Open the connection
  cn.Open strConn

  ' Execute the SQL query and populate the recordset
  rs.Open strSQL, cn
MsgBox rs.RecordCount
  ' Check if any records were returned
  If rs.RecordCount > 0 Then
    ' Get the used range of the active sheet (adjust if needed)
    Dim dataRange As Range
    Set dataRange = ActiveSheet.UsedRange

    ' Copy the recordset data to the sheet starting from A1
    dataRange.Resize(rs.RecordCount, rs.Fields.Count).CopyFromRecordset rs

    'Clear formatting from pasted data
    'dataRange.Resize(rs.RecordCount, rs.Fields.Count).NumberFormat = ""
  Else
  '  MsgBox "No records found!", vbInformation
  End If

  ' Clean up
  rs.Close
  cn.Close
  Set rs = Nothing
  Set cn = Nothing

End Sub

Upvotes: 1

Views: 30

Answers (0)

Related Questions