No announcement yet.

ADODB Connection Objects Unrecognized In VBA

  • Filter
  • Time
  • Show
Clear All
new posts

  • ADODB Connection Objects Unrecognized In VBA

    I've searched the forum a number of times but can't seem to find the answer to my question. I'm trying to open a connection to an Access 2007 database using an ADODB connection. I made sure that I have an open reference to the Microsoft Access 12.0 Object Library, which is supposedly the key to making a connection.

    Dim myconn As New ADODB.Connection
    'I get a compile error with this line of code saying "User-defined type not defined"'
    I'm wanting to run multiple select queries from Excel 2007 VBA on a database I created in Access 2007 called "Annual Performance Review" and paste the results in an Excel spreadsheet. This may involve joining of multiple tables eventually, but the ADODB connection is really a hinderance. I tried recording some macros and ended up opening an OLEDB connection to my Access database and changed the connection properties so it would let me run a query. This appears to only let you run one query at a time, so I'd have to open a new connection each time I wanted to run a query. Here's the macro code if you need it:

     With ActiveWorkbook.Connections("Annual Performance Review1").OLEDBConnection
            .BackgroundQuery = True
            .CommandText = Array( _
            "SELECT Salaries.[total comp], [Employee Comp].Ssn, Salaries.[employee name], Salaries.[yr beg date]" & Chr(13) & "" & Chr(10) & "FROM Salaries " _
            , _
            "INNER JOIN [Employee Comp] ON (Salaries.[employee name] = [Employee Comp].[Employee Name]) AND (Salaries.ssn = [Emp" _
            , "loyee Comp].Ssn)" & Chr(13) & "" & Chr(10) & "WHERE ((([Employee Comp].Ssn)=[Employee Comp].[ssn]));")
            .CommandType = xlCmdSql
            .Connection = Array( _
            "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=M:\users\Nick\Projects\Ann Perf Review\Annual Performance Review.acc" _
            , _
            "db;Mode=ReadWrite;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=6;Jet OLE" _
            , _
            "DB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Passw" _
            , _
            "ord="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet " _
            , _
            "OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False" _
            .RefreshOnFileOpen = False
            .SavePassword = False
            .SourceConnectionFile = ""
            .SourceDataFile = _
            "M:\users\Nick\Projects\Ann Perf Review\Annual Performance Review.accdb"
            .ServerCredentialsMethod = xlCredentialsMethodIntegrated
            .AlwaysUseConnectionFile = False
            .ServerFillColor = False
            .ServerFontStyle = False
            .ServerNumberFormat = False
            .ServerTextColor = False
        End With

    I need to know from an expert - what are the advantages / disadvantages of each connection and why can't I get the ADODB connection to work?

    Thanks for any help you can offer!

  • #2
    Re: Adodb Connection Objects Unrecognized In Vba

    Wrong reference library... try Microsoft ActiveX Data Objects x.x Library...


    • #3
      Re: ADODB Connection Objects Unrecognized In VBA

      Check this thread out:

      It explains the differences, advantages and disadvantages of all mentioned protocols(?).


      • #4

        Re: ADODB Connection Objects Unrecognized In VBA

        Thanks to all who replied - I got it to work no problem and now know more about the types of connections you can create between Excel and other applications.