On Sep 15, 10:33*am, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote:
Depends houw you are trying to connect... ADO, DAO, ???. For ADO you need..

Microsoft ActiveX Data Objects 2.? Library.

One thing to note is that you may want to select a library somewhere around
2.5 in order to ensure backwards compatability with older version of XL.
--
HTH...

Jim Thomlinson



"Damil4real" wrote:
What other options are needed to be checked in the Microsoft Access
Object Library (Reference - VBA Project) in order to successfully
connect from Excel to Access using a Macro?

I currently have the following options checked:

Visual Basics for Applications
Microsoft Excel 11.0 Object Library
OLE Automation
Microsoft Office 11.0 Oject Library
Microsoft *ADO Ext. 2.8 for DDL and Security
MIcrosoft Access 11.0 Object Library

I appreciate your assistance.

Thanks!- Hide quoted text -

- Show quoted text -

Thanks for the response, Jim

I'm still kinda new to all of these so I don't really know the
difference btw ADO or DAO.

The code is below. What kind of connection is it? Thanks a bunch!

Public Sub GetUniqueDataFromAccessFields(MyDatabaseFilePathAn dName As
String, MyTable As String, _
MyTableField1 As String, _
MyTableField2 As String, _
MyTableField3 As String, _
MyTableField4 As String, _
MyTableField5 As String, _
MyTableField6 As String, _
MyTableField7 As String, _
DestSheetRange As Range,
ClearRange As Boolean)

Dim MyConnection As String
Dim MySQL As String
Dim MyDatabase As Object
Dim I As Integer
Dim str1 As Variant


'If ClearRange = True clear all cells in column K:O
If ClearRange Then
Sheets(DestSheetRange.Parent.Name).Range(DestSheet Range.Address,
DestSheetRange.Offset(0, 4)).EntireColumn.ClearContents

'Create connection string
MyConnection = "Provider=Microsoft.Jet.OLEDB.4.0;"
MyConnection = MyConnection & "Data Source=" &
MyDatabaseFilePathAndName & ";"

' Create MySQL string
str1 = Array(MyTableField1, MyTableField2, MyTableField3,
MyTableField4, MyTableField5, MyTableField6, MyTableField7)

MySQL = ""
For I = LBound(str1) To UBound(str1)
If str1(I) < "" Then
MySQL = "Select Distinct [" & str1(I) & "] From " &
MyTable

' Open the database and copy the data
On Error GoTo SomethingWrong
Set MyDatabase = CreateObject("adodb.recordset")
MyDatabase.Open MySQL, MyConnection, 0, 1, 1

' Check to make sure we received data and copy the data
If Not MyDatabase.EOF Then
'Copy to K:M in the Criteria sheet (Columns are
hidden)
DestSheetRange.Offset(0, I).CopyFromRecordset
MyDatabase
Else
MsgBox "No records returned from : " & str1(I),
vbCritical
End If

MyDatabase.Close
Set MyDatabase = Nothing
End If
Next I

Exit Sub

SomethingWrong:
On Error GoTo 0
Set MyDatabase = Nothing
MsgBox "Error copying unique data", vbCritical, "Test Access data
to Excel"

End Sub