Type mismatch when setting recordset

[James]

I have updated an Access database from 97 to 02 and for
some reason this piece of code is giving a type mismatch
when it sets rstATTACH equal to tbl_ATTACH. I have
included a simplified version of subroutine below. Any
help would be much appreciated.

Private Sub btn_FileName_Click()

Dim strInsertFilename As String
Dim dbsData As Database
Dim rstATTACH As Recordset
Dim booRecordAdded As Boolean

Set dbsData = CurrentDb()
Set rstATTACH = DbData.OpenRecordset("tbl_ATTACH")

InsertFilename = OpenFiles()
If Not IsEmpty(strInsertFilename) Then
_strInsertFilename = "#" & Left$(strInsertFilename,
_InStr(strInsertFilename, Chr(0)) - 1) & "#"

rstATTACH.AddNew
rstATTACH!Work = Me.REF
rstATTACH!Attachment = strInsertFilename
rstATTACH.Update
booRecordAdded = True

End Sub

 

[Scott McDaniel]

You must set a reference to DAO 3.6 in your project. From the Visual Basic
Editor, click Tools - References, and find and check Microsoft DAO 3.6. For
a workaround, move it up higher on the list than ADO. However, to avoid
problems you need to disambigulate your code by explicitly referring to the
proper library:

Dim rst As DAO.Recordset
Dim rs As ADODB.Recordset
Dim dbs As DAO.Database
etc etc

Also, ALWAYS clean up after setting object references. If you open it, close
it ... if you set it "unset" it:

Dim dbsData As DAO.Database
Dim rstAttach AS DAO.Recordset

<other stuff here>

Set dbsData = Nothing
rstAttach.Close
Set rstAttach = Nothing

 

[Dan Artuso]

Hi,
Newer versions of Access set a reference to ADO by default.
Both ADO and DAO have recordset objects.

Qualify the rst object:
Dim rst As DAO.Recordset

Or remove the reference to ADO

If you leave it in, it's a good idea to qualify all DAO objects.

 

[Guest]

Cheers Scott and Dan, works a treat.