ADOImportFromAccessTable

[Subs]

Hi I am trying to use ADO to import data from Access to Excel (Procedure
below) when I run it the Macro Box is activated but is empty i.e. does not
contain the procedureI managed to do it the other way round using ADO to
export data from Excel to Access, that worked fine. Do I have to use a
different Reference ie ActiveX Data Objects x.x Object Library for the
Access to Excel , or do I put the code in the Access Module. Thanks for any
help.Sub ADOImportFromAccessTable(DBFullName As String, _ TableName As
String, TargetRange As Range)' Example: ADOImportFromAccessTable
"C:\FolderName\DataBaseName.mdb", _ "TableName", Range("C1")Dim cn As
ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer Set
TargetRange = TargetRange.Cells(1, 1) ' open the database Set cn = New
ADODB.Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
Source= C:\Documents and Settings\Me\Desktop\db4.mdb" & ";" Set rs = New
ADODB.Recordset With rs ' open the recordset .Open
TableName, cn, adOpenStatic, adLockOptimistic, adCmdTable ' all
records .Open "SELECT * FROM " & TableName & _ " WHERE
[FieldName1] = 'Mailing List ID'", cn, , , adCmdText ' filter records
' RS2WS rs, TargetRange ' write data from the recordset to the worksheet
' optional approach for Excel 2000 or later (RS2WS is not necessary)
For intColIndex = 0 To rs.Fields.Count - 1 ' the field names
TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
Next TargetRange.Offset(1, 0).CopyFromRecordset rs ' the recordset
data End With rs.Close Set rs = Nothing cn.Close Set cn =
NothingEnd Sub

 

[Guest]

The general approach looks OK to me (with a few details in question - see
below). You don't need any different reference: the ADO library is
independent of the calling application. And you should not need to put the
code in Access, it makes more sense to keep it in the spreadsheet.

So the questions:
- Why are you calling rs.Open twice? Normally ADO won't allow this unless
you first close the already open recordset, so I must be missing something.
- You must have left out some code, since it goes directly from your main
procedure to defining the RS2WS procedure, and that part of the code seems to
be pretty critical to understanding you you get your data from the recordset
into your RS2WS routine.
- Are you sure you have your connection string set properly? Have you
tested to see if you are getting the connection? Also, try breaking the code
after the recordset is returned and use the VBA debugger to take a look at
what you got - is it returning the records you expect?

 

[Jamie Collins]

(Procedure
below) when I run it the Macro Box is activated but is empty i.e. does not
contain the procedure

Sub ADOImportFromAccessTable(DBFullName As String, _
TableName As String, TargetRange As Range)

Your Sub does not appear in the Macro dialog because it has arguments.
Jamie.

--