Wilfem
11-05-2019, 02:29 AM
Hi I have a Calculated Query Table name DataQuery like
Date / RJournal / AMount
where Rjournal is a calculated field
Rjournal : DLookUp("REFjournal","DV","ChckID > 0 and Payee = '" & [Payee] & "' and Dvnumber = " & [Dvnumber] & "")
And it works fine.
But since MS Access is my Database and Excel is my front end and most of my users are excel users. I created an Export Button to export this Query to excel using ADO in excel. For some reason the Field RJournal wont capture its data it just leave blank. But if I use access Menu External Data then Export to Excel all data will be present.
I wonder if ADO supports Exporting Calculated Table query.
Below is my code in Excel Active Button
Private Sub Export_Click()
Dim cnn As ADODB.Connection 'dim the ADO collection class
Dim rs As ADODB.Recordset 'dim the ADO recordset class
Dim dbPath As String
Dim SQL As String
'add error handling
On Error GoTo errHandler:
'Disable screen flickering.
Application.ScreenUpdating = False
'clear the values from the worksheet
Sheets("Data").Range("A2:C500000").ClearContents
'get the path to the database
dbPath = Sheets("Update Version").Range("b1").Value
Set cnn = New ADODB.Connection ' Initialise the collection class variable
'Connection class is equipped with a —method— named Open
'—-4 aguments—- ConnectionString, UserID, Password, Options
'ConnectionString formula—-Key1=Value1;Key2=Value2;Key_n=Value_n;
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath
SQL = "SELECT * FROM DATAQUERY"
'Create the ADODB recordset object.
Set rs = New ADODB.Recordset 'assign memory to the recordset
'ConnectionString Open '—-5 aguments—-
'Source, ActiveConnection, CursorType, LockType, Options
rs.Open SQL, cnn
'Check if the recordset is empty.
If rs.EOF And rs.BOF Then
'Close the recordet and the connection.
rs.Close
cnn.Close
'clear memory
Set rs = Nothing
Set cnn = Nothing
'Enable the screen.
Application.ScreenUpdating = True
'In case of an empty recordset display an error.
MsgBox "There are no records in the recordset!", vbCritical, "No Records"
Exit Sub
End If
'Write the reocrdset values in the sheet.
Sheets("DATA").Range("A2").CopyFromRecordset rs
'Close the recordset and the connection.
rs.Close
cnn.Close
'clear memory
Set rs = Nothing
Set cnn = Nothing
'Enable the screen.
Application.ScreenUpdating = True
'Inform the user that the macro was executed successfully.
MsgBox "Congratulation the data has been successfully Imported", vbInformation, "Import successful"
'error handler
On Error GoTo 0
Exit Sub
errHandler:
'clear memory
Set rs = Nothing
Set cnn = Nothing
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Import_Data"
END SUB
Date / RJournal / AMount
where Rjournal is a calculated field
Rjournal : DLookUp("REFjournal","DV","ChckID > 0 and Payee = '" & [Payee] & "' and Dvnumber = " & [Dvnumber] & "")
And it works fine.
But since MS Access is my Database and Excel is my front end and most of my users are excel users. I created an Export Button to export this Query to excel using ADO in excel. For some reason the Field RJournal wont capture its data it just leave blank. But if I use access Menu External Data then Export to Excel all data will be present.
I wonder if ADO supports Exporting Calculated Table query.
Below is my code in Excel Active Button
Private Sub Export_Click()
Dim cnn As ADODB.Connection 'dim the ADO collection class
Dim rs As ADODB.Recordset 'dim the ADO recordset class
Dim dbPath As String
Dim SQL As String
'add error handling
On Error GoTo errHandler:
'Disable screen flickering.
Application.ScreenUpdating = False
'clear the values from the worksheet
Sheets("Data").Range("A2:C500000").ClearContents
'get the path to the database
dbPath = Sheets("Update Version").Range("b1").Value
Set cnn = New ADODB.Connection ' Initialise the collection class variable
'Connection class is equipped with a —method— named Open
'—-4 aguments—- ConnectionString, UserID, Password, Options
'ConnectionString formula—-Key1=Value1;Key2=Value2;Key_n=Value_n;
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath
SQL = "SELECT * FROM DATAQUERY"
'Create the ADODB recordset object.
Set rs = New ADODB.Recordset 'assign memory to the recordset
'ConnectionString Open '—-5 aguments—-
'Source, ActiveConnection, CursorType, LockType, Options
rs.Open SQL, cnn
'Check if the recordset is empty.
If rs.EOF And rs.BOF Then
'Close the recordet and the connection.
rs.Close
cnn.Close
'clear memory
Set rs = Nothing
Set cnn = Nothing
'Enable the screen.
Application.ScreenUpdating = True
'In case of an empty recordset display an error.
MsgBox "There are no records in the recordset!", vbCritical, "No Records"
Exit Sub
End If
'Write the reocrdset values in the sheet.
Sheets("DATA").Range("A2").CopyFromRecordset rs
'Close the recordset and the connection.
rs.Close
cnn.Close
'clear memory
Set rs = Nothing
Set cnn = Nothing
'Enable the screen.
Application.ScreenUpdating = True
'Inform the user that the macro was executed successfully.
MsgBox "Congratulation the data has been successfully Imported", vbInformation, "Import successful"
'error handler
On Error GoTo 0
Exit Sub
errHandler:
'clear memory
Set rs = Nothing
Set cnn = Nothing
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Import_Data"
END SUB