Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Kent, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ADODB connection (Office 2000)

    So now that I've done my intro course to VBA, I need to get cracking. I am trying to pull into excel with VBA a table from Access called LOSSEVT

    The compilation error I get is "user-defined type not defined".

    I've copied a lot of the code from a course example. Pointers in the right direction would be much appreciated.

    Code below

    Option Explicit
    Dim strConnect As String, strSQL As String
    Dim cnBRUK As ADODB.Connection
    Dim rsBRUK As ADODB.Recordset


    Sub Import()
    Dim introw As Integer

    'Define the connection string
    strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=c:infotecmar_addmarine accrual.mdb;"

    introw = 1

    'Define the data we want
    strSQL = "select distinct LOSS_ID, LOSSNAME, CAT_CODE FROM LOSSEVT"

    'Open the connection
    Set cnBRUK = New ADODB.Connection
    cnBRUK.Open strConnect

    'Produce the recordset
    Set rsBRUK = cnBRUK.Execute(strSQL)

    'Navigate through the recordset
    With rsBRUK
    Do Until .EOF

    'Read LOSS_ID into A, LOSSNAME into B & CAT_CODE into C
    ActiveSheet.Cells(introw, 1).Value = !LOSS_ID
    ActiveSheet.Cells(introw, 2).Value = !LOSSNAME
    ActiveSheet.Cells(introw, 3).Value = !CAT_CODE

    'Go to next row
    introw = introw + 1

    'Go to next record
    .MoveNext

    Loop

    'Close everything
    .Close
    End With

    'Close connection
    cnBRUK.Close
    End Sub

    Thanks
    John

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: ADODB connection (Office 2000)

    Hi John,
    Do you have a reference set to the ADO object library?
    I'd also suggest using something like:
    <pre>Option Explicit
    Dim strConnect As String, strSQL As String
    Dim cnBRUK As ADODB.Connection
    Dim rsBRUK As ADODB.Recordset


    Sub Import()

    'Define the connection string
    strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=c:infotecmar_addmarine accrual.mdb;"


    'Define the data we want
    strSQL = "select distinct LOSS_ID, LOSSNAME, CAT_CODE FROM LOSSEVT"

    'Open the connection
    Set cnBRUK = New ADODB.Connection
    cnBRUK.Open strConnect

    'Produce the recordset
    Set rsBRUK = cnBRUK.openrecordset(strSQL)

    activesheet.Range("A1").copyfromrecordset rsBRUK
    rsBRUK.Close
    set rsBRUK = Nothing
    'Close connection
    cnBRUK.Close
    set cnBRUK = Nothing
    End Sub
    </pre>

    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Kent, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADODB connection (Office 2000)

    I didn't have a lot of libraries referenced, so I checked "Microsoft Access 9.0 object library" and it compiled OK, BUT...when I ran it, I received the error message "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another"

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: ADODB connection (Office 2000)

    John,
    Sorry I've just looked at the code I posted and I'm really not sure what I was thinking about! <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
    You need a reference to the Microsoft ActiveX Data Objects library not the Access library, then try this:
    <pre>Option Explicit
    Dim strConnect As String, strSQL As String
    Dim cnBRUK As ADODB.Connection
    Dim rsBRUK As ADODB.Recordset
    Sub Import()
    'Define the connection string
    strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" _
    & "Data source=c:infotecmar_addmarine accrual.mdb;"
    'Define the data we want
    strSQL = "select distinct LOSS_ID, LOSSNAME, CAT_CODE FROM LOSSEVT"
    'Open the connection
    Set cnBRUK = New ADODB.Connection
    cnBRUK.Open strConnect
    'Produce the recordset
    Set rsBRUK = new adodb.connection
    with rsBRUK
    .open strSQL, cnBRUK
    activesheet.Range("A1").copyfromrecordset rsBRUK
    .Close
    end with
    set rsBRUK = Nothing
    'Close connection
    cnBRUK.Close
    set cnBRUK = Nothing
    End Sub
    </pre>

    Hope that helps and sorry for the confusion!
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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