Importing Access Tables

I am trying to import a table from an access database that is saved on my c
drive and import it into a worksheet.....I keep getting a type mismatch error
when I try to set the recordset.....Got any suggestions?....Here is the code
I'm using.....

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


--
Thanks in advance....

Hi Matthew,
Just check if you have references set to both the ADO as well as the DAO. If
you do and if ADO is ahead of the DAO in the references listed then you could
get this error. You can easily correct it by declaring rs as DAO.Recordset.
Hope this helps.


"Matthew" wrote:

> I am trying to import a table from an access database that is saved on my c
> drive and import it into a worksheet.....I keep getting a type mismatch error
> when I try to set the recordset.....Got any suggestions?....Here is the code
> I'm using.....
>
> 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
>
>
> --
> Thanks in advance....

Hey Thanks it worked.....Thats all that was wrong....I declared it a
DAO.Recordset and it worked....

"Alok" wrote:

> Hi Matthew,
> Just check if you have references set to both the ADO as well as the DAO. If
> you do and if ADO is ahead of the DAO in the references listed then you could
> get this error. You can easily correct it by declaring rs as DAO.Recordset.
> Hope this helps.
>
>
> "Matthew" wrote:
>
> > I am trying to import a table from an access database that is saved on my c
> > drive and import it into a worksheet.....I keep getting a type mismatch error
> > when I try to set the recordset.....Got any suggestions?....Here is the code
> > I'm using.....
> >
> > 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
> >
> >
> > --
> > Thanks in advance....

do you need to declare "dbOpenTable"

Do you have Option Explicit at the top of your module?

"Matthew" wrote:

> I am trying to import a table from an access database that is saved on my c
> drive and import it into a worksheet.....I keep getting a type mismatch error
> when I try to set the recordset.....Got any suggestions?....Here is the code
> I'm using.....
>
> 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
>
>
> --
> Thanks in advance....