Results 1 to 5 of 5
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ADO ADODB.Connection (2000)

    I tested this macro but i recieve on this line (Dim cnt As ADODB.Connection) : "error compilation Type not defined fron user"

    code:

    Sub Import_AccessData()

    Dim cnt As ADODB.Connection

    Dim rst1 As ADODB.Recordset, rst2 As ADODB.Recordset
    Dim stDB As String, stSQL1 As String, stSQL2 As String
    Dim stConn As String
    Dim wbBook As Workbook
    Dim wsSheet1 As Worksheet
    Dim lnField As Long, lnCount As Long

    'Instantiate the ADO-objects.
    Set cnt = New ADODB.Connection
    Set rst1 = New ADODB.Recordset
    Set rst2 = New ADODB.Recordset

    Set wbBook = ThisWorkbook
    Set wsSheet1 = wbBook.Worksheets(1)

    'Path to the database.
    stDB = "c:dbDELIVERY.mdb"

    'Create the connectionstring.
    stConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & stDB & ";"

    'The 1st raw SQL-statement to be executed.
    stSQL1 = "SELECT * FROM Production_E1"

    'The 2nd raw SQL-statement to be executed.
    stSQL2 = "SELECT * FROM Production_E2"

    'Clear the worksheet.
    wsSheet1.Range("A1").CurrentRegion.Clear

    With cnt
    .Open (stConn) 'Open the connection.
    .CursorLocation = adUseClient 'Necessary to disconnect the recordset.
    End With

    With rst1
    .Open stSQL1, cnt 'Create the recordset.
    Set .ActiveConnection = Nothing 'Disconnect the recordset.
    End With

    With rst2
    .Open stSQL2, cnt 'Create the recordset.
    Set .ActiveConnection = Nothing 'Disconnect the recordset.
    End With

    With wsSheet1
    .Cells(2, 1).CopyFromRecordset rst1 'Copy the 1st recordset.
    .Cells(2, 2).CopyFromRecordset rst2 'Copy the 2nd recordset.
    End With

    'Release objects from the memory.
    rst1.Close
    Set rst1 = Nothing
    rst2.Close
    Set rst2 = Nothing
    cnt.Close
    Set cnt = Nothing
    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ADO ADODB.Connection (2000)

    Do you have a reference to the Microsoft ActiveX Data Objects 2.n Library? You can check this in Tools | References... in the Visual Basic Editor.

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO ADODB.Connection (2000)

    ok, ok! i have solved this prob.
    But this library appear only if i open the wbook that is required? If i could want to set this library for ever? is possible?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ADO ADODB.Connection (2000)

    References to object libraries are stored in the workbook, so it must be set in each workbook separately. You could make a default workbook with this reference, but that would mean that ALL new workbooks have a reference to the ADO library. That may not be desirable, and could cause problems if you want to distribute a workbook to another user.

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO ADODB.Connection (2000)

    infact i have thing this problem "...may not be desirable, and could cause problems if you want to distribute a workbook to another user...."
    You are very, very , professional and tks for the spdeey to help me. In this time all is clear form me.
    Tks

Posting Permissions

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