Re: Running a query on the Back End

From: rgrantz (r_grantz_at_hotmail.com)
Date: 03/04/05 

Date: Fri, 4 Mar 2005 11:30:39 -0800

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

"Alex Dybenko" <alex@PLEASE.cemi.NO.rssi.SPAM.ru> wrote in message
news:u0XH97MIFHA.156@TK2MSFTNGP10.phx.gbl...
> 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
>
> --
> Alex Dybenko (MVP)
> http://Alex.Dybenko.com
> http://www.PointLtd.com
>
>
> "rgrantz" <r_grantz@hotmail.com> wrote in message
> news:38r1scF5qppiiU1@individual.net...
> > 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" <alex@PLEASE.cemi.NO.rssi.SPAM.ru> wrote in message
> > news:%23JDqtdJIFHA.2744@tk2msftngp13.phx.gbl...
> >> 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
> >> --
> >> Alex Dybenko (MVP)
> >> http://Alex.Dybenko.com
> >> http://www.PointLtd.com
> >>
> >>
> >>
> >> "rgrantz" <rgrantz@hotmail.com> wrote in message
> >> news:38q12hF5o79rdU1@individual.net...
> >> > 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.
> >> >
> >>
> >>
> >
> >
>
>