Open recordset using ODBC

[Mr B]

The following code connects to an existing data source using and existing
ODBC connection when used in MS Access.

I am attempting to make the same connection from Excel, with one exception.
When used in an Access enviornment, the ODBC connection is used to link the
table. I can then use a different statement to actullay open the recordset.

I am getting an error at the "rstRecordset.Open" statement.

Here is the code:

Dim cnnConnect As ADODB.Connection
Dim rstRecordset As ADODB.Recordset


Set cnnConnect = New ADODB.Connection
cnnConnect = "ODBC;"
cnnConnect = cnnConnect & "DSN=ACCOUNTING;"
cnnConnect = cnnConnect & "APP=Microsoft Excel;"
cnnConnect = cnnConnect & "DATABASE=PHAGF;"
cnnConnect = cnnConnect & "UID=ACCTNG;"
cnnConnect = cnnConnect & "PWD=dtatrf;"
cnnConnect = cnnConnect & "TABLE=BPCSF_GPM"

Set rstRecordset = New ADODB.Recordset
strSql = "SELECT Max(BPCSF_GPM.PYEAR) AS MaxOfPYEAR FROM BPCSF_GPM " _
& "WHERE (((BPCSF_GPM.POPNCL)=""Y"") AND ((BPCSF_GPM.PAROFF)<21));"

rstRecordset.Open _
Source:=strSql, _
ActiveConnection:=cnnConnect, _
CursorType:=adOpenDynamic, _
LockType:=adLockReadOnly, _
Options:=adCmdText

varMaxYear = rstRecordset.Fields("MaxOfPYEAR").Value
rstRecordset.Close

Any assistance will be appreciated.

Mr. B

 

[MH]

What is the error?

 

[Mr B]

The error is:
Application-defined or object-defined error

Mr B

What is the error?