I'm having difficulty understanding how ODBC works with Access 2003. I've
created a database file and it works fine. However, I want to put it up on
the internet where the security is still there and anyone who has
authorization could access the database off of the webserver. I'm very new
to
odbc and the step involved to implementing this database on my website.
There is two types of database systems. "server based systems" and "file
based systems"
Server bases systems are systems that accept requests. So sql server, or
Oracle are these types of systems. What happens is the client (via odbc)
sends the command to the server, the server then processes this request,
looks for the data, and then sends back the record. Obviously to send a
complex sql command to retrieve a record takes some software RUNNING ON THE
SERVER to accomplish this.
In the case of ms-access, we are talking about a file share. This file is
just like Word, or Excel. You place the file on the hard disk, and then open
it with ms-access. So, ms-access is a file share system. That means you can
put mdb file on a computer next to your desk. You can then share the folder
on this computer. Now, go back to your computer, browse via network
neighborhood, and open the file. Note that you did not install ANY SOFTWARE
on the machine, but JUST PUT a REGULAR file on that computer. This file is
opened via the regular file->open in ms-access. Up to this point, we have
NOT used ODBC here.
So, if you place the mdb file on your web server, you STILL MUST be able to
OPEN the file from YOUR COMPUTER. That means you MUST be able to go
file->open....
When you use a ODBC connection with JET (jet is the data engine that opens
mdb files), you ALWAYS use a FULL PATH NAME to the mdb file.
So, ODBC with ms-access/jet works like:
Your program ->ODBC -->JET->jet opens file (eg: c:\my docuemnts\data.mdb)
Note that this means you might be using VB, or FoxPro on your computer, but
the odbc connection STILL requires that you MUST have JET on your computer,
and just MUST BE ABLE TO OPEN the file like ANY REGULAR file. Remember er,
ms-access is a FILE based system, and just like word, or excel, you can't
pull data out of Excel just because you placed the file on the web server.
So, it is important to realize that when you use odbc with ms-access, you
are in fact making a odbc connection to JET, and JET in turn MUST be able to
open the file name like any windows regular file. (look close at any JET
odbc connection string, and you will see a REGULAR windows file path name to
the back end....so, really, using ODBC gains NOTHING in terms of
connectively here (well, ok, except that other software on your pc that
supports odbc can now read mdb data..but that is all you gain here!).
With a server based system, you get the following:
Your program --->ODBC --- sql request of data -----> SQL server on web site
(sql server process the sql, opens the data file, reads the record, and then
RETURNS the record to the odbc client). Note how the data processing, and
opening of the file occurs on the SERVER side. All we need is a odbc socket
connection to the web server, but we do NOT actually open the file on the
client side.
So, with a odbc connection to a mdb file, you STILL MUST be able to open the
file like any windows regular file. And, if you look close at the odbc
connection string for a JET mdb file, the path name of where the file
resides is always a standard windows file path name.
So, you really can't connect to the mdb via odbc, but in fact must use JET
to make the connection. And, JET thus must be installed on YOUR pc, not the
server side. (and the reverse is true for server based systems...you can
make a odbc request to a oracle database, but you most certainly do NOT have
to install oracle on your pc).
I suppose if you used a VPN (virtual private network), then you CAN open
files across the internet, as a VPN does allow file sharing across the
internet. However, this again would not be odbc, and this again would be far
to slow.
You could however write some code that runs on the web server that accepts
requests from a client running on the pc. Then the web server could open the
mdb file, read some data..and send it back. (in fact, that is what any asp
page that uses ms-access mdb file does anyway.)
You can read about how to use ms-access on a "wan" (wide area network) here:
http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html