none
ODBC Connection with Microsoft Access 2010

    Question

  • I have an MS Access 2010 Front End APP in multiple sites, all site have the same structure but different Back-End Database Name. I need to create a new ODBC Connection with a a generic Name. This allow me to update the Front End App without the need to select a Back End Database.

    The Proble is that I can the ODBC Connection with .MDB Database but not with .ACCDB Database.  When I try to connect I receive the Following error:

    You Cannot use ODBC to Import, export to or link an external Microsoft Access or ISAM database table to your database.

    Any suggestion.

     

    Thanks...

     

    Thursday, January 26, 2012 11:32 AM

Answers

All replies

  • I'm shocked you're able to use ODBC with .MDB files: I was sure that wasn't possible either!

    See Relink Access tables from code at "The Access Web" for how to point to different tables. While it only talk about .MDB files, it applies to .ACCDB files as well (you'll have to change the filter in fGetMDBName). Note that you must also include the GetOpenFileName code.


    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, January 26, 2012 12:51 PM
  • Edwin Vazquez1 wrote:

    The Proble is that I can the ODBC Connection with .MDB Database but not
    with .ACCDB Database.  When I try to connect I receive the Following
    error:

    You Cannot use ODBC to Import, export to or link an external Microsoft
    Access or ISAM database table to your database.

    Well, you should get the exact same message with your .mdb. I'm more than
    surprised that you're able to connect. Can you give us more details on that
    please?


    Peter Doering [MVP Access]

    Thursday, January 26, 2012 3:47 PM
  • Thanks ...

    Is right I made ​​a mistake. I check the ODBC connection and the connection that I refer as .MDB is and MYSQL Database and  was pointing to an MSQL server.
    I read this article and I use this routine on my Front-End App.
    I have over 50 installation on Remote sites and I use an FTP routine to transfer the Back-End Database to my server. All the database are different names but same structure. We are made some change to the Fron-End Program. I need to update all the Front- End Installation and Connect to their Databases. I use on my site a Demo Database but when I make an Update the Client need to Change the Database demo to the Real Back End Database manualy.
    My question is who I can make this update Unattended. to the client without the need to Select the Database on their site. That the reason to use odbc but doesn't work with MS Access Back-End Database.
    Thanks...


    Thursday, January 26, 2012 4:12 PM
  • You need to replace this section of code in fRefreshLinks

        strMsg = "Do you wish to specify a different path for the Access Tables?"
        
        If MsgBox(strMsg, vbQuestion + vbYesNo, "Alternate data source...") = vbYes Then
            strNewPath = fGetMDBName("Please select a new datasource")
        Else
            strNewPath = vbNullString
        End If
    
    
    

     


    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 Bruce Song Friday, February 03, 2012 2:16 AM
    Thursday, January 26, 2012 4:19 PM
  • HI Doug,

    I received the same error message. Where is the fRefreshLinks code located?

    Thanks for the help

    Friday, September 14, 2012 4:03 PM
  • Folllow the hyperlink in my first response.


    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)

    Friday, September 14, 2012 4:27 PM
  • Thanks, very helpful

    Friday, September 14, 2012 6:43 PM