none
ODBC queries from VS 2012 aren't working RRS feed

  • Question

  • I have an Access database that has a few tables linked to SQL Server Express 2008 server (different computer) thru ODBC. 

    I've migrated the client computer to a new computer - the old client computer works fine with the same code, but the new one doesn't - both are Windows 7 64-bit and both have SQL Server 2008 Standard installed.

    The only difference between the old and new computers is one has Access 2010 installed and the new one has Access 2013 installed.

    If I run a SQL query that retrieves data from the linked tables from within Access 2013, it runs fine. But, when running it from my VS 2012 VB code (Windows-form project), it fails with: 

               ODBC--connection to '(my odbc db)' failed

    The connection string to the Access MDB is simply: 

    Provider=Microsoft.Jet.OLEDB.4.0;Data Source=(my db path);User Id=admin;Password=;"

    This is driving me nuts and it's a serious production issue at this point.

    Anyone have an idea what's going on???

    (FWIW, I have a "hello-world" app with the one query retrieving data from a native jet table and another retrieving data from a linked table - the first succeeds; the latter fails - so I know I'm getting to the Access database.)

    thanks,



    Bert Sirkin


    • Edited by BertSirkin Saturday, May 11, 2013 12:15 AM
    Friday, May 10, 2013 11:43 PM

Answers

All replies

  • Hi Sirkin,

    Please change it to 

    Provider=Microsoft.ACE.Oledb.14.0;Data Source=Database1.accdb;

    I hope this will be helpful.

    Best regards,


    Mike Feng
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, May 13, 2013 9:46 AM
    Moderator
  • Unfortunately, I now get this error when trying to connect to the db:

    An OLE DB Provider was not specified in the ConnectionString.  An example would be, 'Provider=SQLOLEDB;'.


    Bert Sirkin

    Monday, May 13, 2013 12:28 PM
  • Hi Bert,

    Would you like to try this code:

    System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection(@"PROVIDER=Microsoft.ACE.OLEDB.14.0;Data source=c:\xtreme.mdb;User Id=admin;Password=;");

    Best regards,


    Mike Feng
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, May 14, 2013 8:51 AM
    Moderator
  • Hi Mike,

    That "provider" isn't registered on my computer. I get this error with that connection string:

              The 'Microsoft.ACE.OLEDB.14.0' provider is not registered on the local machine.

    I had installed the 64-bit version of the "Microsoft Access Database Engine 2010 Redistributable", and even re-installed it, but neither the "Microsoft.ACE.OLEDB.14.0" or Microsoft.ACE.OLEDB.12.0" providers seem to be registered.

    Going thru the registry, I do find classids for Microsoft.ACE.OLEDB.10.0, 12.0 and 15.0 in HKey_Classes_Root, but according to Visual Studio, none are registered.

    I tried installing the 32-bit version of the "Microsoft Access Database Engine 2010 Redistributable", but I couldn't as I have Office 64-bit versions installed.

    Any idea how I can get these versions registered??

    thanks,

    Bert


    Bert Sirkin

    Tuesday, May 14, 2013 10:52 AM
  • Hi Bert,

    If so, please change the provider from 14.0 to 15.0, and change the platform of your project to x64.

    Best regards,


    Mike Feng
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by BertSirkin Tuesday, May 14, 2013 12:42 PM
    Tuesday, May 14, 2013 11:49 AM
    Moderator
  • Hi Mike,

    I set the provider in the connection string to: Microsoft.ACE.OLEDB.15.0 and the compile processor to x64 and it worked. Thanks!

     

    I wasn't aware that it was possible to call the Jet Engine from x64.

    BUT... that creates another problem - I was calling the "CompactDatabase" method of the JRO class. The JRO class is no longer available with the 64-bit provider. I guess I can get around that - getting to my data was the critical piece!

    thanks!

    Bert


    Bert Sirkin

    Tuesday, May 14, 2013 12:42 PM