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....

Importing Access Tables
 

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....

Importing Access Tables
 

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....

Importing Access Tables
 

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....