DAO playing havoc with my brain.

[Deltaflyer]

I am using the following code to fill out a combo box with items from an access database, but each time i try to execute it i am coming up with "TYPE MISMATCH Error 13" on the Set rs=db.OpenRecordset line, can anybody help me please.
Dim ws As Workspaces
Dim db As Database
Dim rs As Recordset

Set db = Workspaces(0).OpenDatabase(databasepath)
sField = "Field"
sTable = "Table"
ssql = "SELECT DISTINCT " & sField & " FROM " & sTable & " ORDER BY " & sField
MsgBox (ssql)
Set rs = db.OpenRecordset(ssql, dbOpenSnapshot)
Do Until rs.EOF
Combo1.AddItem rs(sField)
rs.MoveNext
Loop
rs.Close
Set db = Nothing


Thanks, DeltaFlyer
The Only Programmer To Crash With Style. LOL

 

[strongm]

This is probably because you have references set for both ADO and DAO. Since you are not being explicit in your declarations there is a certain amount of 'guesswork' that VB does to decide which particular library you want an object from (I call it guesswork because I've never been able to figure out just how it decides). So what happens is that Dim db As Database is probably returning a DAO database object, and Dim rs As Recordset[/b} is returning an ADO recordset object. When you get to Set rs = db.OpenRecordset(ssql, dbOpenSnapshot) what is happening is that you are trying to set the ADO recordset as a DAO recordset, which gives you the type mismatch.

Solutions: remove ADO from your project references, or use explicit declarations, as follows:
[tt]
Dim ws As DAO.Workspaces
Dim db As DAO.Database
Dim rs As DAO.Recordset