Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL not importing records from ACCDB
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--- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL not importing records from ACCDB
Steven North wrote:
"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 & """) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL not importing records from ACCDB
On Friday, September 13, 2013 10:06:01 AM UTC+8, witek wrote:
Steven North wrote: "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 & """) 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--- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing a text file of individual records | Excel Discussion (Misc queries) | |||
Importing new records | Excel Programming | |||
Connecting to accdb with Excel 2003 | Excel Programming | |||
Importing XML records individually | Excel Programming | |||
Importing multiline records files into Excel cells | Excel Discussion (Misc queries) |