Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Working with MS-Access functions

2 posts in DataWindow Last posting was on 2009-07-16 21:03:56.0Z
Mariano Recuero Posted on 2009-06-22 10:02:35.0Z
Sender: 4b32.4a3f5358.1804289383@sybase.com
From: Mariano Recuero
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Working with MS-Access functions
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4a3f56bb.4d90.1681692777@sybase.com>
NNTP-Posting-Host: forums-3-dub.sybase.com
X-Original-NNTP-Posting-Host: forums-3-dub.sybase.com
Date: 22 Jun 2009 03:02:35 -0700
X-Trace: forums-3-dub.sybase.com 1245664955 10.22.241.188 (22 Jun 2009 03:02:35 -0700)
X-Original-Trace: 22 Jun 2009 03:02:35 -0700, forums-3-dub.sybase.com
Lines: 16
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89529
Article PK: 418717

PowerBuilder 11.5 - MS Access 2003

Hi, im working with a PB app that connect to an MS Access
DB.
Im trying to get data in a DW from a Query (built in Access)
which reference to another Query and this one call a
function public that i have created in a Module.
If i execute the Query in Acce it works fine.
Then if i execute from PowerBuiler i get an error: Undefined
function in the expression.

is not possible to work with Querys that call functions in
Access?
Is there any way to get it?

Thanks in advance


Bill Chestnut Posted on 2009-07-16 21:03:56.0Z
Sender: 526b.4a5f8b29.1804289383@sybase.com
From: Bill Chestnut
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: Working with MS-Access functions
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4a5f95bc.5a2e.1681692777@sybase.com>
References: <4a3f56bb.4d90.1681692777@sybase.com>
NNTP-Posting-Host: forums-3-dub.sybase.com
X-Original-NNTP-Posting-Host: forums-3-dub.sybase.com
Date: 16 Jul 2009 14:03:56 -0700
X-Trace: forums-3-dub.sybase.com 1247778236 10.22.241.188 (16 Jul 2009 14:03:56 -0700)
X-Original-Trace: 16 Jul 2009 14:03:56 -0700, forums-3-dub.sybase.com
Lines: 67
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89651
Article PK: 418839

Mariano,
As far as I know, (that may not be very far!) Ms Access can
only offer you the parts of the system that are exposed to
the Jet engine and ODBC. When you connect to Access from PB,
you aren't really running MS Access at all, you are simply
getting connected to an .MDB file via the ODBC drivers.
Queries show up in the tables because they are seen as
"views" in a sense by ODBC. However, there are limits on
that too. For example, if the query is accessing a
connection to a different database, I seem to recall that
that query will simply not show up in the tables/views
selection in PB. If it does, the data will not be updateable
from PB.

As far as Module functions are concerned, they are not
really available when you are connecting to PB. You are
thinking, perhaps, of having MS Access work as though it had
"stored procs" the way Oracle and SQL Server do.

There is a way to do this in a primitive sort of way. You
can set up a timer event inside a function in MS Access
modules, that looks at a particular table for "commands" to
execute. Then you have PB insert records into this "command"
table. When the timer event in the Access module fires, it
should look at the command table and execute the commands.
Your Module code in Access will also have to include a
little command processor.
Commands could include instructions to run Update queries or
run Module functions. You have to design the commands to do
this.

Why can functions work this way and yet not the way you
want? In order to make this work, you have to run the Access
database as a program. You can do this through PB by running
the MS Access .exe with the .MDB file as a command line
parm, using the PB powerscript function Run ( string {,
windowstate } ). When the MS Access app itself is up and
running it can then run its "listener" via the timer and
pick up commands from the PB front end by looking at the
command table for commands. Then it has to delete those
commands or set a flag in the table indicating that they
have been processed, and in your PB APP you will also need
an object with a timer function that watches for this flag
update.

I have experimented with this concept more out of curiousity
that anything else and found that it works quite nicely as a
very crude stored proc surrogate.

hope this helps.

> PowerBuilder 11.5 - MS Access 2003
>
> Hi, im working with a PB app that connect to an MS Access
> DB.
> Im trying to get data in a DW from a Query (built in
> Access) which reference to another Query and this one call
> a function public that i have created in a Module.
> If i execute the Query in Acce it works fine.
> Then if i execute from PowerBuiler i get an error:
> Undefined function in the expression.
>
> is not possible to work with Querys that call functions in
> Access?
> Is there any way to get it?
>
> Thanks in advance