How do you define a user defined type? (ODBC Connection Sub)

[qjd2004]

Hiya,

got some code to get recordset from access and export to excel.

The VBA editor complains about either "User defined type not defined" about the

Set Conn = New ADODB.Connection

or if I move that and try to delclare it in a type block, it says "INVALID INSIDE TYPE BLOCK"

Can anyone help me out here?

Sub GetStuff()

Dim objExcelApp As Excel.Application
Dim xlsExcelSheet As Excel.Worksheet
'Dim conn As New ADODB.Command

'Open Access db set ADODB Connection.
    Set conn = New ADODB.Connection
        conn.ConnectionString = _
            "Provider=Microsoft.Jet.OLEBD.4.0;" & _
            "Data Srouce=" & strAccessDB & ";" & _
            "Persist Security Info=False"
'Begin ADODB connection
    conn.Open

'Select data from table/query MAIN_QUERY_LIVE
    Set rs = conn.Execute( _
        "SELECT * FROM MAIN_QUERY_LIVE", , _
        adCmdText)

'''''''''''''SOME MORE CODE HERE TO INSERT DATA INTO EXCEL

'close the recordset & destroy variable
rs.Close
Set rs = Nothing
'close the connection & destroy variable
conn.Close
Set conn = Nothing

ErrHand:
MsgBox "An Error Occured", vbCritical, "Error Handle"
Exit Sub

End Sub

Type newType
Dim conn As New ADODB.Command			'THIS DOESN'T SEEM TO WORK EITHER
End Type

Can someone show me the right way to do it? Do I need to reference a library to do this?

 

[gh61]

In the IDE - Tools, References... scroll down to Microsoft ActiveX Data Objects 2.* Library. AFAIK latest is version 2.7, but earlier ones should work.

 

[qjd2004]

Hiya,

Thanks for your reply. I've got v 2.7 and the Sub routine now runs, but I've noticed that no data gets put into the workbook.

I wondered if it's because the source table in the strSQL SELECT statement is in fact a query and not a table? Other than that, I can't see what I'm doing wrong. Can you help me?

'Open Access db set ADODB Connection
    Set conn = New ADODB.Connection
        conn.ConnectionString = _
            "Provider=Microsoft.Jet.OLEBD.4.0;" & _
            "Data Srouce=" & strAccessDB & ";" & _
            "Persist Security Info=False"
'Begin ADODB connection
    conn.Open

'Select data from table/query MAIN_QUERY_LIVE
    Set rs = conn.Execute( _
        "SELECT * FROM MAIN_QUERY_LIVE", , _
        adCmdText)

'On Error GoTo ErrHand

'Make column headers
    For col = 0 To rs.Fields.Count - 1
        Application.ActiveSheet.Cells(1, col + 1) = rs.Fields(col).Name
    Next col
    
'Get data from recordset and insert into excel sheet
    Row = 2
    Do While Not rs.EOF
        For col = 0 To rs.Fields.Count - 1
            Application.ActiveSheet.Cells(Row, col + 1) = rs.Fields(col).Value
        Next col
        
        Row = Row + 1
        rs.MoveNext
    Loop
    
On Error Resume Next
    
'close the recordset & destroy variable
rs.Close
Set rs = Nothing
'close the connection & destroy variable
conn.Close
Set conn = Nothing

ErrHand:
MsgBox "An Error Occured", vbCritical, "Error Handle"
Exit Sub

End Sub

 

[gh61]

The fact that you're selecting against a query doesn't matter. I take it you don't get any errors? The following code works for me from _inside_ excel:

Sub test()
    Dim con As ADODB.Connection
    Dim sSQL As String
    Dim rs As ADODB.Recordset
    Dim col As Long, rw As Long
    
    Set con = New ADODB.Connection
    sSQL = "SELECT * FROM rework_job_query"
    
    con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\graeme.henderson\My Documents\edfa_rework_test_db.mdb"
    con.Open
    Set rs = con.Execute(sSQL)
    
    
    For col = 0 To rs.Fields.Count - 1
    ActiveSheet.Cells(1, col + 1) = rs.Fields(col).Name
    Next col
    
'Get data from recordset and insert into excel sheet
    rw = 2
    Do While Not rs.EOF
        For col = 0 To rs.Fields.Count - 1
            Application.ActiveSheet.Cells(rw, col + 1) = rs.Fields(col).Value
        Next col
        
        rw = rw + 1
        rs.MoveNext
    Loop

    
    con.Close
    Set con = Nothing
    Set rs = Nothing
    
End Sub

Note that 'Row' is a keyword in excel and shouldn't be used as a variable. Are you able to try running your code from inside excel and post results?

 

[qjd2004]

Hi GH61,

Your code works a treat! In fact, my problem in my original code was that I hadn't defined "strAccessDB" yet (it will eventually get read in from an input box) and so there was no DB to get the values from!

I was also using a Jet SQL function Nz(fieldname, valueifnull) as expr1 and I had to change that to make it work.

Thanks for your help!