Dear Jason:
I am referring to using VBA coding to create a single string, which is in
the form of a SQL command acceptable to the AS400. How or when you do this
depends on what this SQL does. It could the the RecordSource of a form or
report, it could be the RowSource of a Combo Box or List Box, or it could
just be used for a datasheet. When you do this is whenever there is a
change that you want to have reflected on a form, when you open the report,
or when the change affects the combo box or list box. You haven't given me
enough detail of this where I could say which of these it might be.
When it comes to coding this, you must first be able to hand write the SQL
for the AS400. Until you are skilled enought to do this with an editor,
you're certainly not going to be able to code to create it. When you code
to create it, you need to be able to READ the SQL created by your code to
see it is correct. If you cannot both read SQL and see every detail of any
mistake you have made, and also code to create and fix that created SQL
string, you're probably wasting your time trying to do this. But when you
are able to do so, you will be rewarded by an excellent piece of working
software. I mention this because I cannot tell if this area might be where
your problem lies, or if it is somewhere else. And prying into what you can
an cannot do is part of being able to help.
The coding, then, is the process of:
1. simply write a non-varying string that produces a fixed result (not
reacting dynamically to the controls on your form) successfully from the
AS400.
2. having that code insert the variable pieces using values from your form
You will need to use the debugging facilities of the VBA window heavily as
you learn to do this, especially the immediate window. You probably should
use a Debug.Print statement to display the SQL generated by the code each
time so you can read it for any errors. If you're at least a bit familiar
with these features, you're probably on your way to building this. If not,
then you've got some thing to learn. Without the ability to fine tune code
this way, your path to a working result would be quite tenuous. At least,
I'd hate to think of having to do such work myself without the invaluable
feedback this provides. On the other hand, with a little practice, this
isn't a half bad task to attempt once you know how to use such features.
--
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - your one stop database experts
Jason said:
Ok - very helpful so far, but I've been unable to figure
out how to write the code you're refering to. Do I write
it on the form where the user is inputting the selection
criteria? I tried making a command button on the form and
then passing the values to two strings, running the query
using the open query, and then refering to the strings in
the SQL, but this didn't work.
Can you guide me just a little more please?
Thank you,
Jason
-----Original Message-----
Dear Jason:
You need to use code to extract the VALUE from the control, then write that
value into the SQL string you are going to submit to the AS400.
--
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - your one stop database experts
Jason said:
I had attempted to do this with only limited success. The
problem I ran in to was that I guess I don't know how to
reference Access forms in the pass through SQL. I was
doing it the same as I did for any other query, but I got
and error that the "[" character was not allowed. The
actual message reads:
"ODBC--Call failed
[IBM][Client Access Express ODBC Driver (32-bit)]
[DB2/400SQL]SQL0104 - Token [ was not valid. Valid Tokens
( + - ? : DAY RRN CASE CAST CHAR DAYS HOUR LEFT TRIM USER
YEAR. (#-104)"
How then do I reference the form that the users are
entering their criteria into?
Here's the SQL I was trying to use:
SELECT ORDN09 as PO, VNDR09 as VndrNum, ITEM09 as PN,
OQTY09 as OrdQty, TQTY09 as TransQty, DUED09 as DueDate,
RECD09 as RecvDate
FROM "PMP09"
WHERE TQTY09>0 AND RECD09 Between [Forms]![frmDate]!
[StartDateAS400] And [Forms]![frmDate]![EndDateAS400];
Thanks in advance,
Jason
-----Original Message-----
Dear Jason:
You'll probably get the best performance for the least
effort if you write
the whole query system as pass-through to the AS400.
You could use code to
write the SQL based on the "user entered criteria" and
let the AS400 do the
calculations. If that isn't fast, then the AS400 is not
fast, and nothing
you do that has to wait for the AS400 will be fast.
Another approach is to import the entire database from
the AS400 and work on
a local copy of the data. This has obvious drawbacks in
not being able to
see changes to the data as soon as they are posted on
the AS400, but if that
is acceptable you may be able to do considerable to
improve this.
--
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - your one stop database experts
I'm working on my first database that uses another
with a
large (~500,000) number of records. As such, my queries
are running very slow.
Any input on what I can do potentially to speed things
up
as far as how the query is designed?
The first query is a pass-through query that polls an
AS400 database table to retrieve some records. The
second
narrows the list down based on user entered criteria
(date range). The data is then used to do some
calculations which are then summed in a totals query.
The
final query takes almost 10 minutes to run.
Any input would be appreciated.
Thanks,
Jason
.
.
