SQL not importing records from ACCDB

[Steven North]

I'm struggling trying to figure out why the SQL is constantly returning 'no records'. It's essentially the same as MS Query. Except in the MS Query it's returning results.

I am trying to set it up so that I can return various groups of data to different financial headings but the data is just not returning. Can someone please 'debug' this code and advise where it's going wrong.

---< BEGIN CODE>---

Public Sub ImportTransactions()
Dim rsDATA As ADODB.Recordset
Dim szCONNECT As String
Dim szSQL As String
Dim mqryCCA, mqryPERIOD As String


' Dim conn As New Connection, rec As New Recordset
Dim ws As Worksheet
Dim sql$, i&

Set ws = ThisWorkbook.Worksheets("Sheet1")

' Create the connection String

' Obtain the values from Excel
mqryCCA = ws.Range("qryCCA").Value
mqryPERIOD = ws.Range("qryPERIOD").Value

szCONNECT = "Provider=microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" + ThisWorkbook.Path + "\OEPA Fiancial Database.accdb"

' Create the SQL statement

szSQL = "SELECT * " & _
"FROM GL_DATABASE " & _
"WHERE (GL_DATABASE.CC LIKE " & mqryCCA & ") AND (GL_DATABASE.PERIOD_NAME LIKE " & mqryPERIOD & ")" & _
"ORDER BY GL_DATABASE.GL_CODE, GL_DATABASE.POSTED_DATE "



' Create the Recordset object and run the query
Set rsDATA = New ADODB.Recordset
rsDATA.Open szSQL, szCONNECT, adOpenForwardOnly, _
adLockReadOnly, adCmdText


' Make sure we got records back
If Not rsDATA.EOF Then
' Dump the contents of the record set onto the worksheet
ws.Range("A10").CopyFromRecordset rsDATA
' Close the recordset
rsDATA.Close
' Fit the columd widths to the data
ws.UsedRange.EntireColumn.AutoFit
Else
MsgBox "Error: No records returned.", vbCritical
End If

' Close the recordset if it is still open
If CBool(rsDATA.State And adStateOpen) Then rsDATA.Close
Set rsDATA = Nothing

End Sub

---<END CODE>---

 

[witek]

"WHERE (GL_DATABASE.CC LIKE " & mqryCCA & ") AND (GL_DATABASE.PERIOD_NAME LIKE " & mqryPERIOD & ")"

print it in debug window
do not you miss "" around mqryCCA and mqryPERIOD values?


LIKE '" & mqryCCA & "')

or

LIKE """ & mqryCCA & """)

 

[Steven North]

print it in debug window

do not you miss "" around mqryCCA and mqryPERIOD values?





LIKE '" & mqryCCA & "')



or



LIKE """ & mqryCCA & """)

Thanks witek... this is perfect!

LIKE '" & mqryCCA & "')

Revised code...

---<BEGIN CODE>---

Public Sub ImportTransactions()
Dim rsDATA As ADODB.Recordset
Dim szCONNECT As String
Dim szSQL As String
Dim mqryCCA As String
Dim mqryPERIOD As Date


' Dim conn As New Connection, rec As New Recordset
Dim ws As Worksheet
Dim sql$, i&

Set ws = ThisWorkbook.Worksheets("Sheet1")

' Create the connection String

' Obtain the values from Excel
mqryCCA = ws.Range("qryCCA").Value
mqryPERIOD = ws.Range("qryPERIOD").Value

szCONNECT = "Provider=microsoft.ACE.OLEDB.12.0;" + _
"Data Source=" + ThisWorkbook.Path + "\OEPA Fiancial Database.accdb"

' szCONNECT = "Provider=microsoft.JET.OLEDB.4.0;" & _
"Data Source=" + ThisWorkbook.Path + "\OEPA Fiancial Database.accdb"

' Create the SQL statement

szSQL = "SELECT * " & _
"FROM GL_DATABASE " & _
"WHERE (GL_DATABASE.CC LIKE '" & mqryCCA & "') AND (GL_DATABASE.PERIOD_NAME LIKE '" & mqryPERIOD & "')" & _
"ORDER BY GL_DATABASE.GL_CODE, GL_DATABASE.POSTED_DATE "



' Create the Recordset object and run the query
Set rsDATA = New ADODB.Recordset
rsDATA.Open szSQL, szCONNECT, adOpenForwardOnly, _
adLockReadOnly, adCmdText


' Make sure we got records back
If Not rsDATA.EOF Then
' Dump the contents of the record set onto the worksheet
ws.Range("A10").CopyFromRecordset rsDATA
' Close the recordset
rsDATA.Close
' Fit the columd widths to the data
ws.UsedRange.EntireColumn.AutoFit
Else
MsgBox "Error: No records returned.", vbCritical
End If

' Close the recordset if it is still open
If CBool(rsDATA.State And adStateOpen) Then rsDATA.Close
Set rsDATA = Nothing





End Sub

---<END CODE>---