Connection string works on server, not in DW!

Report · Feb 07, 2011

Hi!

I am trying to connect to an excel sheet with a named range. I have tried every possible connection string there is. Sometimes the connection works on the server, and sometime it works in Dreamweaver CS5. But never at both server and DW. Right now I am using:

"Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\inetpub\wwwroot\www\malaif\db\tabeller.xls;" & "Extended Properties=""Excel 8.0;HDR=Yes;"";"

And the page shows up fine in IIS 7.5. In DW when i test the connection in Database/Custom Connection String (Using driver on testing server) I get "Format of the initialization string does not conform to the OLE DB specification".

Environment: IIS 7.5 on Win 7 64 bit. (defaultapp pool configured to 32bit) Page is asp VBScript.

I hope someone is kind enough to give me a hand with this. This is driving me nuts!

Greetings!

/Richard

Report · Feb 07, 2011

Hi again!

Went through a couple of strings again... This one:

"Driver={Microsoft Excel Driver (*.xls)};" & "DriverId=790;" & "Dbq=C:\inetpub\wwwroot\www\malaif\db\tabeller.xls;"

Works on the server, in DW it says "Connection was made successfully. BUT, it doesn't show any tables. When I try to creata a recordset it says "Unable to retrieve tables from this connection, click on the 'Define...' button to test this connection." Wich off course does not work...

Any ideas?

Thanks in advance!

/Richard

Report · Feb 07, 2011

Yet another string, yet another error:

"DBQ=" & Server.MapPath("..\db\tabeller.xls") & ";DRIVER={Microsoft Excel Driver (*.xls)};driverid=790"

Works on server, throws this when testing connection in DW:

-------------------------------

[Microsoft][ODBC Excel Driver] '(unknown)' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.

[Microsoft][ODBC Excel Driver] General Warning Unable to open registry key 'Temporary (volatile) Jet DSN process 0xa8 Thread 0x7e4 DBC 0x2985d4c Excel'.

[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed

----------------------------------

Could it be a problem between 64 vs 32 bit? Even though I set the server to Enable 32bit application= true...

/Richard