ODBC for Access back end in WAN environment

[Remco Groot Beumer]

Hello all,

Currently we are implementing an Access database which has to send some (not
much) data over a WAN. We are using an Access front end and an Access back
end. Basicly the front end runs local, but at the end of the session the
front end needs to write the data to the back end on the WAN.
The responsible IT manager asked me to use a ODBC linkage between the
database, since this sends smalles packages of data (which minimizes the
time out failure) instead of linking it directly (he told me that when a
drive mapping is done, the virus scanning causes to much data traffic). I
don't know if these assumptions are correct.
Since i'm no network specialist: Is it possible to create a ODBC on the
server and link it on the front end side? And more important: how does it
work? Has any body some ideas or literature for this kind of operation. When
I create a ODBC (system or/and DNS) and I want to link it in my Access
database I get a error that now data can be linked through a ODBC (it also
mentioned ISAM in it's error description, i don;t have it on me currently).
When I use my ODBC in Excel it works great, so the ODBC is fine, it only
doesn't work on a Acces front end?

I'm aware that an Access database is not the ideal backend source for this
kind of operation, but for now it has to do...in the nearby future we will
implement a DBMS.

Any help would be gladly appreciated,

Remco Groot Beumer
Holland

[Douglas J. Steele]

"Randy Harris" <ra***@SpamFree .com> wrote in message
news:tk******** *******@newssvr 15.news.prodigy .com...

I'm puzzled. How can I communicate from one .mdb to another .mdb via
ODBC. even using ADO?
Carl Prothman has all sorts of ADO connection strings posted at
http://www.able-consulting.com/ado_conn.htm including some that use ODBC
rather than Ole DB. As I indicated elsewhere in this thread, I've
successfully used this approach to read data from an Access 2000 database from Access 97. It is, however, quite limited in terms of what you can

do.

Sorry, I'm still trying to understand. I searched Carl's site, all of the
examples show local access to the .mdb (even when the .mdb is on a network
share), except for ASP server and MS Remote. He has this posted there as
well:

if MDB is located on a remote machine
- Or use an XML Web Service via SOAP Toolkit or ASP.NET
- Or upgrade to SQL Server and use an IP connection string
- Or use an ADO URL with a remote ASP web page
- Or use a MS Remote or RDS connection string
Is there a means of accessing data in a .mdb from a remote system other than by use of a web server?

See Albert's explanation.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)

[Randy Harris]

Albert, thanks very much for the clarification.

Randy
"Albert D. Kallal" <pl************ ********@msn.co m> wrote in message
news:7rpec.8463 1$Ig.68131@pd7t w2no...

"rkc" <rk*@yabba.dabb a.do.rochester. rr.bomb> wrote in message
news:Vb******** ***********@twi ster.nyroc.rr.c om...

"Douglas J. Steele" <NOSPAM_djsteel e@NOSPAM_canada .com> wrote in message
news:r5******** ************@tw ister01.bloor.i s.net.cable.rog ers.com...

Access cannot use ODBC to communicate between MDB files.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)
Can you not use ADO from within an .mdb file to communicate with
another .mdb file via ODBC?

Yes, you can setup and use a odbc connection string. However, you are NOT
communicating across the network using odbc. What happens is the

following:

ADO---->ADO ODBC DRIVER---> DAO JET engine -->jet opens file
\\servername\my data.mdb

Note how JET is STILL getting a fully qualified path name to the back end.
So, yea..you are using odbc to connect to JET...but then jet is simply
opening the file across the network. This simply means that yes..you are
using odbc to connect to JET..but then jet uses the standard file share
ACROSS the network. (you are NOT sending sql across the network..since ONLY a file share exists on the other end!).

So, no..you can't use odbc to connect to access database on another
computer. You can certainly specify a odbc connection...bu t then you are
telling JET in a different way to open the same file.

In all cases..you are still using a file share..and the file is being opened ACROSS the network. There is NO performance advantage to this over a simple linked table as a file share. Either way...jet opens the file across the
connection. Either way, the JET library and code resides on the CLIENT
COMPUTER.

So, the virus software..and all other stuff that occurs will STILL occur!

You can read the following article of mine as to why you can't use a wan:

http://www.attcanada.net/~kallal.msn/Wan/Wans.html
--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
pl************* *****@msn.com
http://www.attcanada.net/~kallal.msn

[Larry Linson]

"Remco Groot Beumer" wrote

Is it possible to create a ODBC on the
server and link it on the front end side?

As others have pointed out, Albert's being the "telling point", you
don't gain anything by using ODBC, and I have never seen any
Access-Jet lashup that would work over a WAN with any consistency and
reliability, except one.

That one used an application at each end, communicating through
Winsock... so the communication was not from the remote application to
the database, it was from the remote application to another
application, could send just the data needed to identify and update
the record, and the update was done locally by the application that
received the data. In the specific case I observed, it was two VB
applications, but as far as I know, you can use WinSock via API from
Access itself if you wish. You might find some information about this
at http://www.wolfwares.com -- the site owner is the one who
implemented it.

You could categorize this approach as using Access to implement your
own, little very specialized client-server system.

Larry Linson
Microsoft Access MVP