none
[Microsoft] [ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed.

    Question

  • I have an excel (2003) macro that connects to a DB2 connection via ODBC:

    Dim db As ADODB.Connection
    Dim rs As ADODB.Recordset
    Set db = New ADODB.Connection
    Set rs = New ADODB.Recordset
    Set db = New Connection

    db.CursorLocation = adUseClient
    db.Properties("Prompt") = adPromptCompleteRequired
    db.CommandTimeout = 240
    db.ConnectionTimeout = 240
    db.Open Range("BASE2")

    Set rs = New Recordset
    rs.Open sql, db

    But when I execute this code I keep getting an error message: [Microsoft] [ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed.

    I am running a Windows 7 (64 bits), IBM DB2 (v9). I have also configured the system DNS to use a 32 bits driver (in order to avoid the architecture mismatch error). And I have added my USER to the SQLLIB folder permission with full control option.

    Even with all this things set, I keep getting this error message. Anyone know how to solve this problem? I am out of options here.

    Thanks in advance.

    Friday, October 28, 2011 3:08 PM

Answers

  • I don't know what is being used as the connection string.  the website below says not to use ODBC (has known bug) but instead use oldb.  You are setting a new connection which is using a default connection string.  You can see what the connection string is by adding a watch for the variable db and looking at the connection property.

    http://community.discountasp.net/showthread.php?t=7014

     

    You also have two connection string (see below)

    Set db = New ADODB.Connection
    Set rs = New ADODB.Recordset
    Set db = New Connection               ****** Remove this line

     

    If deleting the one line doesn't work then I would changing the open statement.

    From : db.Open ConnectionString:=Range("BASE2")

    To : db.Open ConnectionString:="Provider=SQLOLEDB;Data Source=" & Range("BASE2")

    Or this

    To : db.Open ConnectionString:="Provider=SQLOLEDB;Data Source=Range(""BASE2"")"




    jdweng
    Thursday, November 03, 2011 7:06 PM

All replies

  • You probably don't have any connection.  Where did you find the statement below?

    db.Open Range("BASE2")

    I have never seen a connection open string like this before.  Is there any examples or websites you have been using?

    You also having set sql to any valid command string

    rs.Open sql, db

    sql needs to contain at minimum the following

    sql = "Select * from MyTable"


    jdweng
    Friday, October 28, 2011 3:53 PM
  • Thanks for the reply

    But this is not the problems really, this logic works on Windows XP, the error appeared when the OS was changed to Windows 7.

    Just to clarify: This excel I am using have a series of drop downs that a user can select. Depending on the information selected on these drop down lists a different sql is executed under a certain DB2 Connection.

    db.Open Range("BASE2")

    BASE2: is a defined name range that stores the connection name (the same name defined on the ODBC under system DNS and the same name for the connection set on DB2).

    rs.Open sql, db

    sql: is a variable that contains the sql depending on the settings.

    example:

    sql = Range("MYQUERY2").Comment.Text

    sql = Replace(sql, "{Owner}", Range("Owner"))
    sql = Replace(sql, "{Year}", Range("Year"))

    From some other forum I have been reading, it seems this error is related to Windows permissions. But none of the solutions (to guarantee enough permission level for the SQLLIB folder (DB2 folder)) seems to be working on my case.

    I mean, I am able to run queries and pull information. It is just that it is not working when I am trying to do it from this excel tool. I am wondering if could be a driver version issue, but which driver I do not know.

    Friday, October 28, 2011 4:15 PM
  • The microsoft jet engine is used to as the drive for opening databases including excel. I can't tell if the problem is with the connection or the sql statement.  I would make sure the connection is valid by add the variable db to the watch window by highlighting the variable with the mouse and then right click and select Add Watch. 

     

    The step through the code and make sure the connection is valid by checking the db variable properties in the watch window and check for errors.  Next I would do the same with the sql variable and the rs variable.  Make sure the SQL statement is correct.

     


    jdweng
    Friday, October 28, 2011 7:18 PM
  • The microsoft jet engine is used to as the drive for opening databases including excel. I can't tell if the problem is with the connection or the sql statement.  I would make sure the connection is valid by add the variable db to the watch window by highlighting the variable with the mouse and then right click and select Add Watch. 

     

    The step through the code and make sure the connection is valid by checking the db variable properties in the watch window and check for errors.  Next I would do the same with the sql variable and the rs variable.  Make sure the SQL statement is correct.

     


    jdweng

    I did the Add Watch and double checked the SQL statement. Nothing is wrong with th SQL. Also, nothing I have read regading this specific error message leads me to conclude that the problem was in the SQL Statement.

    Definitely this is a permission issue, I am just not sure which folder I am forgetting to add the correct permission.  

    Thursday, November 03, 2011 4:11 PM
  • I don't know what is being used as the connection string.  the website below says not to use ODBC (has known bug) but instead use oldb.  You are setting a new connection which is using a default connection string.  You can see what the connection string is by adding a watch for the variable db and looking at the connection property.

    http://community.discountasp.net/showthread.php?t=7014

     

    You also have two connection string (see below)

    Set db = New ADODB.Connection
    Set rs = New ADODB.Recordset
    Set db = New Connection               ****** Remove this line

     

    If deleting the one line doesn't work then I would changing the open statement.

    From : db.Open ConnectionString:=Range("BASE2")

    To : db.Open ConnectionString:="Provider=SQLOLEDB;Data Source=" & Range("BASE2")

    Or this

    To : db.Open ConnectionString:="Provider=SQLOLEDB;Data Source=Range(""BASE2"")"




    jdweng
    Thursday, November 03, 2011 7:06 PM
  • Not sure if you are able to resolve this already. I just had similar problem, and when I use 'run as administrator' this is working fine.
    Wednesday, December 12, 2012 4:59 AM