[AccessD] Suitability of Access For Task

Jim Dettman jimdettman at verizon.net
Wed Mar 7 10:07:07 CST 2007


Larry,

  I'll basically ditto Mark's comments, but I generally prefer to allow
access to Startup/shutdown via the Windows task scheduler every x minutes.
The only time I leave it running constantly is if the monitoring it needs to
do is constant.

  This approach minimizes overall load on the server if it's running a bunch
of stuff.

  As John said, there is no reason why Access can't handle that.

Jim.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Lawrence Mrazek
Sent: Tuesday, March 06, 2007 12:24 PM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Suitability of Access For Task

Hi Folks:

I have a possible project that will need to:

1. Monitor a dbase table for changes, when a certain event is seen, read an
ID number and print it in EAN128 barcode format. 
2. Monitor another dbase table for a specific event, then write an ID number
to a temp table. 
3. Scan the barcode from #1 and compare it the ID number from #2 ... If they
match, write success codes to a table, if they don't, display error and
write info to a table. 

Given that this app will have to be running 12-18 hrs per day; it will
usually have to deal with 30-50 of these events per day, should I be looking
to write it in VB.NET? 



Larry Mrazek
LCM Research, Inc.
www.lcm-res.com
lmrazek at lcm-res.com
ph. 314-432-5886
mobile: 314-496-1645


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of artful at rogers.com
Sent: Tuesday, March 06, 2007 8:43 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Using a query name in an in clause

You are incorrect about when the static needs to be evaluated, JC. When
creating your subquery, place the name of the static function in the
criteria row of the appropriate column in the builder. That's all you need
to do. Of course, you must set the static function, prior to running the
query, from your front end, but if that is done then the query will run
exactly as expected. I have lots of queries and subqueries that work just
like this. The only down-side is that should you ever have to migrate the
query to SQL Server, then you have to rewrite it as a UDF that takes
parameters.

Anyway, I thought that you wanted to use the static inside the subquery, not
within the IN() clause. Perhaps I misread the original message.

 
Arthur 

----- Original Message ----
From: JWColby <jwcolby at colbyconsulting.com>
To: Access Developers discussion and problem solving
<accessd at databaseadvisors.com>
Sent: Tuesday, March 6, 2007 8:55:58 AM
Subject: Re: [AccessD] Using a query name in an in clause


Arthur,


>Also, presumably the subquery returns exactly one column.
yes

>I take it this is an Access query not a SQL query, since your message 
>is on
the AccessD forum. You could get around this problem by using static
functions in the subquery rather than references to the controls on the
form. Also, presumably the subquery returns exactly one column.

As you must know by now, you and I are the (co) kings of static functions.
And unfortunately, a static function doesn't help, for the reason that it
has to be evaluated before it can be inserted in the SQL statement.  I am
trying to make a simple query that sits in the combo that is being filtered.
The IN clause of a SQL statement appears to accept ONLY a comma delimited
list, or a query that returns a list.  It (apparently) cannot contain a
function, a name of a query etc.

I am quite capable of dynamically building a SQL statement and assigning
that sql string to the property of the table but I wanted to avoid that.  

John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of artful at rogers.com
Sent: Tuesday, March 06, 2007 8:40 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Using a query name in an in clause

I take it this is an Access query not a SQL query, since your message is on
the AccessD forum. You could get around this problem by using static
functions in the the subquery rather than references to the controls on the
form. Also, presumably the subquery returns exactly one column.

Arthur 



----- Original Message ----
From: JWColby <jwcolby at colbyconsulting.com>
To: Access Developers discussion and problem solving
<accessd at databaseadvisors.com>
Sent: Tuesday, March 6, 2007 8:18:45 AM
Subject: [AccessD] Using a query name in an in clause


I am trying to set up a filtered combo.  The combo needs to display records
"where AgencyID in (qryAgencyIDResultSet)" so to speak.  If I replace the
name qryAgencyIDResultSet with the sql that makes up qryAgencyIDResultSet
the query works just fine.  I really need to use a query name as shown
initially for the simple reason that the query references controls on forms
/ fltr() type things in order to select that result set.  Doing this
directly in the subquery gets darned messy.

Is it possible (is there any syntax) to reference the name of a query as the
subquery in an IN clause, rather than specifically placing the subquery's
SQL statement inside of the IN clause?  

The only way I have accomplished this in the past was to dynamically build
the query in a string variable and then assign that to either the combo's
property directly, or by editing the query def sql property.  I am trying to
avoid that messy stuff.

John W. Colby
Colby Consulting
www.ColbyConsulting.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list