locked
Microsoft Access "ODBC failure to connect" RRS feed

  • Question

  • A Windows 10 64-Bit Machine was used to to rebuild a database using Access (32-Bit) and SQL Server Management Studio .  The Windows 10 machine had a fresh install of Office (64-Bit) following being backed up.  The 6 Access files that exist from the backup have mixed results.  3 can be opened and 3 issue an error message ODBC--connection to 'dsnFalconSQL32' failed and an inability to be opened in Datasheet view.  By right-click on table or form it is able to be opened but not edited in Design View.  The most important Access file that provides the error messages has all the tables, forms  and shows a file size of 2660 KB.  When the file is selected an Access file is shown with the same name in the same folder with a padlock and .laccdb as the extension instead of the original .accdb without a padlock.  Wondering if there is a way to identify the source file and search the backup for it or can the contents available in Design View be associated or exported to a new db source/file?  I've seen support articles that identified being able to click on a linked table and delete resulting in the table without the link.  When I tried this the table was deleted.


    • Edited by Damian F19 Thursday, February 21, 2019 5:40 PM
    Thursday, February 21, 2019 1:35 AM

All replies

  • Ok, this much sounds like you simply need to re-link your tables to the running instance of SQL server.

    No question that moving to a new computer or a re-build/re-install of the operating system will require you re-link your tables.

    So that message about “dsnfalonSQL32” is simply connection information to the database that your Access application was connecting to.

    In other words with the information you shared so far your data is not part of Access but resides in some kind of server database. (That dsnFalconSQL32 database).

    So you have to restore and setup that falconSQL32 database. It is not clear if this data system was running on that previous computer or this is some kind of corporate/company database system that the Access application was connecting to.

    The laccdb file is always created of the same name when you open an acccDB file. This file or issue is of ZERO concern to you. Even if your Access system has NO local tables or no data such locking files are always created when you open an Access application.

    It just not clear if you dealing with an Access application (no data but JUST an application written in Access without ANY local data tables. (the data is from an external system like dsnFalconSQL32).

    Or common is the application has a mix of local tables and external data tables. So deleting links to external systems does not delete data but only the link.

    However deleting tables without a link are thus deleting actual data.

    I often even with SQL (external systems) in Access we see they have some local data tables.

    These local tables “usually” are just for scratch data. However for a working application you cannot delete EITHER type of table or you will risk breaking and preventing such applications from working.

    You do not want to delete local tables and you do NOT want to delete linked tables. For those linked tables I suggest launching the linked table manager and re-linking those tables.

    You can’t start deleting bits and parts of the application and blowing away parts that are going to break and damage such an application.

    What you want to do is look at the list of tables – are they linked tables pointing to some external database system (like that dsnFalconSQL32 database).

    >>a way to identify the source file

    Usually you can “hover” your cursor over a linked table and it will show the connection information. You can also hit ctrl-g to jump to the debug window and type in:

    ? currentdb.tabledefs("name of table").Connection

    The above will thus then display the path name to if the linked table was to an accDB file (likely not if a DSN is involved).

    Here is what typical Access icons look like:

    So pay close attention to the kind of tables.

    However, it really don’t matter here.

    You might well have deleted a link and NOT deleted valuable data but your actions can still cause the application to break.

    If the Access application had all tables as linked then the Access system was ONLY an application that you setup + install on each workstation and does not and did not contain any data.

    Deleting a linked table might not delete some data but it is very likely to break and damage and prevent an application from working correctly.

    Holding (hovering) your cursor over the table link will display the information. and the above command from the debug window will also display this information.

    Regards,

    Albert D. Kallal (Access MVP 2003-2017)

    Edmonton, Alberta Canada

    Monday, February 25, 2019 3:39 AM
  • Albert,

    Thank you very much.  I don't recall receiving a message that there was a response.  The icons available on my local machine show the External link to SQL Server you list above.  Nothing is accessible that matches the dsnFalconSQL32 database.  the link table manager looks like the resource I will be using this evening.  Thanks Again.

    Tuesday, August 27, 2019 1:55 AM
  • Hello Albert D. Kallal (Access MVP 2003-2017)
    Thanks again for your response.  The SQL server instance previously used is no longer running but the 2 exported .sql files  "Falcon.sql" and "Falconstage.sql" are available. Tables and Columns were entered into Access by team and SQL work was performed by PM.  Merged data was viewable in Access.  The 2 .sql files have been installed on a new SQL server instance and opened in SQL Server Management Studio.  the Access file has connected to data using SSMS and the ODBC connector.  Some of the content has been restored but there are errors in the SSMS that I'm not certain how to resolve.

    The list of tables in Access (when hovering) shows they are linked to tables with same name in the .sql files

    NAME = N'Falcon', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\falcon.mdf' , SIZE = 139264KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
     LOG ON 
    ( NAME = N'Falcon_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\Falcon_log.ldf' , SIZE = 139264KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
    GO



    ON  PRIMARY 
    ( NAME = N'FalconStage', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\FalconStage.mdf' , SIZE = 3547136KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
     LOG ON 
    ( NAME = N'FalconStage_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\FalconStage_log.ldf' , SIZE = 1974272KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
    GO
    ctrl-g ? currentdb.tabledefs("name of table").Connection
    Thursday, August 29, 2019 4:47 PM
  • Just wondering why you are using 64bit office. Normally you would use 32bit.

    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Friday, August 30, 2019 1:40 AM
  • If you had a DSN that you were using i am pretty sure that you are pointing to the 32bit version of the ODBC driver..So rebuild the DSN pointing to the correct 64bit ODBC driver of the SQL Server.
    Friday, August 30, 2019 8:41 AM
  • I would use Access to launch the ODBC manger and create a new FILE dsn to re-link the tables.

    By creating a FILE dsn, then Access will convert such links to DSN-less, and as a result you will not have to re-configure or deal with a DSN from that point on.

    So, by using a FILE dsn, then you can copy the application to a different computer, and you not have to setup + create a DSN, since access will have used what is a called a DSN-less link.

    So, I high recommend you re-link the table(s) and you create the new DSN (a FILE dsn) from scratch. Having change computers, and also having changed bit size of office, you have far more luck forcing access to re-create the DSN for you.

    If the FILE dsn works, then from that point on, access does not need or use the DSN you setup – it is ignored, and can even be deleted. So, key takeaway here is creating a FILE dsn will result in Access converting and saving the DSN information inside of the linked table. At that point, you not require setting up of a DSN on any additional computer(s) that run/use the application.

    Even if for some reason you decide to not use a FILE dsn, I still strong recommend you re-create the DSN from inside of access, since new computer(s), and bit size etc. means a very high chance that locations/registry settings are different.

    Regards,

    Albert D. Kallal (Access MVP 2003-2017)

    Edmonton, Alberta Canada

    Friday, August 30, 2019 8:27 PM