I'm using Access 2007 and it creates files with an .accdb extension instead of .mdb. Can I still use these files with my ASP scripts?
To be honest... this question caught me a little off guard. I had not yet installed
Microsoft Office 2007 and had no idea that they had gone and changed the Access
database file format. After a little digging, I found that yes you can indeed use
the newer format with only minor changes to your existing code.
The first step in getting your scripts working with the new format is to install the
2007 Office System Driver: Data Connectivity Components
on your web server. This installs the components you'll need (ODBC and OLEDB drivers) in order to
read and write data to the new Office 2007 file formats.
The next step is to change the connection string you use to connect to your
Access database. If you've been using OLEDB to connect to your Access database
then your existing connection string will probably look something like this:
Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\path_to_database\database.mdb;
In addition to changing it to reference the new file, you'll also need to
change the Provider argument to "Microsoft.ACE.OLEDB.12.0".
The resulting connection string should look something like this:
Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\path_to_database\database.accdb;
That's all there is to it. I haven't played with too many examples of this since
most of my stuff has been moved to either SQL Server or SQL Express, but as far
as I can tell, you shouldn't need to make any other changes to your code.
I keep getting an error that tells me "ADO could not find the specified provider." What does this mean and how do I fix it?
Basically this error usually means that ADO can't find the data provider that knows how to
talk to the database you are trying to connect to. In order
for ADO to work with any database, it needs to have a database
provider that translates the ADO commands you execute to native
code for the database in question.
Providers for a database usually get installed when you install
the database itself. For example, if you want to use Access or Excel
as a data source
you probably won't have any problems if you already have Microsoft
Office installed. The same goes for SQL Server if you're running
it on your web server.
Where this can type of thing can be
more of a problem is when you are connecting to SQL Server remotely
and the SQL Server client machine (your web server) doesn't have an
appropriate provider installed. To deal with this situation, Microsoft has
packaged up the main data access components into a simple
redistributable package that will install everything you need to connect
to most Microsoft-based databases. It's called the
Microsoft Data Access Components (MDAC) and can be downloaded from
the Microsoft Universal Data Access Web Site
and installed on your web server.
Another situation when you're likely to see this error message
is when you're using a non-Microsoft database like Oracle or MySQL.
Generally the easiest way to find a provider for any of these is
to check the documentation in their website's support section.
Look for a FAQ page or search for ODBC or ADO and you'll almost
certainly come across a link to the appropriate download.
One last common cause of this type of error message is when you've
really confused ADO. When you've got something really wrong with your
database connection string sometimes ADO gets to the point where
it assumes you must be doing something through a provider it
simply doesn't understand and will throw this error. In this case,
try taking a look at our article
What's in an ADO Connection String?
to try and get things straightened out.
There's a reference to adovbs.inc in the code. What is it, why doesn't it work without it, and where can I get it?
When writing database related code in ASP, you're really using
ADO (ActiveX Data Objects) which is Microsoft's set of components
which provide programmatic database access. ADO defines a lot of
different constants which you can use to make code more readable
and easier to follow and debug. In order to use these constants
in VBScript you need to define them. This is where adovbs.inc
comes in.
The adovbs.inc file contains a list of the ADO constants and
their numerical equivilents and when included in your ASP code,
it defines them as constants so you have access to them in your
code. There are a lot of these constants and most of our code
is relatively simple database code so it only uses a few of
them, but some you might recognize are adOpenStatic,
adLockReadOnly, adCmdText, etc...
The reason the code doesn't work when you forget to include
the file is that the constants the code uses aren't defined.
The result is that incorrect values get passed to ADO and it
doesn't know what to do with them.
In terms of getting a copy, you probably already have one!
Do a search on your system for adovbs.inc and you'll almost
certainly find the copy that was installed with ADO. If you
can't find one you can get a copy of the one I'm currently using
for the samples from here.
Do I really need to do all this just to connect to my DB?
No, you can do a simple query with just a few lines of code! This is more of a generic framework and was done to illustrate the general usage of the different database objects. It is meant to be a sample and should not be mistaken for the only or even "best" way to accomplish what it does. It also includes the code that allows you to build your own queries. This adds some complexity and can be eliminated to simplify the process if you so desire.
When I run a query why doesn't the form default to the values I just used?
While it certainly could, and other samples on the site do this sort of thing, this particular sample was done more as an example of the database work. Since it was already getting long and involved, I decided to leave the form code free of ASP in an attempt to make it easier to understand.
Please note: This form is only for submitting questions about the sample for us to consider including in the FAQ. If we feel the question merits inclusion, we will include it along with a reply. We will not respond to your email individually.
