[RESOLVED] declare as recordset?

[forunna]

Hello there,
This code wont let me declare rs as a recordset, it will only work if I declare it as a variant. It gives a Type Mismatch error.
I have used this code in other projects as a recordset. (I think)
What have I missed?
Is there a problem if I leave it as a variant anyway?

It is merely grabbing a record from Access to display in a Excel user form, selecting from a listbox on the same form.
Excel 2003, Access 2003, VBA.

Public Sub WholeOfVolly()
'Populates the viewing form with all the chosen volunteers data

Dim a As Integer, i As Integer, dbid As Integer, wholename As String
Dim db As Database, qry As String, rs As Variant

'Get database record ID, a, from selected row
For a = 0 To frmVollyView.ListBox2.ListCount - 1
    If frmVollyView.ListBox2.Selected(a) Then
        dbid = frmVollyView.ListBox2.Column(3, a)
    End If
Next

'Populate text boxes with chosen volly data
    Worksheets("sheet1").Range("a1").Value = Application.ActiveWorkbook.Path & "\"
    wholename = Worksheets("sheet1").Range("a1").Value & Worksheets("sheet1").Range("a2").Value
Set db = OpenDatabase(wholename, False, False, "MS Access; pwd=xxxx")
    qry = "select * from tblVolunteerDetails where ID = " & dbid
Set rs = db.OpenRecordset(qry)
If rs.RecordCount <> 0 Then
With frmVollyView
        .lblThId.Caption = "ID: " & rs.Fields("thid")
        .lblName.Caption = "" & rs.Fields("title") & " " & rs.Fields("givenname") & " " & rs.Fields("surname")
        .lblRegion.Caption = "" & rs.Fields("region")
        .lblThStatus.Caption = "" & rs.Fields("thstatus")
        .lblAdd1.Caption = "" & rs.Fields("raddline1")
        .lblAdd2.Caption = "" & rs.Fields("raddline2")
        .lblAdd3.Caption = "" & rs.Fields("raddtown")
        .lblAdd4.Caption = "" & rs.Fields("raddstate") & " " & rs.Fields("raddpc")
        .lblAdd5.Caption = "" & rs.Fields("paddline1")
        .lblAdd6.Caption = "" & rs.Fields("paddline2")
        .lblAdd7.Caption = "" & rs.Fields("paddtown")
        .lblAdd8.Caption = "" & rs.Fields("paddstate") & " " & rs.Fields("paddpc")
        .lblPhnNum1.Caption = "" & rs.Fields("phone1code") & " " & rs.Fields("phone1num")
        .lblPhnNum2.Caption = "" & rs.Fields("phone2code") & " " & rs.Fields("phone2num")
        .lblMobNum1.Caption = "" & rs.Fields("mobile1")
        .lblMobNum2.Caption = "" & rs.Fields("mobile2")
        .lblEml1.Caption = "" & rs.Fields("email1")
        .lblEml2.Caption = "" & rs.Fields("email2")

End With
End If
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub

[Hack]

If I add a reference to DAO, I have no problem with the declaration.

Can you attach your spreadsheet?

[forunna]

Sorry, extremely security concious here. I cant upload any files.
I dont know what you mean by
"add a reference to DAO"

[anhn]

There are 2 different types of Database and Recordset here, one is DAO, the other one is ADO.
To avoid mixed-up, when declare you should explicit qualify with DAO or ADO:

Dim db As DAO.Database
Dim rs As DAO.Recordset

or

Dim db As ADO.Database
Dim rs As ADO.Recordset

DAO.Recordset cannot be used with ADO.Database
and ADO.Recordset cannot be used with DAO.Database

[forunna]

I dont know what that means but yes it works. thanks.
DAO.Recordset works.
do I need to know why it works? is it better than Variant?

[westconn1]

it is better to always give variables the correct type

variants are for when that is too hard, or programmers are too lazy to figure the correct type
several microsoft functions require the use of variant data type, so there are times when they should be used

[anhn]

With the question you asked

I dont know what you mean by
"add a reference to DAO"

I don't want to go into more details that may confuse you because other than guessing, we need to ask few more questions about the order of referencing to DAO and ADO if both of them are referred.

In this case both DAO and ADO libraries are used and ADO has higher priority than DAO, so if just declare Dim rs As Recordset then ADO will take over.
To avoid confusing to VB/VBA, the best way is declare as Dim rs As DAO.Recordset.

[forunna]

thanks for your time everyone.