copyFromRecordSet code in excel-vba

O

oscar.c.marin

I need to copy the result of a query from Access to Excel in a particular wks.

I haven't been able to declare "db" as a database and I get a prompt saying
that user type not defined.

I copied the code from a previous thread from 2005 that was like this:

Dim DBFullName As String
Dim TableName As String
Dim FieldName As String
Dim TargetRange As Range
Dim db As dataBase
Dim rs As Recordset
Dim intColIndex As Integer

DBFullName = "C:\Documents and Settings\Junior\My Documents\Consumer "
Loans.mdb " "
TableName = "tbl_CLC"

Set TargetRange = Excel.Application.Worksheets("CLC").Range("A1")
Set db = OpenDatabase(DBFullName)
Set rs = db.OpenRecordset(TableName, dbOpenTable) ' all records
For intColIndex = 0 To rs.Fields.Count - 1
TargetRange.Offset(0, intColIndex).Value = _
rs.Fields(intColIndex).Name
Next
' write recordset
TargetRange.Offset(1, 0).CopyFromRecordset rs
Set rs = Nothing
db.Close
Set db = Nothing

Any suggestions on how to proceed?

Thank you in advance.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top