Using "DB2 Friendly" Commands in Access Query - InStr v. POSSTR

[Patrick A]

All -

I have the following in the Field row of a column in a query that runs
fine against a local table:

MSA: InStr([Forms]![frm_Browse1]![tb_strSQL],[CONTENT]![FL1])

I'm now trying to run the query against a linked DB2 table. I get a
"Connection Failed" error.

I think I understand 2 things from other posts:

1. DB2 is not going to accept this because of my use of the InStr.

2. The DB2 equivalent of InStr is POSSTR.

So I've modified the query so it reads:

MSA: POSSTR([Forms]![frm_Browse1]![tb_strSQL],[CONTENT]![FL1])

I get an "Undefined Function" error.

If what I think I know is true, how would I construct my field to use
POSSTR? I've tried lots of syntax variations with no joy.

If what I think I know is false, can someone point me in the correct
direction? Do I need to set up POSSTR in a module somehow?

Thanks,

Patrick

[John Mishefske]

Patrick A wrote:

All -

I have the following in the Field row of a column in a query that runs
fine against a local table:

MSA: InStr([Forms]![frm_Browse1]![tb_strSQL],[CONTENT]![FL1])

I'm now trying to run the query against a linked DB2 table. I get a
"Connection Failed" error.

I think I understand 2 things from other posts:

1. DB2 is not going to accept this because of my use of the InStr.

2. The DB2 equivalent of InStr is POSSTR.

So I've modified the query so it reads:

MSA: POSSTR([Forms]![frm_Browse1]![tb_strSQL],[CONTENT]![FL1])

I get an "Undefined Function" error.

If what I think I know is true, how would I construct my field to use
POSSTR? I've tried lots of syntax variations with no joy.

If what I think I know is false, can someone point me in the correct
direction? Do I need to set up POSSTR in a module somehow?

Thanks,

Patrick

Make the query a pass-through query. Then Jet doesn't evaluate the SQL
but instead sends it to the DB2 back-end where it is evaluated. Thus
you'll need to use DB2 SQL syntax and functions; it won't know anything
of VBA functions or Jet SQL Syntax.

With the query in design mode select Query, SQL Specific, Pass-Through
and the query will switch to a SQL editor where you can type in the DB2
SQL.

This should perform better as well since the processing takes place on
the server instead of in the front-end (depending on what your query
does all the records won't be dragged to the front-end first before
filtering for example).
--
'---------------
'John Mishefske
'---------------

[Tom van Stiphout]

On 5 Apr 2005 15:42:58 -0700, "Patrick A" <me*****@butter.toast.net>
wrote:

If you want to use syntax that is understood by the server (and not
necessarily by Access), then you need to use a Passthrough query.

-Tom.

All -

I have the following in the Field row of a column in a query that runs
fine against a local table:

MSA: InStr([Forms]![frm_Browse1]![tb_strSQL],[CONTENT]![FL1])

I'm now trying to run the query against a linked DB2 table. I get a
"Connection Failed" error.

I think I understand 2 things from other posts:

1. DB2 is not going to accept this because of my use of the InStr.

2. The DB2 equivalent of InStr is POSSTR.

So I've modified the query so it reads:

MSA: POSSTR([Forms]![frm_Browse1]![tb_strSQL],[CONTENT]![FL1])

I get an "Undefined Function" error.

If what I think I know is true, how would I construct my field to use
POSSTR? I've tried lots of syntax variations with no joy.

If what I think I know is false, can someone point me in the correct
direction? Do I need to set up POSSTR in a module somehow?

Thanks,

Patrick

[Patrick A]

Thanks, guys - esp. for the Step=by-Step.

Pass through queries are new to me, but I'll give it a try, and post
the results.

Patrick

[Trimmed Previous]

[Patrick A]

Wow. There's a ton in my query that's apparently not in DB2 SQL
syntax, or is not using DB2 SQL functions.

Can anyone recommend a good book or other resource where I can learn
how to translate from VBA functions and Jet SQL syntax into the DB2
equivalent?

Thanks,

Patrick

[MGFoster]

Patrick A wrote:

Wow. There's a ton in my query that's apparently not in DB2 SQL
syntax, or is not using DB2 SQL functions.

Can anyone recommend a good book or other resource where I can learn
how to translate from VBA functions and Jet SQL syntax into the DB2
equivalent?

This site seems to have the DB2 Help file info:

https://aurora.vcu.edu/db2help/index.htm

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

[Patrick A]

Looks handy and detailed - I'll check into it. Thanks, MG