This discussion is archived
5 Replies Latest reply: Apr 3, 2012 3:08 AM by orafad RSS

ODBC, 32bit Windows 7 and VBA

844050 Newbie
Currently Being Moderated
I've got a VBA script that works fine with any v10 of the Oracle client on Windows XP.

However, on Windows 7 (Professional, 32 bit, no UAC, running as Administrator), it hangs and locks up the calling program at the objConnection.Close part of the script (after it's successfully connected and inserted the data).

I've tried it with 10.2.0.1, 10.2.0.5 and 11.2.0.1.0

I don't particularly want to keep deploying new PCs with a 10 year old OS on them, but I can't give out more Windows 7 boxes until I get this fixed.


Does anyone have any ideas? Has anyone seen this before?


Thanks,


Gareth
  • 1. Re: ODBC, 32bit Windows 7 and VBA
    696513 Newbie
    Currently Being Moderated
    Hi there (unusual user ID you have there...),

    Ok, I support a number of MS based legacy 32 bit applications that need to work on Win 7. It was tricky at first. I wish that someone would find a place to post the basic instructions. I have a word document that describes the process in detail (which I gave to our IT department), but will summarize here.

    1) I recommend going with Oracle 11g - it works fine on both the XP and Win 7 systems. Yes, I had to get a special approval from our IT, but I simply told them it was this or not having a bunch of staff able to work....

    2) Remember that you need to install the 32 bit ODBC drivers if you want to run a 32 bit application - even if the computer is running Win 7 64bit. Also, note that some applications can run in both modes and, if running in 32 bit mode, may require the 32 bit drivers.

    3) When you install the 32 bit drivers, use the custom option and check sqlPlus, Oracle ODBC Driver, and Oracle Net (the last so you can configure TNSNames)

    4) After you install the 32 bit drivers, the ODBC Administrator that Oracle shows in its menu is the same as you get from the other Windows menus - BUT IT ONLY WORKS WITH 64 BIT DRIVERS.... So you need to create a shortcut and point it to C:\Windows\SysWOW64\odbcad32.exe - which is the 32 Bit version of the ODBC Administrator. There may be some other way to get to it, but I don't know how. Also, there is no easy way to tell if you are running the 32 or 64 bit version other than by looking at where the exe is located. Also, I have no idea why the 32 bit software is in SysWOW64 and the 64 bit software is in System32 (you would think the numbers would be reversed) - so don't ask.

    5) If you have installed the ODBC drivers and can't see them when you run the ODBC Administrator, run the other version in case you accidently installed the wrong drivers. They only show up in the ODBC Admin that is the same bit as the drivers.

    In my case, after I did this, then all my MS based apps ran just as well on Win 7 or Win XP.

    One last thought. If you accidently posted to this forum but do not use ODBC, there are some other tricks I used as well to get my connections to Oracle working (in this case with my .NET apps.)
  • 2. Re: ODBC, 32bit Windows 7 and VBA
    844050 Newbie
    Currently Being Moderated
    Whoops. I forgot about this thread!

    I don't remember getting a choice of user id. Strange, but there you go.

    When you recommend Oracle 11g - is that for the database itself? We're using something old like Oracle 8 or 9, and so many things connect to it that it will never get upgraded. This may be the problem.

    I'm on Windows 7 32-bit. Fortunately there's no crazy 64-bit machines that I need to get working on this.

    Unfortunately, I've got the ODBC working in Windows to the point where I can read the tsnames.ora file and successfully do a test connection to the database. It's only when running the VBA script that the program hangs.


    I'm at the point now where I'm about to take away people's nice shiny new Windows 7 machines and give them XP ones.
  • 3. Re: ODBC, 32bit Windows 7 and VBA
    gdarling - oracle Expert
    Currently Being Moderated
    I haven't heard of that one before.

    Out of curiosity, where are you executing the script?

    Do you reproduce the same if you do something as simple as the following, executed as .vbs script from a dos prompt?
    set con = createobject("adodb.connection")
    con.open "dsn=orcl;uid=scott;pwd=tiger"
    con.execute "insert into onecol values('hello')"
    con.close
    set con = nothing
    wscript.echo "done"
    I'd probably start by using adplus or debugdiag and getting a hang dump, to see what it's hung on. If you open a SR with support we can give you a hand looking into that.

    Greg
  • 4. Re: ODBC, 32bit Windows 7 and VBA
    928106 Newbie
    Currently Being Moderated
    Oracle 9i client is not running on Windows 7 professional 64 bit OS , Will it work on Windows 7 professional 32 bit OS .
  • 5. Re: ODBC, 32bit Windows 7 and VBA
    orafad Oracle ACE
    Currently Being Moderated
    Please avoid hijacking unrelated old threads. Instead start a new thread for your specific issue and setup.

    The earliest version supported on Windows 7 is 10.2.0.5, which requires a patch set install.

    I would suggest using a current release 11.2 version for new installations, over the age old "9i" Client.

    Look into using Instant Client for easy of install/deployment.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points