Need ADO Recordset Help

I posted a question yesterday, and was not able to get the help I needed. I
have since tried several things to get a connection to the AS400 and return a
recordset using ADO.

I have now been able to actually make the connection without getting an
error, but when I attempt to get a recordset, I am still getting an error.

I would really appreciate some help.

Below is the code that I have now:

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 myconn = New ADODB.Connection
myconn.Open strConn

strSql = "SELECT * FROM BPCSF_GPM"

Set rs = New ADODB.Recordset
rs.Open (strSql), myconn

'here read some values from the recordset

rs.Close
myconn.Close

Mr B

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.

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.

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.

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.

AA2e72E,

You are the man. The fact is that the actual name of the table is
"BPCSF.GPM" with the dot not an underscore. Access does that because it
cannont use the dot in the table name.

The problem had nothing to do the the connection string, just the correct
table name.

Thank you very much.

I was just not seeing the forest for the trees.

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.

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.