none
How to set 32 bit ODBC driver on 64 bit server + MS access 2000, MS Access Drivers 14.00.4760.1000,

    Question

  • Facing below issue --> How to set 32 bit ODBC driver on 64 bit serve

    Connection is successfult but not getting tables in ODI , throwing below erroe -

    ODI Model reverse engineering Error-

    a>Standard Reverse Engineering:
    Error :
    java.sql.SQLException: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
    at oracle.odi.jdbc.datasource.LoginTimeoutDatasourceAdapter.doGetConnection(LoginTimeoutDatasourceAdapter.java:133)

    b>Customize Reverse Engineering: Custom reverse engineering is not supported by ODBC driver
    Error:
    org.apache.bsf.BSFException: exception from Jython:
    Traceback (innermost last):
    File "<string>", line 20, in ?
    File "<string>", line 78, in startReverse
    File "<string>", line 127, in _odiReverse__gettables
    File "<string>", line 210, in _odiReverse__getkeys
    java.sql.SQLException: [Microsoft][ODBC Driver Manager] Driver does not support this function

    Generated code by ODI is

    srcCx = odiRef.getJDBCConnection("SRC")
    srcCatalog= ''
    srcSchema =''

    trgCx = odiRef.getJDBCConnection("DEST")
    imod = 30001
    tabpat = [ '%', 'ST', '' ]

    cols = 1
    pks = 1
    fks = 1
    logfile = r'C:\Documents and Settings\backupsa2\reverse.log'
    uselog = 1


    #
    # START THE REVERSE
    #
    rev = odiReverse(imod, tabpat, srcCx, srcCatalog,srcSchema, trgCx, uselog, logfile)
    rev.startReverse(getcolumns=cols, getkeys=pks, getfks=fks)
    rev.endReverse()

    Monday, December 19, 2011 1:09 PM

Answers

All replies

  • Use the version of odbcad32.exe that's in SysWOW64, not the one that's in System32
    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)
    Monday, December 19, 2011 1:37 PM
  • Do I need to first de-install the existing ODBC and then will have to run C:\WINDOWS\SysWOW64\odbcad32.exe? Will this install WIN32 ODBC in system? I have heard that "

    Using the ODBC Data Source Administrator from the system's Administrative Tools folder (64 bit version) shows the "Microsoft Access Driver (*.mdb, *.accdb)" as version 14.00.4760.1000.
    Running OdbcAd32.exe, from the SysWOW64 folder (32 bit version) shows the "Microsoft Access Driver (*.mdb)" as version 6.01.7600.16385.

     Is this true?

    I can see the current ODBC configuration from property as "%SystemRoot%\system32\odbcad32.exe". Is this 64 bit ODBC driver?

     

    • Edited by shilpadhote Tuesday, December 20, 2011 5:32 AM
    Tuesday, December 20, 2011 5:30 AM
  • What do you mean by "the existing ODCB"? If you're talking about whatever DSN you may have set up in an attempt to use an Access database, yes, delete it. If you're talking about any shortcut(s) you might have on your desktop (or your Start menu), no, don't delete it: you may well need both.

    Yes, "%SystemRoot%\system32\odbcad32.exe" is the shortcut to the 64 bit client.


    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)
    Tuesday, December 20, 2011 12:20 PM
  • The driver MS Access Drivers 14.00.4760.1000 is already installed on my client's machine. I have mentioned the OS, MS access, driver info in the subject line of thread. but this driver is not supporting the reverse engineering of model in ODI. The connection is successful in ODI but proble in reverse engineering. After saching a lot, I got information that 64 bit server withthe above ODBC driver is not supported fully. So I wanted to install C:\WINDOWS\SysWOW64\odbcad32.exe.

    The already install ODBC shows "%SystemRoot%\system32\odbcad32.exe" if I right click the existing ODBC/Properties

    I have posted the thread on oracle ODI form also. So you can get details from below link.

    https://forums.oracle.com/forums/thread.jspa?threadID=2322111

    Please let me know if you can suggest/guide us to resolve this issue. Other solution that I got from PDF to code in Jython for driver - http://docs.oracle.com/cd/E14571_01/integrate.1111/e12643.pdf 

     

     

    Tuesday, December 20, 2011 2:06 PM
  • As far as I'm aware, there is no 64-bit version of the MS Access drivers, so you must use the 32-bit version. (In fact, if you look at http://technet.microsoft.com/en-us/library/ee681792.aspx you'll see that Microsoft doesn't actually recommend using the x64 version of Access!)

    As I said earlier, to do that, simply invoke "%SystemRoot%\SysWOW64\odbcad32.exe" instead of "%SystemRoot%\system32\odbcad32.exe"

    To make it easier, why not set up a short-cut to the other client, so that you have both available? That's what we do for all of our x64 machines.


    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)
    Tuesday, December 20, 2011 2:37 PM
  • We do not want to use x64 version but it is already installed on client's remote machine( my machine) and we are trying to access MSAccess 2000 from ODI using clients machine(my machine). Because of License issue, client is not ready to install MS access application on the same machine(my machine)

    Can you please let me know the version of ODBC installed on my client machine with the below information-

    "The already install ODBC shows "%SystemRoot%\system32\odbcad32.exe" if I right click the existing ODBC/Properties"

    The information that I got from google-

    "

    Using the ODBC Data Source Administrator from the system's Administrative Tools folder (64 bit version) shows the "Microsoft Access Driver (*.mdb, *.accdb)" as version 14.00.4760.1000.
    Running OdbcAd32.exe, from the SysWOW64 folder (32 bit version) shows the "Microsoft Access Driver (*.mdb)" as version 6.01.7600.16385.
    My conclusion is that this code doesn't work with the version 14.x driver in 64 bit command prompt, but does work with version 6.x version in 32 bit environment.

    "

    Wednesday, December 21, 2011 5:38 AM
  • There's no such thing as "the version of ODBC installed on my client machine". When you use ODBC, you're using the ODBC Data Source Administrator client (odbcad32.exe) to interact with what ever ODBC drivers have been installed on the machine. With an x64 architecture operating system, some of the installed drivers will be x64, others will be x86.

    To interact with a Jet database, you need to create a DSN using the x86 ODBC Data Source Administrator client (the one accessible through "%SystemRoot%\SysWOW64\odbcad32.exe". To interact with other data source, you may need the x86 client, or you may need the x64 client: it depends on the drivers with which you're interacting.


    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)
    Wednesday, December 21, 2011 11:59 AM
  • We do not know your version of the Server OS but sounds like you're using the latest version that is 64-bit.

    As Doug indicates, you should be using the older version in the SysWoW64 folder.

    The default ODBC Administrator GUI in your OS is x64. It does not have an interface that separate the 64-bit and 32-bit version of drivers. In other words, you'll have to create a x86 version as Doug's suggestion.

    The "system32" folder is in fact the x64 version of drivers which has done a lot of confusion for some people. Some of them still thinks that system32 is still 32-bit version, perhaps they were looking for "system64" (does not exist yet) which in my opinion a big mistake on MS part.

    Thursday, December 22, 2011 1:44 AM
  • It's misleading to refer to the version in SysWOW64 as an "older version". It's not: it's a 32 bit version, required when the ODBC version is only 32 bit.
    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)
    Thursday, December 22, 2011 2:06 AM
  • Doug,

    Your statement

    "To interact with a Jet database, you need to create a DSN using the x86 ODBC Data Source Administrator client (the one accessible through "%SystemRoot%\SysWOW64\odbcad32.exe". To interact with other data source, you may need the x86 client, or you may need the x64 client: it depends on the drivers with which you're interacting."

     

    My Answer : I am using SYSstem32 version -The already install ODBC shows "%SystemRoot%\system32\odbcad32.exe" if I right click the existing ODBC/Properties

    My Queries-

    1> We have copied only MDB MS access file locally and trying to retrieve object details  through ODI. Is only MDB file sufficient to retrieve the objects in MS access DB? I am aware of JET DB. How to check JET DB relationshipt with MS acccess. i.e whether MS access belong to it or not?How to create   create a x86 version  of ODBC?

    2> Which latest highest  version of driver supports  MS ACCESS 2000 for os  OS 'windows server 2003 R2 Enterprise x64 edition'?

    3> Other than ODBC driver which drivers or communication way for MS access 2000?

    4> Why 'MS Access Drivers 14.00.4760.1000' driver giving me the issue in reverse engineerng? What other latest version of driver I can use? Please let me know the link of suggested drivers

    Thanks In advance

     

    With Regards,

    Shilpa

    Thursday, December 22, 2011 12:21 PM
  • If you're using the version of the ODBC Data Source Administrator at "%SystemRoot%\system32\odbcad32.exe", you're using the x64 version. As I've said numerous times in this thread, the x86 version is found in "%SystemRoot%\SysWOW64\odbcad32.exe". Yes, it's somewhat confusing, but in x64 operating systems, the x64 versions of programs are found in "%SystemRoot%\system32\" and the x86 versions are found in "%SystemRoot%\SysWOW64\"

    Have you tried building a DSN using the x86 version of the ODBC Data Source Administrator, found in "%SystemRoot%\SysWOW32\odbcad32.exe", as I've suggested you try several times already?


    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)
    Thursday, December 22, 2011 12:51 PM
  • It's misleading to refer to the version in SysWOW64 as an "older version". It's not: it's a 32 bit version, required when the ODBC version is only 32 bit.

    I prefered that as a layman's term for simplicity for such users as I find through my experiences with them, even that it's not entirely true in any sense to you.

    There are people still regarding 32-bit as old and 64-bit as new. So in a sense, it would be true to them.

    Yes, it would be misleading to us but to a typical user, would they know the differences?

    Friday, December 23, 2011 1:18 AM
  • There are people still regarding 32-bit as old and 64-bit as new. So in a sense, it would be true to them.

    Yes, it would be misleading to us but to a typical user, would they know the differences?

    Given that the whole point of this thread is trying to convince the OP to use the 32 bit client, despite the fact that they're running a 64 bit OS, it serves no purpose to designate them as "old" and "new".

    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)
    • Marked as answer by shilpadhote Monday, December 26, 2011 6:23 AM
    Friday, December 23, 2011 1:39 AM
  • Hello Douglas/AccessVandal,

    We got success on ODBC set up of  x86 versions are found in "%SystemRoot%\SysWOW64\". At leaset we are able to reverse engineere the model. There are still issue but this ODBC has solve our issue to reverse engineer locally. We are facing issue if we set up the same on ODI agent .

     

    Thanks for the information.

     

    With Thanks,

    Shilpa Dhote

     

     

    Monday, December 26, 2011 6:27 AM