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
--
HTH

Mr B

What is the error?

"Mr B" <(E-Mail Removed)> wrote in message
news59B7E84-5AD7-405A-A560-(E-Mail Removed)...
> 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
> --
> HTH
>
> Mr B

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

Mr B


"MH" wrote:

> What is the error?
>
> "Mr B" <(E-Mail Removed)> wrote in message
> news59B7E84-5AD7-405A-A560-(E-Mail Removed)...
> > 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
> > --
> > HTH
> >
> > Mr B
>
>
>