Helpful Information

Category: Database Management

What is ODBC?

If I sound confused I am. A friend of mine told me I can create a Access file and then upload it to my server, use an ODBC connection to work with it. We where discussing this in the light of using this method instead of using a text file when mysql is not available. What I don't understand is how you can connect to a Access file with out having a database. I thought an ODBC drive just allowed databases to use another database file. Can anyone enlighten me on this. He did it on a MicroSoft server using ASP, all I know is PHP.

odbc stands for open database connectivity. basically odbc is an interface for applications. think of it kind of like an api. drivers are implemented between your application and the database itself. when you are talking about asp, your friend probably just included a connection to the db on the remote server and accessed it that way. somebody probably set up the dsn on the remote server as well.

So I can't just place an Access or Filemaker database file in my site folder and then connect to it via ODBC? I need the server to be running some sort of database, right?

no, i apologize...it may have been misleading. dsn is data source name and it just makes things a bit easier, but you can do it without any dsn setup on the server side. look up some stuff on active server pages and it might help you out a bit. stardeveloper.com has some good stuff on this i think.

ODBC abstracts the database from it's physical location, and makes it available over a network. Basically, it makes it so you can work locally or remotely with a database without worrying about the details. It also abstracts the details about exactly which type of database you are working with (to a degree). Thus, you can build an application with Access, import the data into SQL server, and just re-assign your ODBC connection, and your system will work almost identically, although faster. The only information your application needs is the DSN (Data Source Name), which is a unique name you choose for every ODBC connection created.

The thing to remember is that, in order for a webserver to run an ODBC-connected database, there has to be a machine somewhere with the database engine itself. Here are a few different scenarios:

1. You have an dedicated SQL server machine, which hosts your database. Your webserver merely opens an ODBC connection with that server, and thus the database is available as if it was hosted on the webserver.

2. Your webserver has Access installed on it. You place a .MDB file anywhere on the server, and create a file-based ODBC connection, which your application can use just the same as any other ODBC connection.

3. You have one of the above two scenarios, and suddenly you want to move your database to another machine. You simply move the database, and change the ODBC connection details on your server, and your application continues working transparently, since it is still talking to the "same" ODBC connection on the server.

In all of these scenarios, your application never talks to the database directly. It just talks to the ODBC connection, and ODBC acts as the broker, making the actual "deal" with this or that database.

So, in answer to your original question, IF the webserver has Access installed on it, then you can do what you want. If the webserver is just running Windows NT and IIS, then it doesn't have the database engine to run Access directly. However, the server does have the ODBC engine itselt, so if you can find another machine with Access on it, then you can simply place your .MDB file on that machine, and open an ODBC connection between the server and that machine.

I suppose a theoretically possibility is to export your database as a self-running executable application, in which case it would contain the Access run-time libraries, and then it could be it's own self-contained database engine, and theoretically share an ODBC connection locally on the server. I have absolutely no experience messing with that, however.

WoW!

Thanks rycamor and tron for your great answers.

rycamor--
are you sure that your statement about having a dedicated SQL server is correct? i have set up a server with iis and did not have to install sql server.

I think there is still a misunderstanding here: I never said you need any kind of a database in order to run a server. What I am saying is, if you want a database to interact with your server, the database has to be processed somewhere, on a machine which actually has the database engine installed. That can be local (right on the server) or remote, but it needs to exist somewhere.

And the clarification I was trying to provide to Mark_php was: just because you have an Access .mdb file, that doesn't mean you can run your database on a server, unless the server or a host machine on the network has Access (or at least the Access run-time libraries) installed on it. I mean it's plain and simple: in order to work with a database, even through ODBC, you need the database engine to be installed somewhere.