Wrong reference library... try Microsoft ActiveX Data Objects x.x Library...
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.
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:VB:Dim myconn As New ADODB.Connection 'I get a compile error with this line of code saying "User-defined type not defined"'
VB: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!
Check this thread out:
It explains the differences, advantages and disadvantages of all mentioned protocols(?).
There are currently 1 users browsing this thread. (0 members and 1 guests)