#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    8
    Rep Power
    0

    Question Excel VBA General ODBC Error


    Long story short, we recently moved from Windows XP to Windows 7 (64bit) and therefore broke my app.

    I helped create a Excel VBA application a year ago and it worked fine until the upgrade this week. The app makes a connection to an Oracle database and retrieves certain data based on specific user input.

    When we were on XP we were using Oracle 10g, and upgraded with the Windows upgrade.

    Facts:

    I'm running
    Windows 7 Professional (64bit)
    Excel 2010 (reverted back to 32bit because of a plugin issue)
    I have both Oracle 11g 32bit and 64bit client on my pc.

    I'm getting a Run-time error '1004': General ODBC Error.

    Code:
    'Connection to database
    Public Function RunQuery(Des, query)
    UserId = Sheets("User").Cells(2, 16)
    Password = Sheets("User").Cells(2, 17)
    database = Sheets("User").Cells(2, 18)
        With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
            "ODBC;DRIVER={Oracle in oraclient11g_home1_32};SERVER=" & database & ";UID=" & UserId & ";PWD=" & Password & ";DBQ=" & database & ";DBA=W;APA=T;EXC=F;XSM=Default;FEN=T;Q" _
            ), Array( _
            "TO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=Me;CSR=F;FWC=F;FBS=60000;TLO=O;" _
            )), Destination:=Des)
            .CommandText = query
            .Name = rangeName & " from " & database
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = True
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlOverwriteCells
            .SavePassword = True
            .SaveData = True
            .AdjustColumnWidth = False
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .Refresh BackgroundQuery = False
        End With
    End Function
    Since none of the code changed, the only thing I can think of is that the Oracle Driver name changed. I've tried both the 32bit (oraclient11g_home1_32) and the 64bit (oraclient11g_home1) however with no luck.

    Any help offered is appreciated, I'm out of ideas.

    It's driving me insane!
    Last edited by eljefe_cendigit; July 19th, 2013 at 04:03 PM. Reason: Added more information
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    289
    Rep Power
    44
    64 bit systems will run both 32 bit and 64 bit software. The difference is that on 64 bit systems, all the library files and drivers in the "%SystemRoot%\system32\" directory are 64 bit. When you run 32 bit software, you are automatically directed to the "%SystemRoot%\syswow64\" directory. By default, when you access the ODBC manager, you are using the 64 bit manager. To use the 32 bit manager, you must specifically run %SystemRoot%\syswow64\odbcad32.exe. To make that change permanent, change the shortcut in Data Sources (ODBC).

    J.A Coutts
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2004
    Posts
    192
    Rep Power
    328
    Just in case if you missed it, did you define correct network address for your 64-bit driver? If this didn't help, you might want to try to change coding from ANSI to UNICODE or the other way around, one of my client had this problem when they changed to 64-bit driver/new OS.
    Last edited by anziga; July 22nd, 2013 at 06:56 PM.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    8
    Rep Power
    0
    Originally Posted by couttsj
    64 bit systems will run both 32 bit and 64 bit software. The difference is that on 64 bit systems, all the library files and drivers in the "%SystemRoot%\system32\" directory are 64 bit. When you run 32 bit software, you are automatically directed to the "%SystemRoot%\syswow64\" directory. By default, when you access the ODBC manager, you are using the 64 bit manager. To use the 32 bit manager, you must specifically run %SystemRoot%\syswow64\odbcad32.exe. To make that change permanent, change the shortcut in Data Sources (ODBC).

    J.A Coutts
    When I go to change the shortcut in Data Sources (ODBC). Am I doing that in the following place? Data > From Other Sources > From Data Connection Wizard? I can't seem to find the correct place to make a connection to the driver, do I need to put the full path in my code?
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    8
    Rep Power
    0
    Actually I got it, the systems team told me the wrong driver name, when I went out and looked it was slightly different. I replaced it and I'm back in business.

    Thanks!

IMN logo majestic logo threadwatch logo seochat tools logo