none
SQL database connection works in Excel 2003 fails in Excel 2007 RRS feed

  • Question

  • I'm facing really big problem with one computer on which excel 2007 is working. I'have done a lot of reports retrieving data from MS SQL 7.0 to excel 2003 without any problem. Usually Im doing the adodb connection and then getting the recordset from the database. On Excel 2007 any of these reports is working - Im getting error 80004005 when trying to connect to the database. I have noticed also that

    - In administarative tools Im able to define DSN connction and the test passes.

    - In excel 2007 Im able to retrieve the data only by MS query

    and this is the only way I can get the data from this server to Excel 2007.

    I suspect it can be a problem with some ODBC driver version or generally with the way Excel is trying to access this database

    But I have no idea how to check the version, how to put the right version if it is possible.. 

    Im afraid it is the problem with the old style database but it should be the way to catch this data.

    I would very appreciate for any hint.

    Friday, November 25, 2011 2:56 PM

All replies

  • What connection string are you using. If still using the same as for 2003 see here

    http://www.connectionstrings.com/excel-2007
    http://www.connectionstrings.com/excel

    Peter Thornton

    Friday, November 25, 2011 3:27 PM
    Moderator
  • Thank you for the advise although:

    Im trying to connect to MS SQL 7.0 database, and I have tried all the strings from connection strings.

    I can  get the data using function - Existing connections  or MS query

    but when Im trying to make the connection from inside Excel 2007 - other sources - OLEDB, SQL server connection  it does not work.

    But in fact the main problem is that the VBA code gives an error (althougrh in excel 2003 is working excelent) - I have done many custom reports and functions using ADODB.Connection so it is really important to have it working on Excel 2007 too.

    My code looks as following:

        Set CNN = CreateObject("ADODB.Connection")
        Set rst = CreateObject("ADODB.Recordset")
        CNN.Open "DRIVER=SQL Server;SERVER=192.168.1.2;UID=a;PWD=a;DATABASE=MAX102PLfor1"

    The highlighted row gives an error when debugging.

    As I have just checked - it does not want to connect to newer sql server either, so it is not the problem with SQL 7.0 

    Pls help.

    Tuesday, November 29, 2011 3:32 PM
  • More strange effect is that when using existong connection I chose saved query  or query in this worksheet I can get the data and the connection is working , but while I chose ODBC definition ( which in other places is working) then the result is

    Cannot connect to the data source. Access denied. There is mistake in enetring ID or pass or you have insufficient rights

    This function looks like taking the connection string from ODBC definition, displays it and asks for the user and pass

    and thats why it fails ( but whyyyyyyy?)

     

    I really cannot understand how to make it working .

    Thank you for possible help.

    Tuesday, November 29, 2011 5:30 PM
  • Afraid I don't have any more suggestions as I can't replicate the scenario, hopefully someone else might jump in!

    Peter Thornton

    Wednesday, November 30, 2011 3:14 PM
    Moderator