Running a query on the Back End

[rgrantz]

I've searched the newsgroups about this, and any posts regarding my
particular situation are from 2000 or earlier, so I'm hoping a resolution
has come up since then.

I have a Back End that is used by several users' identical Front Ends. All
Front Ends ONLY use the Back End Access tables. However, I want to update
and append these Back End tables using linked ProvideX tables from a
separate SCO ERP table set. So, I want to Link the SCO tables to the Back
End, and just activate/run the BackEnd's update/append queries to append the
Access tables in the Back End which the Front Ends use. I have 2 reasons
for doing this:

- only having to pay for one set of ODBC drivers for the PC/server where the
BackEnd resides
- reducing network traffic and Server lag, since the queries on the Back End
would fill Access Tables only with usable data, whereas querying the SCO
tables per use per FrontEnd user take about 4 minutes for every Form/Data
entry action. For example, the forms and queries on the Front end never
need to see records from before 2003, whereas the SCO tables themselves have
records dating back to 1999. Using update/append queries and linking
FrontEnd forms/queries to the Access Tables rather than the SCO tables
reduces the records/filters from about 600,000 records to 30,000.

Is there any way to just run a BackEnd query using a FrontEnd button click
event or something? Again, the query would ONLY update/append the Back End
tables from SCO tables linked to that same Back End. If anyone knows the
code or whatever that's needed, let's assume a sample SCO Linked table is
named Order_Status, the Access Back End table to be updated/appended is
OrderStatusAcc, and the path to the Back End is
//ServerRoot/C:/BackEnd/AccessBackEnd.mdb.

Thanks for any help here, it is much appreciated.

 

[Alex Dybenko]

Hi,
as i understand you - this will not work as you expect. even if BE is
located on a server, Jet is still running on a local PC, so in order to
connect to SCO tables it need ODBC drives on local PC also

solution can be - is to build a separate application to be run to server,
which will do import, and which will be intiated from any frontent by some
flag in BE table for example

HTH

 

[David C. Holley]

Have you thought about loggin onto the server directly and running the
query directly from within the backend? If you don't have physically
access to the server, a product like PCAnywhere can give you access to
the server's desktop.

 

[rgrantz]

Thanks for the reply. Is there any kind of example you can give me as to
how to do this? All I need is for these tables in the Back End to be
updated from SCO tables which would be linked to that same Back End. What
sort of application would I make, and what do you mean by flag?


Thanks again

 

[Alex Dybenko]

Hi,
just make an access application, which runs on server and do following
tasks:
every 5 mins (1 hour) it checks to a value in a field "run import" of a
table "settings"
if it=true - is run all your import queries and then set flag back to false

so when some user need to run import - in just set this flag field to true

Also you can build the same on VB, and run it as a service on your server.
then you dont need to have a logged session runnig

 

[rgrantz]

Can I check and see if I'm thinking about this right?

I make a completely separate .mdb from the Back End .mdb, and place it on
the server, or even in the folder, where the Back End resides. I put one
table in the new .mdb called Settings. I make one field in the Settings
Table called RunImport. I make it a Yes/No or True/False field. I then
make a module in the new .mdb that uses the timer event to every 50,000,000
or whatever milliseconds, looks at Settings.RunImport, and if it is False,
docmd.runcmd.openquery1, docmd.runcmd.openquery2 etc. etc. and then what?
Why would I set the value as different, when it's a timer that drives the
queries running? Shouldn't it just be a timer that does it, rather than a
field value? I may be thinking about it wrong, I hardly ever use the Timer.

Would it be possible to get a code snippet assuming the tablenames above?
I'm not sure how to run a query in a separate db using VBA, and I also don't
know how to use the timer event except on forms.

Thanks again, I appreciate the consideration

 

[Poul Max Christensen]

Hi - just a bit of info
I have tried the same thing and ran into a problem when a FrontEnd(FE) was
open with a link to the BackEnd(BE), then the BE could not perforn the
update. The soultions for me was two applications, one Datapart and a
Reportpart per user.
When a user opens Datapart it is the same as the BE, but with an user
interface to start up an update and on this user interface You can se when
the last update has been performed.
Opening a Reportpart it is a FE where I do a tableimport with VBA from the
BE so I don't need to link to the BE and in the FE users also can se when an
update has been performed.

Best regards
Poul

 

[Alex Dybenko]

Hi,
yes your idea is correct. timer - you have to use one from the form (for
example a hidden one)

in timer event you check if Settings.RunImport=true, if yes - then run
queries, and then set it back to false

 

[Alex Dybenko]

I guess the part I'm confused about is setting the value to false after the
queries are run. Won't this mean that every check in the future will
return false, so the queries would never run? If I want to have the timer
check every certain amount of time, doesn't the value need to be "true" at
every check?

as i understand you - user can force import to run. so when user need to run
import - he just set this flag to true. but if you need to run import say
every 20 mins - then you dont need this flag.

And how do I loop this checking from a form? I looked at the timer help,
and didn't see anyhting about looping. Do I need to some extra VBA to
start the timer again after it hits the first timer amount set? For
instance, to run these queries every 20 minutes, what would the VBA behind
the timer look like, and would there be some other code necessary to start
the timer over?

you can just set appropriate timer interval (in milliseconds), and timer
event will fire automatically.

 

[rgrantz]

I guess the part I'm confused about is setting the value to false after the
queries are run. Won't this mean that every check in the future will return
false, so the queries would never run? If I want to have the timer check
every certain amount of time, doesn't the value need to be "true" at every
check?

And how do I loop this checking from a form? I looked at the timer help,
and didn't see anyhting about looping. Do I need to some extra VBA to start
the timer again after it hits the first timer amount set? For instance, to
run these queries every 20 minutes, what would the VBA behind the timer look
like, and would there be some other code necessary to start the timer over?

Sorry if I'm coming across as being thick, I'm just trying stuff here and
nothing's working so far (except using ODBC on every user's machine)