By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,274 Members | 2,229 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,274 IT Pros & Developers. It's quick & easy.

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

P: n/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

Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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
'---------------
Nov 13 '05 #2

P: n/a
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


Nov 13 '05 #3

P: n/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]

Nov 13 '05 #4

P: n/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

Nov 13 '05 #5

P: n/a
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)
Nov 13 '05 #6

P: n/a
Looks handy and detailed - I'll check into it. Thanks, MG

Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.