VB Forum / Database Access / May 2007

I'm trying to import data into an Excel sheet with ADO.
I don't understand why the same query that in Access gets the correct
output when is called by Excel+ADO return no records!!

Everyone can tell me why and help me to solve the problem?

Thanks.

This is the code:

Public Sub GetRecords()
   Dim objField As Object ' ADODB.Field
   Dim rsData As Object ' ADODB.Recordset
   Dim objConnection As Object
   
   Dim lOffset As Long
   Dim szConnect As String
   Dim szQueryName As String
   
   Dim ws As Worksheet
   
   Set ws = ThisWorkbook.Worksheets("TestADO")
   
   ws.Range(Cells(10, 1), Cells(65535, 1)).EntireRow.Clear
   
   ' Create the connection string.
   szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source= " & ws.Range("DB_Path").Value & "\" & _
                                 ws.Range("DB_FileName").Value & ";"
   
   szQueryName = "[" & ws.Range("DB_QueryName").Value & "]"
   
   On Error GoTo ERR_EstraiEM
   
   Set objConnection = CreateObject("ADODB.Connection")
   objConnection.Open szConnect
   
   
   ' Create the Recorset object and run the query.
   Set rsData = CreateObject("ADODB.Recordset")
   With rsData
          .CursorLocation = adUseClient
       .CursorType = adOpenStatic
       '.locktype = adLockBatchOptimistic
       .LockType = adLockReadOnly
       .Open szQueryName, , , , adCmdTable
   End With
       
   ' Make sure we got records back
   If Not rsData.EOF Then
       ' Add headers to the worksheet.
       With ws.Range("A10")
           For Each objField In rsData.Fields
               .Offset(0, lOffset).Value = objField.Name
               lOffset = lOffset + 1
           Next objField
           .Resize(1, rsData.Fields.Count).Font.Bold = True
       End With
       ' Dump the contents of the recordset onto the worksheet.
       ws.Range("A11").CopyFromRecordset rsData
       ' Close the recordset
       ' Fit the column widths to the data.
       ws.UsedRange.EntireColumn.AutoFit
       rsData.MoveLast
       rsData.MoveFirst
       
       ws.Range(Cells(10, 1), Cells(10 + rsData.RecordCount,
lOffset)).Interior.Color = vbGreen
       rsData.Close
   Else
       MsgBox "Error: No records returned.", vbCritical
   End If
   
   Set rsData = Nothing
   Exit Sub
   
ERR_EstraiEM:
   MsgBox "Error: " & Err & " - " & Error$, vbCritical
   On Error GoTo 0
   Exit Sub
   

End Sub