Need ADO Recordset Help

[Mr B]

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

 

[AA2e72E]

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.

 

[Mr B]

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]

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).

 

[AA2e72E]

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

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).

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

 

[Mr B]

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

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

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).

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


:

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.

 

[Mr B]

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

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

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).

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


:

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.

 

[Mr B]

Well, I finally got it to work to return the value I was needing, using the
correct sql statement. The syntax is much different when used through this
ODBC connection.

Here is the statement that actually works:

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

Notice the single quotes.

Thanks again for all the help you provided.

Mr B

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

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

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).


:

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


:

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]

By the time I got to it, yu had it sorted. Well done.
There are several conventions about SQL statements that are best observed:
1. All SQL statements must be terminated by ; i.e. semi-colon. ORACLE's
SQL*PLUS requires it but Microsoft is not tto fussy. As you learn more ADO,
you will learn that some PROVIDERS can return multiple recordsets in the same
SDODB.Recordset object: in that context, ; is mandatoty.
2. strings embedded within SQL statements must be enclosed within single
quotes: Microsoft accepts double quotes most others do not. Literals such as
O'Shea must have the single quoted replicated ie. O''Shea
3. Always name calculated fields i.e Select SALARY *1.10 FROM EMPLOYEE does
not have a named colum (the driver assigns a name automatically) whereas
SELECT SALARY * 1.10 AS NEWSalary does.
4. All SQL's composed in UPPERCASE.
5. Different drivers/providers use other symbols for wrapping date literals
etc.

And so on...

The conventions come to matter a great deal when an application supports
multiple databases and the developer has to cater for variations in SQL
dialects; just an example, ORACLE strings are case sentitive whereas SQL
Server is not, by default.

Good luck with your future exploits with SQL.

Well, I finally got it to work to return the value I was needing, using the
correct sql statement. The syntax is much different when used through this
ODBC connection.

Here is the statement that actually works:

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

Notice the single quotes.

Thanks again for all the help you provided.

Mr B

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

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

:

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).


:

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


:

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.

 

[Mr B]

You are certainly correct about the differences in sql. I am having to learn
the hardway. I have had no expereience in the AS400 world but I am having to
extract data directly from those tables.

In the Access world. things are much simpler to deal with as, I'm sure, you
are aware.

Again, let me say how much it means to have someone be willing to offer and
provide the kind of help and support that you have been willing to offer and
share with me.

I am currently a little stuck again. Here's the deal.

I have a field in the AS400 table that is a numeric field. In most cases
this field only has 2 digit numbers. However, in somecases, there are some
entries where there is only one digit. In the Access world I would simply
use the Format function to return two characters in every case, preceeding
the single digit with a zero.

I have not been able to get anything to work to replicate this. I had one
of our AS400 people to tell me that they use the Digits function to produce
the desired results. I have not been able to get this to work throuh my sql
statement.

Do you have any ideas on this one. Sure could use some help.

Mr B

By the time I got to it, yu had it sorted. Well done.
There are several conventions about SQL statements that are best observed:
1. All SQL statements must be terminated by ; i.e. semi-colon. ORACLE's
SQL*PLUS requires it but Microsoft is not tto fussy. As you learn more ADO,
you will learn that some PROVIDERS can return multiple recordsets in the same
SDODB.Recordset object: in that context, ; is mandatoty.
2. strings embedded within SQL statements must be enclosed within single
quotes: Microsoft accepts double quotes most others do not. Literals such as
O'Shea must have the single quoted replicated ie. O''Shea
3. Always name calculated fields i.e Select SALARY *1.10 FROM EMPLOYEE does
not have a named colum (the driver assigns a name automatically) whereas
SELECT SALARY * 1.10 AS NEWSalary does.
4. All SQL's composed in UPPERCASE.
5. Different drivers/providers use other symbols for wrapping date literals
etc.

And so on...

The conventions come to matter a great deal when an application supports
multiple databases and the developer has to cater for variations in SQL
dialects; just an example, ORACLE strings are case sentitive whereas SQL
Server is not, by default.

Good luck with your future exploits with SQL.

Well, I finally got it to work to return the value I was needing, using the
correct sql statement. The syntax is much different when used through this
ODBC connection.

Here is the statement that actually works:

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

Notice the single quotes.

Thanks again for all the help you provided.

Mr B

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


:

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

:

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).


:

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


:

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]

Unfortunately, SQL is Structured QL and not Standard QL i.e each
driver/provider has its own additions to the SQL it supports and they do not
support all of them as defined in the SQL92 or subsequent standards: the
latest one is SQL2003. I have never worked with AS400. I would recommend that
you acquire 1. SQL in 10 minutes (SAMS) and 2. SQL Pocket Reference
(O'Reilley) (both around £10 each and excellent for learning. Neither is
about AS400 but useful for SQL generally.

Now, to concatenate '0' to a string, the syntax is :

'0' + Myfield
'0' & MyField (or '0' && MyField)
'0' | MyField (0r '0' || MyField)

You get the idea .. experiment with the concatenation symbol & see what
happens.

Say, '0' & MyField works for you. In order to take the last 2 characters,

Right('0' & MyField,2) as MyFieldx

might work.

Trouble is, although most drivers support this functinality, they do not all
use the Right keyword. I don;t know what the AS400 driver uses.

I'd like to know whow you get on.

You are certainly correct about the differences in sql. I am having to learn
the hardway. I have had no expereience in the AS400 world but I am having to
extract data directly from those tables.

In the Access world. things are much simpler to deal with as, I'm sure, you
are aware.

Again, let me say how much it means to have someone be willing to offer and
provide the kind of help and support that you have been willing to offer and
share with me.

I am currently a little stuck again. Here's the deal.

I have a field in the AS400 table that is a numeric field. In most cases
this field only has 2 digit numbers. However, in somecases, there are some
entries where there is only one digit. In the Access world I would simply
use the Format function to return two characters in every case, preceeding
the single digit with a zero.

I have not been able to get anything to work to replicate this. I had one
of our AS400 people to tell me that they use the Digits function to produce
the desired results. I have not been able to get this to work throuh my sql
statement.

Do you have any ideas on this one. Sure could use some help.

Mr B

By the time I got to it, yu had it sorted. Well done.
There are several conventions about SQL statements that are best observed:
1. All SQL statements must be terminated by ; i.e. semi-colon. ORACLE's
SQL*PLUS requires it but Microsoft is not tto fussy. As you learn more ADO,
you will learn that some PROVIDERS can return multiple recordsets in the same
SDODB.Recordset object: in that context, ; is mandatoty.
2. strings embedded within SQL statements must be enclosed within single
quotes: Microsoft accepts double quotes most others do not. Literals such as
O'Shea must have the single quoted replicated ie. O''Shea
3. Always name calculated fields i.e Select SALARY *1.10 FROM EMPLOYEE does
not have a named colum (the driver assigns a name automatically) whereas
SELECT SALARY * 1.10 AS NEWSalary does.
4. All SQL's composed in UPPERCASE.
5. Different drivers/providers use other symbols for wrapping date literals
etc.

And so on...

The conventions come to matter a great deal when an application supports
multiple databases and the developer has to cater for variations in SQL
dialects; just an example, ORACLE strings are case sentitive whereas SQL
Server is not, by default.

Good luck with your future exploits with SQL.

Well, I finally got it to work to return the value I was needing, using the
correct sql statement. The syntax is much different when used through this
ODBC connection.

Here is the statement that actually works:

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

Notice the single quotes.

Thanks again for all the help you provided.

Mr B


:

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


:

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

:

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).


:

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


:

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.

 

[Mr B]

I'll be giving the senerios you provided as try.

As for books, I will look for the ones you mentioned, however, I have one:
SQL for Dummies. It is pretty good for most things that I have done, but not
relative to the AS400 stuff.

I let you know when I get something working.

Mr B

Unfortunately, SQL is Structured QL and not Standard QL i.e each
driver/provider has its own additions to the SQL it supports and they do not
support all of them as defined in the SQL92 or subsequent standards: the
latest one is SQL2003. I have never worked with AS400. I would recommend that
you acquire 1. SQL in 10 minutes (SAMS) and 2. SQL Pocket Reference
(O'Reilley) (both around £10 each and excellent for learning. Neither is
about AS400 but useful for SQL generally.

Now, to concatenate '0' to a string, the syntax is :

'0' + Myfield
'0' & MyField (or '0' && MyField)
'0' | MyField (0r '0' || MyField)

You get the idea .. experiment with the concatenation symbol & see what
happens.

Say, '0' & MyField works for you. In order to take the last 2 characters,

Right('0' & MyField,2) as MyFieldx

might work.

Trouble is, although most drivers support this functinality, they do not all
use the Right keyword. I don;t know what the AS400 driver uses.

I'd like to know whow you get on.

You are certainly correct about the differences in sql. I am having to learn
the hardway. I have had no expereience in the AS400 world but I am having to
extract data directly from those tables.

In the Access world. things are much simpler to deal with as, I'm sure, you
are aware.

Again, let me say how much it means to have someone be willing to offer and
provide the kind of help and support that you have been willing to offer and
share with me.

I am currently a little stuck again. Here's the deal.

I have a field in the AS400 table that is a numeric field. In most cases
this field only has 2 digit numbers. However, in somecases, there are some
entries where there is only one digit. In the Access world I would simply
use the Format function to return two characters in every case, preceeding
the single digit with a zero.

I have not been able to get anything to work to replicate this. I had one
of our AS400 people to tell me that they use the Digits function to produce
the desired results. I have not been able to get this to work throuh my sql
statement.

Do you have any ideas on this one. Sure could use some help.

Mr B

By the time I got to it, yu had it sorted. Well done.
There are several conventions about SQL statements that are best observed:
1. All SQL statements must be terminated by ; i.e. semi-colon. ORACLE's
SQL*PLUS requires it but Microsoft is not tto fussy. As you learn more ADO,
you will learn that some PROVIDERS can return multiple recordsets in the same
SDODB.Recordset object: in that context, ; is mandatoty.
2. strings embedded within SQL statements must be enclosed within single
quotes: Microsoft accepts double quotes most others do not. Literals such as
O'Shea must have the single quoted replicated ie. O''Shea
3. Always name calculated fields i.e Select SALARY *1.10 FROM EMPLOYEE does
not have a named colum (the driver assigns a name automatically) whereas
SELECT SALARY * 1.10 AS NEWSalary does.
4. All SQL's composed in UPPERCASE.
5. Different drivers/providers use other symbols for wrapping date literals
etc.

And so on...

The conventions come to matter a great deal when an application supports
multiple databases and the developer has to cater for variations in SQL
dialects; just an example, ORACLE strings are case sentitive whereas SQL
Server is not, by default.

Good luck with your future exploits with SQL.
:

Well, I finally got it to work to return the value I was needing, using the
correct sql statement. The syntax is much different when used through this
ODBC connection.

Here is the statement that actually works:

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

Notice the single quotes.

Thanks again for all the help you provided.

Mr B


:

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


:

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

:

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).


:

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


:

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.