Type Mismatch with OpenRecordset...what!?

[GuildBoss]

Been programming Access 97 for years on a Win98 machine but now I have
a new XP machine, installed Access 2002, and now I'm running into some
really baffling little errors. For example, why in the world would
this:

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("tblTest", dbOpenDynaset)

result in a type mismatch (on the OpenRecordset line)? This is
baffling. I've done this a thousand times on the old machine...

BTW, I have referenced DAO 3.6 and the code compiles fine. tblTest is
a local table.

Any ideas? What ridiculously minor detail am I overlooking?!

TIA
Paul

 

[Doug Chinnock]

I suspect that VB is using a library that has the type returned by
OpenRecordset different.

You might need "Microsoft Visual Basic for Applications Extensibiity 5.3"
there too.

--Doug

 

[Doug Chinnock]

Alse DAO3.6 should be above DAO2.1

I suspect that VB is using a library that has the type returned by
OpenRecordset different.

You might need "Microsoft Visual Basic for Applications Extensibiity 5.3"
there too.

--Doug

 

[Ken Snell]

You need a reference set to DAO library in the Visual Basic Editor. Open
VBE, click Tools | References, and click the DAO 3.x library. ADO library is
the default library in ACCESS 2000, not DAO. In ACCESS 97, DAO is the
default library.

If you don't deselect ADO library, be sure you disambiguate the references
to Recordset and other "common" objects by putting DAO. or ADODX. in front
of those objects, depending upon which object library you wish to use.

 

[Nithya]

I have the same problem and I have referenced DAO 3.6 and
also deselected ADO Objects. To avoid anyfurther problems
I have also declared explicity as
Dim dbLocal As DAO.Database
Dim replaceCodes As DAO.Recordset
But still I get the Type mismatch error..
So where else could the problem be?
Thanks in advance,
Nithya

 

[Ken Snell]

On what code step do you get the type mismatch error? Post the code and
let's see.

 

[Nithya]

This is the code

Private Sub Generate_Letter_Click()

Dim dbLocal As DAO.Database
Dim replaceCodes As DAO.Recordset
Dim strCurrAppDir As String
Dim strFinalDoc As String
Dim varReplaceWith As Variant
Dim wrdApp As Object
Dim wrdDoc As Object

On Error GoTo Err_Generate_Letter_Click

Set dbLocal = CurrentDb()
strCurrAppDir = Left$(dbLocal.Name, InStr
(dbLocal.Name, -"\ECMS old.mdb"))

On Error Resume Next
Kill strFinalDoc
On Error GoTo Err_Generate_Letter_Click

FileCopy strCurrAppDir & "\sample.doc", strFinalDoc

Set appWord = CreateObject("Word.Application")
Set wordDoc = appWord.Documents.Add(strFinalDoc)

appWord.Visible = True

Set replaceCodes = dbLocal.OpenRecordset
("StateReplaceCodes", -dbOpenSnapshot) ---- THIS IS WHERE
I GET THE TYPE MISMATCH ERROR.

Do While Not replaceCodes.EOF

varReplaceWith = Eval(replaceCodes!ReplaceWithText)
varReplaceWith = IIf(IsNull(varReplaceWith), " ",
CStr(varReplaceWith))

With wordDoc.Content.Find

If replaceCodes!Code = "{Contact Name}" Then
With .Replacement
.ClearFormatting
.Font.Bold = True
End With
End If

.Execute FindText:=replaceCodes!Code, _
ReplaceWith:=varReplaceWith, Format:=True, _
Replace:=wdReplaceAll
End With

replaceCodes.MoveNext
Loop

replaceCodes.Close

Exit_Generate_Letter_Click:
Exit Sub

Err_Generate_Letter_Click:
MsgBox Err.Description
Resume Exit_Generate_Letter_Click

End Sub

 

[Douglas J. Steele]

Is that really a minus sign before dbOpenSnaphot in the OpenRecordset
statement?

 

[Nithya]

Thank you very much. Now I have another question I am
really new to VB. Can you please explain me what
this ".Execute" does? My table is StateReplaceCodes with 2
fields Code and ReplaceWithText. During run time when I
tried to look what was there in Find Text, it was empty.
So I could not replace anything in my Word template.
Thanks in Advance,
Nithya

Set replaceCodes = dbLocal.OpenRecordset
("StateReplaceCodes", dbOpenSnapshot)

Do While Not replaceCodes.EOF

varReplaceWith = replaceCodes!ReplaceWithText
varReplaceWith = IIf(IsNull(varReplaceWith), " ",
CStr(varReplaceWith))

With wordDoc.Content.Find

If replaceCodes!Code = "{Contact Name}" Then
With .Replacement
.ClearFormatting
.Font.Bold = True
End With
End If

.Execute FindText:=replaceCodes!Code, _
ReplaceWith:=varReplaceWith, Format:=True, _
Replace:=wdReplaceAll
End With

replaceCodes.MoveNext
Loop

replaceCodes.Close

 

[GuildBoss]

You need a reference set to DAO library in the Visual Basic Editor. Open
VBE, click Tools | References, and click the DAO 3.x library. ADO library is
the default library in ACCESS 2000, not DAO. In ACCESS 97, DAO is the
default library.

If you don't deselect ADO library, be sure you disambiguate the references
to Recordset and other "common" objects by putting DAO. or ADODX. in front
of those objects, depending upon which object library you wish to use.

Thanks, that's what it was. Just unchecked ADO and it's all roses again.