Here are some ideas:
From a post by Albert D. Kallal (MVP):
Actually, the above is not 100% true. In the context of your
above example, yes you can't transfer data to a JET file share
on a web site via ODBC.
However, the hard and fast rule is not that you can't transfer
because ms-access is at both ends. The real answer lies in
HOW the connection is going to be made. For example there are
ODBC drivers for using FoxPro, or dbase files also, but
once again, you can't link via ODBC across the net to FoxPro.
So, the issue is not so much that both ends are ms-access, but if you are
using a file share system like JET, or a client to server based system.
In general, the hard and fast rule is:
Is there a software system running on the other side that accepts
ODBC (ie: it not enough to have ODBC, but you need a program
running that you CONNECT to). You can't connect to JET running
on another pc!
And, while we are at this, ms-access is not even installed
on that web site anyway...we are talking about JET.
The real issues is that ms-access is a file share. That means you have to be
able to "open" the file.
So, EVEN WHEN you use the JET odbc drivers, say via ADO (either the oleDB
connection, or EVEN the oleDB ODBC driver, you are simply talking
to a library). That library is just a big chunk of code that MUST BE ABLE TO
OPEN the file we want to use. So, really, using ODBC to open a JET mdb file
requires that you have JET, AND YOU MUST have physical access to that file.
In other words, you may be using a ODBC connection string, but you still
need
to be able to open/view the that file like any other on your pc. In fact,
since you do have physical access, then ODBC is not needed, nor is it really
much help! (well, I suppose it is help, since products like Excel, and
others can
use the ODBC standard. Thus, we have a universal exchange format!).
In theory, the above means that if you got the admin of that server to
create a vpn (virtual private network), then you would actually be able to
browse the files on that server via network nationhood on your local pc. By
the way, that is what a VPN lets you do! A VPN simply lets you share and
create a virtual network *across* the internet. All computers, printers
disk drives etc will thus appear as a cute little office network, but in
fact each pc can be anywhere on the internet. With the VPN, all the little
computers appear on your network. Now that you can browse to the files on
those other computers, then you are home free. The instant you can browse,
and SEE THE FILE in a folder, then you can open it with ms-access/jet (or
even just link the front end to the back end on that server). This
"opening" of that file can be you using ms-access, VB, or yes, even a ODBC
connection to the ado library (but, as mentioned, that library chunk of code
simply talks to JET which has to physically open the file via a standard
file open).
So, with a file share system you need physical access to the actual
mdb data file). You cannot open a JET file on a remote pc via odbc. Your
local pc via ODBC can ask JET to open that file, but then really, ODBC does
nothing in terms of improved connectivity for you!
With a true database server, you are not opening a file like JET does. In
fact, you NEVER open a file with a true database server. You only make a
network connection. So, you CAN connect to a database server via a socket
connection (a socket connection is simply a network pipe that functions
across a network, but you need some software running on the other end. That
software on the other end is of course the database server.
As it turns out, however, you do have a web server that can open, and talk
to the mdb file. Further, that web server CAN ACCEPT a connection! So,
really,the solution could be to setup some ftp transfer, transfer the data
as file to a LOCAL file on the web server. Then have the web server open
that
file, read the data, and then shove the data out into the JET mdb file
(since
now the web server DOES have physical access to the jet file share). You
could
even make a web page that accepts text data, or even sql commands, and then
have the web server run that sql on the mdb file. However, I think the best
soltion is to simply tranfer the data as a file to the web site, and then
have a small piece of code move that data into the mdb file.
I think now you can see how strange it is to ask why you can't connect to
to ms-word, or ms-excel on that server. The same applies ms-access. These
programs from office are just programs that open files on the hard disk, and
nothing more.
I mentioned all of the above, since then with a understanding of a file
share, vs a socket based system like sql server that *ACCEPTS* connections,
then you can more clearly see why ms-access on each end will not work!
