none
ODBC Fails in 64 bit environment - Access MDB

    Question

  • Posting here because I saw other ODBC issues referred here.  Please help a poor scripter...

    The short version...  Scripts fail to connect to MDB databases; same scripts that have worked well for a long time on onther systems.

    This has been bugging me for a while and I've been working around it; but now I need to fix it..

    I think I have narrowed it down to a 32/64 driver issue.

    Sypmptom: Error attempting to create connection to Access (MDB) database.

    Error message: "Microsoft OLE DB Provider for ODBC Drivers: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified"

    Environment: Windows7 64 bit.  Office 2010(64 bit) installed.  Only version of office ever installed on this system.  No other ODBC drivers installed.

    Note: Running from the (64 bit) Command Prompt generates the error above.  Running as 32 Bit seems to work.  If I run the script(s) within my editor (VbsEdit, installed as 32 bit app) everything works.  Same problem with many scripts that have run well on 32 bit systems.

    ODBC Drivers installed:
    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.

    I get same/similar error trying to connect to Access with PowerShell. (Thats what I really need to fix, but I think issue is the environment, and I'm more comfortable with VB Script...

    Sample code follows...................................................

    Set objConnectionG = CreateObject("ADODB.Connection")
    strConnectionG = "Driver={Microsoft Access Driver (*.mdb)}; " & _
                     "Dbq=DB1.mdb; DefaultDir=.\; Uid=Admin; " & _
                     "Pwd='';"
    WScript.Echo strConnectionG
    objConnectionG.Open strConnectionG
    objConnectionG.Close

    • Moved by KJian_ Thursday, October 14, 2010 1:42 AM (From:SQL Server Data Access)
    Wednesday, October 13, 2010 5:33 PM

Answers

  • "CBS3" wrote in message news:a1576cbe-653e-445c-bc33-cf80ad268a75@communitybridge.codeplex.com...

    Posting here because I saw other ODBC issues referred here.  Please help a poor scripter...

    Set objConnectionG = CreateObject("ADODB.Connection")
    strConnectionG = "Driver={Microsoft Access Driver (*.mdb)}; " & _
                     "Dbq=DB1.mdb; DefaultDir=.\; Uid=Admin; " & _
                     "Pwd='';"
    WScript.Echo strConnectionG
    objConnectionG.Open strConnectionG
    objConnectionG.Close

    The problem is that the above conneciton string don't look right. The 64 bit driver shows something like:
     
    Microsoft Access Driver (*.mdb, *.accdb)
     
    I while my win7 box is 64, I am running 32 office.
     
    I can only note that the JET (now called ACE) for 64 bits looks like:
     
    �??Dim acc2007       As DAO.DBEngine
    dim acc2007       As object        ' late bind
    Set acc2007 = CreateObject("DAO.DBEngine.120")
     
     
    Set acc2007 = CreateObject("DAO.DBEngine.120")
    acc2007.OpenDataBase("c:\myDb.accDB")
     
    The above is dao, but I just wondering if your provider string is wrong.
     
    I used the above in windows scripting, but I not used ADO.
     
    So, windows scripting can read the db such as:
     
     
    Set dbeng = CreateObject("DAO.DBEngine.36")
    strMdbFile = "C:\Documents and Settings\Albert\My Documents\Access\ScriptExample\MultiSelect.mdb"
    Set db = dbeng.OpenDatabase(strMdbFile)
    strQuery = "select * from contacts"
    Set rs = db.OpenRecordset(strQuery)
    rs.movefirst
    If rs.EOF = true Then
       quit
    End If
     
    strTextOut = "C:\t5.txt"
    set fs = Wscript.CreateObject("Scripting.FileSystemObject")
    Set ts = fs.OpenTextFile(strTextOut, 2, True)
    2 = write, 1 = read
     
    do while rs.EOF = false
       strOutText = rs("LastName")
       ts.Writeline strOutText
       rs.movenext
    loop
    ts.close
    rs.close
     
    I do have a 64 bit server here, but I don't want to mess with it just yet installing the 64 ACE on that box.
     
    Unfortunately, the above is not a lot of help to you (sorry. And, I am in over my head on this question).
    My best guess however is that your connection string needs to be changed somewhat.
     
    --
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada
    Pleasenospam_kallal@msn.com
    • Marked as answer by Bessie Zhao Thursday, October 21, 2010 9:45 AM
    Thursday, October 14, 2010 6:11 AM