How do I show data from an ADO query?

[psehudson]

I'm new to ADO, and I'm presently trying to understand how to create queries using ADO on Access 2000. I've got the following code written:

    Dim strSQL As String   
    Dim cmd As ADODB.Command
    Dim cat As ADOX.Catalog
              
    strSQL = "SELECT ID FROM tbl_WorkOrder_Entry WHERE [Model #] = ""Widget1"""
    
    Set cat = New ADOX.Catalog
    cat.ActiveConnection = CurrentProject.Connection
    
    Set cmd = New ADODB.Command
    cmd.CommandText = strSQL
    cat.Views.Append "test123", cmd
    cat.Views.Refresh
        
    Set cat = Nothing
    Set cmd = Nothing

This creates a query (or is it called a View?) called "test123". Now that I have this, I would like to display the data in a table form, just like when I execute a query I make with the query wizard. How do I do this?

I've tried

doCmd.OpenQuery "test123"

but Access responds with the error message
"database can't find the object 'test123'"

After I display the table, my next step will be to export the data into a format that I can read with MS Excel. An example of how to display the query and output the data to a MS Excel file would be greatly appreciated!

 

[jrbarnett]

Why not just run the VBA from Excel to pull the data straight in there? Add a reference to ADO then a commandbutton on screen to run the code.

All you need to do is change the connection string to point at the original data source (whether Access, SQL Server or whatever).

Connection strings:

http://www.connectionstrings.com/

John

 

[SkipVought]

Here's a vba example in Excel of a query as a function. To insert the entire resultset, check out the CopyFromRecordset Method.

You must ALSO have a reference set in vba Tools > References to a Microsoft ActiveX Data Objects Library.

Function GetIMT(sPN As String) As String
'--------------------------------------------------
' Access: A010PROD.FPRPTSAR.READ
'--------------------------------------------------
'this function returns IMP Owner for a given Part Number
'--------------------------------------------------
    Dim sConn As String, sSQL As String, sServer As String
    Dim rst As ADODB.Recordset, cnn As ADODB.Connection
    
    Set cnn = New ADODB.Connection
    
    sServer = "A010prod"
    cnn.Open "Driver={Microsoft ODBC for Oracle};" & _
               "Server=" & sServer & ";" & _
               "Uid=/;" & _
               "Pwd="
    
    Set rst = New ADODB.Recordset
    
    sSQL = "SELECT RC.OWNER "
    sSQL = sSQL & "FROM FPRPTSAR.PART_MASTER PM "
    sSQL = sSQL & "  ,  FPRPTSAR.RESPONSIBILITY_CODES RC "
    sSQL = sSQL & "WHERE PM.PART_id like '" & Trim(sPN) & "%' "
    sSQL = sSQL & "  AND PM.RESP_CODE=RC.RESP_CODE"
        
    rst.Open sSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText
               
    On Error Resume Next
               
    rst.MoveFirst
    
    If Err.Number = 0 Then
        GetIMT = rst(0)
    Else
        GetIMT = "NONE"
    End If

    rst.Close
    cnn.Close
    
    Set rst = Nothing
    Set cnn = Nothing
End Function

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}