ADODB Connection Objects Unrecognized In VBA

[FWC_Tressler]

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!

[Craig Ottley]

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

[ryan_au]

Check this thread out:
http://www.issociate.de/board/post/2...vs_OLE_DB.html

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

[FWC_Tressler]

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.