RecordSet Type mismatch using Access 2007 VB script in Access 2002

[Marian M.]

Why am I getting a "runtime error '13': type mismatch" for the line
Set rsState = dbs.OpenRecordset("Select State From tblState2000
Where StateID = " & iLoop)

that is within the following script?

Sub MassImportAppend2000()

Dim strPath As String, strTableName As String, strFileName As String
Dim strSQL As String, strState As String
Dim iLoop As Integer
Dim dbs As Database, rsState As Recordset

Set dbs = CurrentDb
' Set the path to the directory where the files will be.
For iLoop = 1 To 51
Set rsState = dbs.OpenRecordset("Select State From tblState2000
Where StateID = " & iLoop)
strState = rsState!State ' Set the first state string
strPath = "E:\LSAY\Census\TabData\2000SF3\FlatASCI"
strTableName = strState & "00002" ' Set the first entry table name.
strFileName = strTableName & ".uf3" ' Set the first entry file name.
strSQL = "INSERT into sf30002"
strSQL = strSQL & " SELECT " & strTableName & ".*"
strSQL = strSQL & " FROM " & strTableName
DoCmd.TransferText acImportDelim, "AK00002 Import Specification",
strTableName, strPath & strFileName
dbs.Execute strSQL
DoCmd.DeleteObject acTable, strTableName
Next iLoop

End Sub

I am using this with VBA 6.3 c2001 VBA Retail 6.4,9972 Forms3: 11.0.5709
that is tied to Access 2002 sp3 on a WinXP sp2 build 2600 machine.

I used a similar script in Access2007 VBA with no trouble.

TIA
Marian

 

[Allen Browne]

Change the line:
Dim dbs As Database, rsState As Recordset
to:
Dim dbs As Database, rsState As DAO.Recordset

Explanation of why:
http://allenbrowne.com/ser-38.html

 

[Marian M.]

Thank you. That did the trick, exactly.

Change the line:
Dim dbs As Database, rsState As Recordset
to:
Dim dbs As Database, rsState As DAO.Recordset

Explanation of why:
http://allenbrowne.com/ser-38.html