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.