Connected from Excel to Access

[Damil4real]

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!

 

[Jim Thomlinson]

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.

 

[Damil4real]

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









- 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(MyDatabaseFilePathAndName 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(DestSheetRange.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

 

[Jim Thomlinson]

You are using ADO. Note the line
Set MyDatabase = CreateObject("adodb.recordset")

That being said you are using something called late binding where your
references are created at run time and not at design time. To that end you do
not need to add any references. I copied your code into a blank workbook and
it compiled just fine. What makes you think you need to add a reference?
--
HTH...

Jim Thomlinson

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









- 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(MyDatabaseFilePathAndName 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(DestSheetRange.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