Need ODBC for every front end, or just server?

[rgrantz]

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

 

[Douglas J. Steele]

I'm confused. Are you going to be connecting to the backend using ODBC for
everyone, or is the ODBC only required by your test PC to update the backend
from another source, and then everyone connects to the backend without ODBC?

In any case, ODBC must be installed on each workstation that requires it.
You can't install it on the server and use it from the workstations.

 

[Immanuel Sibero]

Hi rgrantz,

I'm confused too. I think some of the information you had found had the
answer.

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

I think I know where you're going with this, you're trying to connect your
BackEnd to the SCO db via ODBC and have our FrontEnd link to your BackEnd
and hoping that you would not need ODBC drivers on your workstations (since
the both the FrontEnd and BackEnd are both .mdb). I dont think this works.

Your BackEnd can certainly link to the SCO db via ODBC, but as you have
found out, those linked ODBC tables would not be accessible to your
FrontEnd; as you put it, *links that are twice removed (linking to a linked
table)* would not work. Your FrontEnd has to link to the ODBC tables
directly, which means you would need the ODBC drivers installed on every
machine running the FrontEnd.

Are you saying that the SCO db people charge you per seat license for ODBC
drivers to connect to their database? Or is there a third party involved?



Immanuel Sibero

 

[david epsom dot com dot au]

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]

The Front Ends only need to use the Access Tables in the Backend. However,
I want to update/append the Backend Access tables using data from the SCO
database. So, I was wondering if it is possible to run queries on the Back
End to update the Access .mdb tables, since the Front Ends don't technically
need to USE the SCO tables, they just use the .mdb tables that are
continuously appended by SCO. When I split a database, queries are in the
Front end, and only tables in the back. I can't make update/append queries
in the Front End that see the SCO linked tables in the back end, the tables
need to be linked to the Front End in order to run any kinds of queries.
However, since the SCO tables would ONLY be used to update the Access
tables, and the other forms/data entry, etc. only use the ACCESS tables, I
was hoping I could somehow update the Backend Access tables with one ODBC
license on the Back End PC. Perhaps make the Update/Append queries in the
Backend and somehow run them via click-buton events in the Front End? How
would I do this?


Thanks again

 

[rgrantz]

- - -- - - - - -

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]

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)