Could you take a look at the follow sql statment? When I use the simple sql
statment that I posted, the recordset is returned, but when I attempt to use
the following statement, it fails:

strSql = "SELECT Max(PYEAR) AS MaxOfPYEAR FROM [BPCSF.GPM] " _
& "WHERE (((POPNCL)=""Y"") AND ((PAROFF)<21))"

This statement works from Access. I have made the corrections to the table
name.

Mr B


"AA2e72E" wrote:

Your connection string does not specify a Password: Isn't one needed?

"AA2e72E" wrote:

Is BPCSF_GPM the table/query name? or is it BPCSF GPM?
You might try enclosing the table name in [] i.e. try the following:

select * from [BPCSF_GPM]
select * from [BPCSF GPM]

and:

select * from [BPCSF_GPM$]
select * from [BPCSF GPM$]

I looks like the table name is incorrect somehow. If you know Access, try:

File | Get External Data | Import Tables

From the dialogue, drop the Files of Type box and select ODBC DataSources(),
specify your source and your table. (gives you an opportunity to see all the
available tables/queries).


"Mr B" wrote:

First, I want to thank you for your response. I do need help. I have been
able to accomplish this from Access with no problems, but for some reason I
have a mental block on this one.

I now have the following:

Dim strConn As String
Dim strSql As String
Dim myconn As ADODB.Connection
Dim rs As ADODB.Recordset

strConn = "Provider=MSDASQL.1; " _
& "Persist Security Info=False; " _
& "User ID=ACCTING; " _
& "Data Source=ACCOUNTING; " _
& "Initial Catalog=S408"

Set rs = CreateObject("ADODB.Recordset")

strSql = "SELECT * FROM BPCSF_GPM"
rs.Open strSql, strConn
Dim varTest
varTest = rs.Fields("PYEAR").Value
rs.Close

I have tested the connection string though the ODBC Admin and it reports the
the connection is good and successful.

When I step through the code and get to:
rs.Open strSql, strConn
I get the: "Run-time error '-2147217911 (80040e09)':
Automation error

Nothing I have tried has let me actually return a recordset. Is there
anything wrong with the statement being assigned to the "strSql" variable?

Mr B


"AA2e72E" wrote:

In this scenario, you do not need a connection Object, just a Recordset Object:


Set RS = CreateObject("ADODB.Recordset")

strConn = "Provider=MSDASQL.1; " _
& "Persist Security Info=False; " _
& "User ID=ACCTING; " _
& "Data Source=ACCOUNTING; " _
& "Initial Catalog=S408"


strSql = "SELECT * FROM BPCSF_GPM"

RS.Open strSql,StrConn

When you've finished with RS,

RS.Close
Set RS = Nothing.