ODBC for Access back end in WAN environment

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

Access cannot use ODBC to communicate between MDB files.

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

"Remco Groot Beumer" <info@rgbplus.nl> wrote in message
news:c58dqe$e88$1@news3.tilbu1.nb.home.nl...[color=blue]
> Hello all,
>
> Currently we are implementing an Access database which has to send some[/color]
(not[color=blue]
> 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.[/color]
When[color=blue]
> 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[/color]
currently).[color=blue]
> 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
>
>[/color]

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:r5Vdc.64990$Bk31.25272@twister01.bloor.is.net .cable.rogers.com...[color=blue]
> Access cannot use ODBC to communicate between MDB files.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (No private e-mails, please)[/color]

Can you not use ADO from within an .mdb file to communicate with
another .mdb file via ODBC?

"rkc" <rkc@yabba.dabba.do.rochester.rr.bomb> wrote in message
news:VbVdc.34947$e17.12245@twister.nyroc.rr.com...[color=blue]
>
> "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
> news:r5Vdc.64990$Bk31.25272@twister01.bloor.is.net .cable.rogers.com...[color=green]
> > Access cannot use ODBC to communicate between MDB files.[/color]
>
> Can you not use ADO from within an .mdb file to communicate with
> another .mdb file via ODBC?[/color]

If you're willing to do everything through VBA, then yes, you can do that
(you can even query Access 2000 databases from Access 97 that way). That
means, though, that you're severely limited in terms of what you can do in
the Access front-end.

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

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:bpWdc.69906$SQE.66353@news01.bloor.is.net.cab le.rogers.com...[color=blue]
> "rkc" <rkc@yabba.dabba.do.rochester.rr.bomb> wrote in message
> news:VbVdc.34947$e17.12245@twister.nyroc.rr.com...[color=green]
> >
> > "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
> > news:r5Vdc.64990$Bk31.25272@twister01.bloor.is.net .cable.rogers.com...[color=darkred]
> > > Access cannot use ODBC to communicate between MDB files.[/color]
> >
> > Can you not use ADO from within an .mdb file to communicate with
> > another .mdb file via ODBC?[/color]
>
> If you're willing to do everything through VBA, then yes, you can do that
> (you can even query Access 2000 databases from Access 97 that way). That
> means, though, that you're severely limited in terms of what you can do in
> the Access front-end.[/color]

Well, the op only wanted to write data to the backend via ODBC so
ADO would be a solution. I guess not everything can be GUI driven
and automagical.

"Remco Groot Beumer" <info@rgbplus.nl> wrote in
news:c58dqe$e88$1@news3.tilbu1.nb.home.nl:
[color=blue]
> 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.[/color]

Plain old ODBC won't work. ADO might work, but on a WAN, I wouldn't
recommend it.

Replication is another option as it actually matches the problem
description your boss gave you. However, Jet replication is *not*
for the faint of heart -- it has a steep learning curve and for
synchronizing over a WAN, you'd definitely need to use indirect
replication, which requires the Developer Edition (which comes with
Replication Manager and the low-level tools for implementing
indirect replication).

Another option would be to simply export the data required, get it
to the remote machine and then let that machine import it.

BTW, the "mapped drive letter" issue is a read herring, as you don't
have to map a drive letter to get access to files on your network.
UNC paths (of the form \\ServerName\ShareName\File.mdb) work just
fine. But on a WAN, that would nonetheless be a disaster waiting to
happen (though it depends on the total bandwidth -- anything
approaching 10mbps that is reliable would probably be safe).

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

"rkc" <rkc@yabba.dabba.do.rochester.rr.bomb> wrote in message
news:VbVdc.34947$e17.12245@twister.nyroc.rr.com...[color=blue]
>
> "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
> news:r5Vdc.64990$Bk31.25272@twister01.bloor.is.net .cable.rogers.com...[color=green]
> > Access cannot use ODBC to communicate between MDB files.
> >
> > --
> > Doug Steele, Microsoft Access MVP
> > http://I.Am/DougSteele
> > (No private e-mails, please)[/color]
>
> Can you not use ADO from within an .mdb file to communicate with
> another .mdb file via ODBC?[/color]

I'm puzzled. How can I communicate from one .mdb to another .mdb via ODBC,
even using ADO?

"Randy Harris" <randy@SpamFree.com> wrote in message
news:YeZdc.160$TR2.27@newssvr15.news.prodigy.com.. .[color=blue]
> "rkc" <rkc@yabba.dabba.do.rochester.rr.bomb> wrote in message
> news:VbVdc.34947$e17.12245@twister.nyroc.rr.com...[color=green]
> >
> > "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
> > news:r5Vdc.64990$Bk31.25272@twister01.bloor.is.net .cable.rogers.com...[color=darkred]
> > > Access cannot use ODBC to communicate between MDB files.
> > >[/color]
> >
> > Can you not use ADO from within an .mdb file to communicate with
> > another .mdb file via ODBC?[/color]
>
> I'm puzzled. How can I communicate from one .mdb to another .mdb via[/color]
ODBC,[color=blue]
> even using ADO?[/color]

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.

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

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:JM0ec.71213$SQE.29148@news01.bloor.is.net.cab le.rogers.com...[color=blue]
> "Randy Harris" <randy@SpamFree.com> wrote in message
> news:YeZdc.160$TR2.27@newssvr15.news.prodigy.com.. .[color=green]
> > "rkc" <rkc@yabba.dabba.do.rochester.rr.bomb> wrote in message
> > news:VbVdc.34947$e17.12245@twister.nyroc.rr.com...[color=darkred]
> > >
> > > "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in[/color][/color][/color]
message[color=blue][color=green][color=darkred]
> > > news:r5Vdc.64990$Bk31.25272@twister01.bloor.is.net .cable.rogers.com...
> > > > Access cannot use ODBC to communicate between MDB files.
> > > >
> > >
> > > Can you not use ADO from within an .mdb file to communicate with
> > > another .mdb file via ODBC?[/color]
> >
> > I'm puzzled. How can I communicate from one .mdb to another .mdb via[/color]
> ODBC,[color=green]
> > even using ADO?[/color]
>
> 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.[/color]

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?

[color=blue]
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (No private e-mails, please)
>
>
>
>
>[/color]

"rkc" <rkc@yabba.dabba.do.rochester.rr.bomb> wrote in message
news:VbVdc.34947$e17.12245@twister.nyroc.rr.com...[color=blue]
>
> "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
> news:r5Vdc.64990$Bk31.25272@twister01.bloor.is.net .cable.rogers.com...[color=green]
> > Access cannot use ODBC to communicate between MDB files.
> >
> > --
> > Doug Steele, Microsoft Access MVP
> > http://I.Am/DougSteele
> > (No private e-mails, please)[/color]
>
> Can you not use ADO from within an .mdb file to communicate with
> another .mdb file via ODBC?
>[/color]

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\mydata.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...but 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
pleasenonoSpamKallal@msn.com
http://www.attcanada.net/~kallal.msn

"Randy Harris" <randy@SpamFree.com> wrote in message
news:tk1ec.208$6G4.140@newssvr15.news.prodigy.com. ..[color=blue]
>[color=green][color=darkred]
> > > I'm puzzled. How can I communicate from one .mdb to another .mdb via
> > > ODBC. even using ADO?[/color]
> >
> > 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[/color][/color]
database[color=blue][color=green]
> > from Access 97. It is, however, quite limited in terms of what you can[/color][/color]
do.[color=blue]
>
>
> 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[/color]
than[color=blue]
> by use of a web server?[/color]

See Albert's explanation.

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

Albert, thanks very much for the clarification.

Randy

"Albert D. Kallal" <pleasenonosspammkallal@msn.com> wrote in message
news:7rpec.84631$Ig.68131@pd7tw2no...[color=blue]
> "rkc" <rkc@yabba.dabba.do.rochester.rr.bomb> wrote in message
> news:VbVdc.34947$e17.12245@twister.nyroc.rr.com...[color=green]
> >
> > "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
> > news:r5Vdc.64990$Bk31.25272@twister01.bloor.is.net .cable.rogers.com...[color=darkred]
> > > Access cannot use ODBC to communicate between MDB files.
> > >
> > > --
> > > Doug Steele, Microsoft Access MVP
> > > http://I.Am/DougSteele
> > > (No private e-mails, please)[/color]
> >
> > Can you not use ADO from within an .mdb file to communicate with
> > another .mdb file via ODBC?
> >[/color]
>
>
> Yes, you can setup and use a odbc connection string. However, you are NOT
> communicating across the network using odbc. What happens is the[/color]
following:[color=blue]
>
>
> ADO---->ADO ODBC DRIVER---> DAO JET engine -->jet opens file
> \\servername\mydata.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[/color]
ONLY[color=blue]
> 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...but 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[/color]
opened[color=blue]
> ACROSS the network. There is NO performance advantage to this over a[/color]
simple[color=blue]
> 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
> pleasenonoSpamKallal@msn.com
> http://www.attcanada.net/~kallal.msn
>
>[/color]

"Remco Groot Beumer" wrote
[color=blue]
> Is it possible to create a ODBC on the
> server and link it on the front end side?[/color]

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