Re: Need ODBC for every front end, or just server?

From: david epsom dot com dot au (david_at_epsomdotcomdotau)
Date: 02/23/05 

Date: Thu, 24 Feb 2005 09:52:22 +1100

> How would I do either of the above? And if I put 3 or 4 queries in the
> Backend, do I lose ALL benefit of having the application split into Front
> and Back ends?

The 3 or 4 queries would only run once daily, to update the local
tables?
And they don't actually have to be in 'the back end' if you wish
to keep that pure: they can be in another copy of the 'front end',
located on the 'odbc server' :~) pc, or in a separate 'front end',
used only for the update from ODBC.

> Or you can use task scheduler on a computer to run
> the action.

Queries can be run from VB Script. The task scheduler
can be set to run VB Script, or a program written in
any language. A VB Script file might include lines like this:

Dim dbe
Dim db

set dbe = createobject("dao.dbengine.36")
set db = dbe.OpenDatabase("qry_database")
db.Execute "select * from tbla into tblb ...." 

----
'or this
Dim objEngine
Dim objWS
Dim objDB
Set objEngine = wscript.CreateObject("DAO.DBEngine.36")
objEngine.DefaultPassword = G_PWD
objEngine.DefaultUser = G_USER
objengine.systemdb = G_SYSDB
Set objWS = objEngine.CreateWorkspace("",g_user,g_pwd)
Set objDB = objWS.OpenDatabase(GDB_SCRIPT)
objDB.Execute "INSERT INTO [ODBC;DSN=fred].[t1] ( f1,f2,f3 )
SELECT * FROM [ODBC;DSN=martha].[t2]
(david)
"rgrantz" <r_grantz@hotmail.com> wrote in message 
news:383lrjF5j9h1uU1@individual.net...
>- - -- -  - - - -
>
> Or you can use task scheduler on a computer to run
> the action.
>
> Or you can write your own program which run continuously
> on the server, and, in response to some other signal
> (for example, dropping a signal file into a shared folder)
> runs the action for you.
> - - - - - - - - - -
>
> How would I do either of the above?  And if I put 3 or 4 queries in the
> Backend, do I lose ALL benefit of having the application split into Front
> and Back ends?
>
>
>
>
> "david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message
> news:e5ZicGVGFHA.2992@TK2MSFTNGP09.phx.gbl...
>> Regardless if linking to a linked table 'works', the
>> ODBC driver to get the data needs to be installed on the
>> workstation, because the DAO/ADO/Jet code which is going
>> to use the ODBC driver is on the workstation, and it will
>> try to load the driver locally.
>>
>> One way around this is to put a program on another
>> PC (for example, on the file server) which, when
>> instructed to do so, loads a DAO/ADO/Jet/SCO object
>> which does the data transfer.
>>
>> OLE has support for remote object invocation: you
>> can use VB (enterprise?) to build a remote OLE object
>> that will do this for you.
>>
>> Or you can use task scheduler on a computer to run
>> the action.
>>
>> Or you can write your own program which run continuously
>> on the server, and, in response to some other signal
>> (for example, dropping a signal file into a shared folder)
>> runs the action for you.
>>
>> All of these suggestions involve running a program or
>> service on the PC where the ODBC driver is installed.
>> Unfortunately, merely putting a database file on the
>> file server is not sufficient to get a program to run
>> there.
>>
>> (david)
>>
>>
>>
>>
>>
>>
>> "rgrantz" <r_grantz@hotmail.com> wrote in message
>> news:381l8iF5gh4pqU1@individual.net...
>> >I am making an application that thus far:
>> >
>> > - Has a backend mdb on a shared drive
>> > - Has a Front End on every user's PC
>> >
>> > I will soon be using ODBC to connect to a separate central SCO db.  I
>> > intend
>> > to:
>> >
>> > - Run MakeTable/Append queries to update the tables in the one back end
>> > .mdb
>> > (to save server traffic/lag on the relatively sluggish sco server).  I
> am
>> > doing this by linking to the ODBC tables on my test PC, and running my
> own
>> > Front End's queries, etc. to append to the tables in the Back End .mdb.
>> >
>> > My question is:
>> >
>> > Do I need the ODBC license and install on every Front End PC, or can I
>> > make
>> > buttons/code etc. on the front ends that run the ODBC MakeTable/Append
>> > queries on the BACK end, thus needing only the one license and one
> install
>> > (on the PC or server w/ the shared drive).
>> >
>> > If I CAN do this, can anyone provide me with one example sub procedure
>> > that
>> > will run a query on the backend .mdb which updates its tables using
> links
>> > to
>> > the ODBC tables?
>> >
>> > By they way, if I link to the ODBC using the backend .mdb, I do NOT see
>> > those linked tables in the FRONT end's Linked Table manager.  Some
>> > newsgroup
>> > searching I did made it seem as if you cannot use links that are twice
>> > removed (linking to a linked table), but I've also heard the reverse.
> It
>> > would save a lot of money to only need the ODBC on one shared 
>> > PC/server,
>> > rather than on every front end, especially since the front ends are for
>> > data
>> > entry in the .mdb tables, and the updating using ODBC is primaily for
>> > validation and to avoid duplicate data entry.
>> >
>> > Any help or advice on this would be greatly appreciated.
>> >
>> > ODBC:  Providex 32-bit
>> > Windows Ver:  2000 Pro
>> > Access ver:  2000 Pro
>> >
>> >
>>
>>
>
>