ODBC connection string for ODBC 6.01

Hi there,

I am trying to configure an ASP IIS website on Windows Server 2008 R2, but I'm having problems with the ODBC connection string to properly connect to an Access 2000 database.  On the old Windows Server 2003 server where this website was previously hosted, I had the connection string in this format, but it doesn't seem to work on Server 2008.

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb

I checked and the ODBC version on 2008 is 6.01.  How can I change this connection string to properly connect to this database on this new server?

Thank you,
- Christian
Christian PalaciosSenior IT Systems AdministratorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Big MontyWeb Ninja at largeCommented:
have a look here to find the right connectionstring that works for you:

https://www.connectionstrings.com/microsoft-access-odbc-driver/

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Christian PalaciosSenior IT Systems AdministratorAuthor Commented:
Thank you.  Does it matter what version of ODBC is on the server?
Big MontyWeb Ninja at largeCommented:
you need to use the version thats on the server
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Christian PalaciosSenior IT Systems AdministratorAuthor Commented:
That's what I thought I did.  Here's what I have on the server, but it didn't work.

Provider=Microsoft.Jet.OLEDB.6.01;Data Source=C:\mydatabase.mdb

That's why I want to know if there is a different way to define it in Server 2008 R2.
Big MontyWeb Ninja at largeCommented:
whats the error you're getting?

i have to run out for a few hours but will have a look later on tonight.

one thing to check is to make sure your permissions are correct...make sure your INET_USR account has access to the folder where the db resides
Christian PalaciosSenior IT Systems AdministratorAuthor Commented:
Sorry for the delay.  I haven't had time at all to continue working on this.  I'll send an update as soon as I have time to try it out.
Christian PalaciosSenior IT Systems AdministratorAuthor Commented:
Hi again,

I have finally had some time to check on this.  I installed the 32bit version of Microsoft Access Database Engine 2010 so that I could install ACE OLEDB 12.0 and I could try that with our Access 2000 database.  According to this page, https://www.connectionstrings.com/ace-oledb-12-0/, I can use this to access this database, but I'm getting errors.  After checking the FailedReqLogFiles I keep getting the Module_Set_Response_Error_Status error "Internal Server Error".  Any suggestions?  Would upgrading the database to maybe 2010 help this issue??

Thanks!
Big MontyWeb Ninja at largeCommented:
can you post the code that creates your connection string? be sure to  take out any sensitive data before posting it.
Christian PalaciosSenior IT Systems AdministratorAuthor Commented:
I tried these two:
The first one uses a DSN connection and the second one references the DB directly.
<%
connectstring="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=ramarksheds"
connectstring="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=<path>\db.mdb"
%>

Anything on IIS that I need to ensure is set?  Since it's IIS 7.0, I'm not sure if there is something else missing there.

Thanks!
Big MontyWeb Ninja at largeCommented:
that looks correct, and no, there isn't anything you need to change in IIS.

what are the errors you're getting?
Christian PalaciosSenior IT Systems AdministratorAuthor Commented:
From IE I keep getting this:

An error occurred on the server when processing the URL.  Please contact the system administrator.

If you are the system administrator please click here to find out more about this error.

I click that link and it takes me to where I can learn about setting up IIS 7.0 with older Access databases.  I click the "Using Classic ASP with Microsoft Access Databases on IIS" and read up on what I have to do.  It says to use 32bit ODBC and to make changes to the Application Pool for that website.  Anything else I should look at?

Thanks!
Big MontyWeb Ninja at largeCommented:
that's a generic "friendly" error message, to see the actual error, go into IIS and double click the ASP icon. Look for the Debugging section, expand it and locate "send errors to the browser". Switch it over to TRUE and then run the page again.
Christian PalaciosSenior IT Systems AdministratorAuthor Commented:
Thanks!  I was curious how to get the right error.  It says:

Microsoft Access Database Engine error '80004005'
Unspecified Error
/page.asp line 89

That line is where I am setting the connection string:
rs.activeconnection = connectstring

Thanks!
Big MontyWeb Ninja at largeCommented:
ok so this looks like a permissions issue...

open up the permissions on the folder that contains your database, and make sure the IUSR account has read/write access as well as the account NETWORK SERVICES
Christian PalaciosSenior IT Systems AdministratorAuthor Commented:
Ok, just made the change, but I'm still getting the same "unspecified" error.
Big MontyWeb Ninja at largeCommented:
just noticed this, which may be the problem. the line of code here:

rs.activeconnection = connectstring

what is the variable connectstring? is it a connection object or just a string?

if it's a string, you'll need to create a connection object, then set up your command object to use the connection object.

see here for more details:

http://www.w3schools.com/asp/prop_comm_activeconn.asp
Christian PalaciosSenior IT Systems AdministratorAuthor Commented:
The connectstring variable is located in another file and I am referencing that file at the top of the page using an #include tag.  It's what I have set up in the old server where these files were copied from.
Big MontyWeb Ninja at largeCommented:
so it is a connection object then?

other than that, I'm not sure what else to try. Hopefully another expert can offer some ideas
Christian PalaciosSenior IT Systems AdministratorAuthor Commented:
Would upgrading the database to 2010 help?  What driver needs to be installed for it to work with Access 2010?
Big MontyWeb Ninja at largeCommented:
what application pool are you using to run your site?

Upgrading to 2011 won't help this particular issue, as it's a permissions thing.
Christian PalaciosSenior IT Systems AdministratorAuthor Commented:
I think I figured it out.  I may have installed the wrong Access Database Engine.  After installing the right one and configuring the application pool, I was able to connect to an old Access database using OLEDB 4.0.
Christian PalaciosSenior IT Systems AdministratorAuthor Commented:
Please close question.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows Server 2008

From novice to tech pro — start learning today.