Consulting

Results 1 to 4 of 4

Thread: MISSING DATA IN EXPORTING CALCULATED QUERYTable FROM ACCESS TO EXCEL USING EXCEL ADO

  1. #1
    VBAX Newbie
    Joined
    Oct 2019
    Posts
    4
    Location

    MISSING DATA IN EXPORTING CALCULATED QUERYTable FROM ACCESS TO EXCEL USING EXCEL ADO

    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

  2. #2
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,857
    How to use the ADODB recordset simply in Excel see: http://www.snb-vba.eu/VBA_ADODB_recordset_en.html

  3. #3
    VBAX Newbie
    Joined
    Oct 2019
    Posts
    4
    Location
    [QUOTE=snb;396155]How to use the ADODB recordset simply in Excel see:

    Thank you for the link. In which I believe it is one of my sources too. But I could not find the answer why Exporting data to excel using VBA Excel ADO wont capture a result from A Calculated Field

  4. #4
    VBAX Newbie
    Joined
    Oct 2019
    Posts
    4
    Location
    Using Inner Select Query In MSAccess Queries Will do the work.

    I made 3 table Queries

    Table 1 Compose of
    Date
    DVnumber
    Payee
    Amount

    Table2 Compose of
    RefJournal
    DVnumber
    Payee

    So in Table 3
    Date
    RJournal: (Select REFjournal From Table2 where Table1.DvNumber = Table2.DVnumber and Table1.Payee=Table2.Payee)
    Amount

    Or Under SQL

    SELECT Table1.Date, (Select REFjournal From Table2 where Table1.DvNumber = Table2.DVnumber and Table1.Payee=Table2.Payee) as Rjournal, Table1.AMOUNT
    FROM Table1;

    I Think THis Will Solve It


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •