Type Mismatch Error

[R Erskine]

I am trying to write a routine to pull a variable string
from a table that has only one record. I am getting a
type mismatch error, any ideas why?


'--------------------------------------------------------
' Import Client Records to ImportTable
'
'--------------------------------------------------------
Function ImportClient()
On Error GoTo ImportClient_Err

Call Test

' Import Client.txt File to ImportTable
DoCmd.TransferText
acImportDelim, "ClientImportSpecs", "ImportTable", "StrSQL
", True, ""

ImportClient_Exit:
Exit Function

ImportClient_Err:
MsgBox Error$
Resume ImportClient_Exit

End Function

'---------------------------------------------------------
' Routine to Call Import File Path Variable
'
'---------------------------------------------------------
Sub Test()

Dim Dbs As Database
Dim RstTest As Recordset
Dim StrSQL As String

Set Dbs = CurrentDb
StrSQL = "SELECT ImpFilePath FROM VariableData"
Set RstTest = Dbs.OpenRecordset(StrSQL)

If Not RstTest.BOF And Not RstTest.EOF Then
MsgBox "File path is " & RstTest!filepath
End If

Proc_Exit:
' Destroy Objects
Set Dbs = Nothing
Set RstTest = Nothing

End Sub

 

[Douglas J. Steele]

It would help if you said where the error was occurring...

I'm going to assume that you're using Access 2000 or 2002, and that it's
dying on the statement

Set RstTest = Dbs.OpenRecordset(StrSQL)

in Sub Test()

Your code is using DAO, while Access 2000 and 2002 use ADO by default. The
fact that you're not getting an "Unknown User Type" error on the line Dim
Dbs As Database implies that you've correctly added a reference to the
Microsoft DAO 3.6 Object Library. However, I suspect you didn't remove the
reference to Microsoft ActiveX Data Objects 2.x Library while you were at
it.

If you have both references, you'll find that you'll need to "disambiguate"
certain declarations, because objects with the same names exist in the 2
models. For example, to ensure that you get a DAO recordset, you'll need to
use Dim rsCurr as DAO.Recordset (to guarantee an ADO recordset, you'd use
Dim rsCurr As ADODB.Recordset)

The list of objects with the same names in the 2 models is Connection,
Error, Errors, Field, Fields, Parameter, Parameters, Property, Properties
and Recordset