473,568 Members | 2,933 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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
6 7508
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
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
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
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
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:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)
Nov 13 '05 #6
Looks handy and detailed - I'll check into it. Thanks, MG

Nov 13 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

17
2760
by: Bruce Jin | last post by:
I wonder how many people are using db2 on Windows? I know db2 is native to AS400 which has about 800,000 installations. Thanks!
1
7340
by: LazyAnt | last post by:
Does DB2 process "ALL" subquery by "nested iteration" strategy, or using its own special algorithm? For example, assume r(A, B, C) and s(D, E, F) are two relations, consider the following query: Q1: select r.* from r where r.A> 5 and r.C > all (select s.D from s
4
6448
by: banz | last post by:
Hello I have a problem to resolve: I wrote a Perlscript which caches data from a server (local on my machine) I would like to have a other connection to a remote server but I don't know how to define the servername / hostname in my Perl Progrem.. Here is the code:
0
1926
by: Don | last post by:
Hello, I am having some troubles and was hoping for some help. I have created links to DB2 tables using DB2 Connect I have multiple queries that I run each day and I need to provide them with a date to use in the query. I am trying to set up a textbox on my main form that I will enter the date into and then use that in the queries.
4
2448
by: Ray | last post by:
Does anyone out there have experience with using Db2 8.2 with a program called MISys (also known as ACCPAC). I am having some problems with the application running exteremly slow on workstations throughout my office. One of the more accessed tables, which only has 3000 rows, take almost 30 seconds to open through their application. MISys...
0
1078
by: dhuygu | last post by:
How can I run db2 commands from .net application? I want to create a new database from my application.Then I want to copy a table from the database on server to my new local database.I'm using db2 v8. By using admin_cmd() I can only export the files.But I couldnt find any procedure to create new database from my application and then import files...
1
7287
by: Siebbel | last post by:
Hello, I posted this problem already in the linux section but without positive result. Perhaps someone here has experienced the smae problem. I am pretty new with writing shell scripts and trying to use a script to perform some db2 commands on a CentOS 4 system. I get a problem when I put in my shell-file 'myscript.sh' the command: db2...
2
19351
by: SrivalliBapathi | last post by:
Hi, Please help me, if theres a way we can execute DB2 commands directly from the windows cmd prompt window instead of executing those from DB2 CLP window. I need to connect to a DB2 database and run a .sql script. I should have these commands in a batch file that should be executed from windows cmd prompt. The cmds that wrk from DB2 CLP...
3
3095
by: rajamurugank | last post by:
Hi, Good Morning friends, I am new in DB2 database.we have DB2(9.1) database on zLinux.I can access the DB using the admin user. But,i can't access the DB as a normal linux users. Now the issue is, How to give the DB2 access for all or some local users. Platform-RHEL4U4 Arch -s390
0
7597
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7909
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8115
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7654
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7961
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5492
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5216
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3630
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1206
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.