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 wrote:[color=blue]
> 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
>[/color]
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
'---------------

On 5 Apr 2005 15:42:58 -0700, "Patrick A" <melted1@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.

[color=blue]
>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[/color]

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]

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

Patrick A wrote:[color=blue]
> 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?[/color]

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)

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