Access - Excel(VBA) - Type Mismatch

Question

We're trying to load data from Access Database using VBA to Excel. But our code doesn't work fine.

SQL1 = "SELECT Name " & _
SQL1 = SQL1 & "FROM February" & _
SQL1 = SQL1 & "WHERE Gender = 'F'" & _
SQL1 = SQL1 & "ORDER BY Name"
Set rs1 = db.OpenRecordset(SQL1, dbOpenSnapshot)

 If rs1.RecordCount = 0 Then
    MsgBox "No data retrieved from database", vbInformation + vbOKOnly, "No Data"
     GoTo SubExit
 Else
     rs1.MoveLast
     recCount = rs1.RecordCount
     rs1.MoveFirst
End If
  xlSheet.Range("B28").CopyFromRecordset rs1

We're getting an error of "Type Mismatched" in the line:

Set rs1 = db.OpenRecordset(SQL1, dbOpenSnapshot)

Can anyone help us and explain us why we're getting that error?

Your code is incomplete, does not how you've declared your variable (or if they are not explicitly declared in which case they should be by having an `Option Explicit` at the top, nor how you initialize the database. For all I can see, `db` is undeclared. Furthermore, I don't know whether `rs1` is implicitly declared as `ADODB.Recordset`, which would be a mismatch since `Database.OpenRecordset()` can only return a `DAO.Recordset`. — this, Mar 06 '19 at 04:31
Part of problem is lack of spaces at ends of FROM and WHERE lines building the SQL statement. Text is 'running together'. — June7, Mar 06 '19 at 05:13

score 1 · Answer 1 · answered Mar 06 '19 at 05:18

SQL1 = "SELECT Name " & _
SQL1 = SQL1 & "FROM February" & _
SQL1 = SQL1 & "WHERE Gender = 'F'" & _
SQL1 = SQL1 & "ORDER BY Name"

should be

SQL1 = " SELECT Name " & _
       " FROM February" & _
       " WHERE Gender = 'F'" & _
       " ORDER BY Name"

...or don't use the line continuation and use SQL1 = SQL1 &, but you can only use one of those options.

Access - Excel(VBA) - Type Mismatch

1 Answers1