Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

Results 1 to 4 of 4

Thread: ADODB Connection Objects Unrecognized In VBA

  1. #1
    Join Date
    27th July 2006

    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!

    Excel Video Tutorials / Excel Dashboards Reports

  2. #2
    Join Date
    15th March 2007

    Re: Adodb Connection Objects Unrecognized In Vba

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

    Excel Video Tutorials / Excel Dashboards Reports

  3. #3
    Join Date
    11th January 2008

    Re: ADODB Connection Objects Unrecognized In VBA

    Check this thread out:

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

    Excel Video Tutorials / Excel Dashboards Reports

  4. #4
    Join Date
    27th July 2006

    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.

    Excel Video Tutorials / Excel Dashboards Reports

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Slow Execution With Adodb Connection
    By kim11vagabond in forum Excel and/or SQL Help
    Replies: 6
    Last Post: June 1st, 2007, 21:57
  2. ADO ADODB.Connection: Preserve Cell Formatting
    By postman2000 in forum Excel General
    Replies: 2
    Last Post: October 4th, 2006, 02:54
  3. Use ADODB connection + SQL CREATE TABLE statement
    By eljays in forum Excel and/or Access Help
    Replies: 1
    Last Post: August 3rd, 2005, 16:58
  4. Replies: 1
    Last Post: December 2nd, 2004, 08:09
  5. ADO ADODB.Connection
    By sal21 in forum Excel General
    Replies: 1
    Last Post: April 23rd, 2004, 21:16


Posting Permissions

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