asked on

ODBC Connection Failed - Moved to new server

I've moved our intranet from Windows 2000 Server to Windows Server 2003 on another box. I've setup the ODBC connections identical as before, but can't get one connection to work.

Dim rs
Set rs = Server.CreateObject("ADODB.recordset")
Set conn = Server.CreateObject("ADODB.Connection")
conn.open "Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\Inetpub\wwwroot\updated\database\test.mdb"

#LINE 86
rs.open "SELECT BLAH BLAH BLAH", "DSN=REPORTS"

Open in new window

The test.mdb has the REPORTS DSN and it has linked tables to an oracle database on DSN PNU.

Here is the error I get:
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Microsoft Access Driver] ODBC--connection to 'PNU' failed.
/updated/index.asp, line 86

I know the linked tables are working, because when you open the access DB it brings in data fine on the linked tables.

Got any ideas why it's not working from ASP?

eli411

1. Open Command prompt on your new server.
2. cd %systemdrive%\Inetpub\AdminScripts or go to "AdminScripts" folder under "Inetpub" folder
3. Execute following command- cscript.exe adsutil.vbs set W#SVC/AppPools/Enable32BitAppOnWin64 1

and see if that will fixed the problem.
The other way you can fix it without the screen is download the latest ODBC driver and re-install and recreate connection.

eli411

change "W#SVC/AppPools/Enable32BitAppOnWin64 1" to "W3SVC/AppPools/Enable32BitAppOnWin64 1" - Sorry Typo!

bschwarting

ASKER

This is a 32bit server. Does that matter with your script above?

bschwarting

ASKER

@eli411 any other thoughts?

Dave Baldwin

Have you made sure that C:\Inetpub\wwwroot\updated\database\test.mdb is writeable by the web server user? I think on Server 2003 it is still IUSR....

Jim Dettman (EE MVE)

OK, I'm stupid when it comes to ASP, but I know the Access side<g>

Usually problems with DSN's lately when moving are 32/64 bit issues; is that at play here?

Was the old server 32 bit and the new 64?

Jim.

bschwarting

ASKER

sorry for the delay, yeah we went from 32bit to 64bit. so how do I fix that?

bschwarting

ASKER

Scratch that, I thought we went to 64 bit but we didn't.

Jim Dettman (EE MVE)

OK. If there are no 32/64 bit issues, then next thing to check is the driver. Is the driver used in your connection string:

{Microsoft Access Driver (*.mdb, *.accdb)}

listed in the ODBC Manager in control panel under the drivers tab?

If so, then next step would be to create a system DSN entry for your database. Then from the ODBC Manager, can you perform a compact on that DB?

If yes, then everything ODBC wise checks out and it's down to security or the connection string.

Jim.

bschwarting

ASKER

Yes, everything is in the ODBC manager correctly.

Jim Dettman (EE MVE)

<<Yes, everything is in the ODBC manager correctly. >>

Sorry for the delay in getting back to you.

Did you try a repair or compact from the ODBC manager on the DB? If so, what happened?

Jim.

bschwarting

ASKER

When i hit compact, it kept popping backup up like it wanted a new name.

UPDATE: sorry, I didn't read it all. i ran the compact just fine now.

Jim Dettman (EE MVE)

OK, so if you can run a compact from ODBC, then the DSN is OK.

That means either security or connection string.

So next, change the directory security to Everyone with full control for a test. See if your software can connect. If so, then you know what the problem is.

If not, then it has to be the connection string.

Jim.

bschwarting

ASKER

ok, must be the connection string, because it has everyone with full control.

Jim Dettman (EE MVE)

Can you supply a screen shot of the drivers tab in ODBC Manager?

Jim.

ASKER CERTIFIED SOLUTION

Jim Dettman (EE MVE)

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

bschwarting

ASKER

Thanks for all the help, I still can't get this to connect. I'm going to have to pick this project up again later and start from scratch.