Recover a deleted ODBC connection to an external SQL db table (1 Viewer)

[Robert Shields]

I run an Access database (on Microsoft 365 Access installed locally) that connects to 16 tables in an external database on a SQL Server. The latter is hosted by Microsoft Azure.
My ODBC Driver is the SQL Native Client 11.0, and I think that I made the connections with a DNS-less connection string. (Forgive me - it was some years ago!)
It was all working pretty well and the connections to all the external tables were quick, dependable and solid.
Recently I used the 'Linked Table Manager' in Access to delete the connection to one of the tables.
I now want to restore the connection, just as it was before, and using the same connection method as all the other tables.
Help!
The Linked Table Manager no longer showed the table whose connection that I deleted. I suppose I should have anticipated this!
I have tried to recover the table by setting up a new ODBC connection using External Data...New Data Source... etc, and have succeeded - but the new connection is not as fast as the others. I now get an annoying error message on my Access front-end, calling for 'SQL Server Login' - which is not necessary.
The Linked Table Manager now shows that I have two "Data Sources". The first Data Source includes the 15 tables that I linked up many years ago, whereas the second Data Source shows only the table that I have recently recovered.
Is there somewhere where I can manipulate the connection strings - and thus re-create the connection to my recovered table so that it's similar to all the others, using only the one data source?
Eagerly looking forward to some expert advice...
Robert.

 

[Isaac]

Does your company support Right click on folder > Properties > "Previous Versions" tab? If so that has saved my behind more times than I can count, you could recover yesterday's version of the whole file and go from there

 

[isladogs]

You can directly edit the connection string in the linked table manager. It will only take a few seconds to do. Then refresh the link

 

[GPGeorge]

You can directly edit the connection string in the linked table manager. It will only take a few seconds to do. Then refresh the link

Along the lines of what Colin said, you could copy the "good" connection string in the LTM and paste it over the "slow" one.

 

[Isaac]

Oh, I thought what you wanted didn't exist any more.
Definitely, if it does just copy it..

 

[isladogs]

Along the lines of what Colin said, you could copy the "good" connection string in the LTM and paste it over the "slow" one.

Along the lines of...but much better explained

 

[Robert Shields]

Hi All -
Many thanks for your suggestions. Yes - I could probably go back to a previous version, but I should have explained that my deletion of the ODBC connection to that one table was not accidental; it was deliberate. After making some changes to the backend table I had been experiencing some weird behaviour which I thought was due to some failure of Access to map the data types (nvarchar(50)) from the new fields in the SQL Server's table to the new Access fields (short text). I had tried refreshing the link several times but that did not resolve the problem. So in desperation I decided to delete the link and re-make it. This action does actually seem to have resolved the original problem - but has left me with another one!
I would so love to "directly edit the connection string in the linked table manager." as Isladogs recommends. How do I do that? My LTM doesn't seem to offer an editing facility.
All I can see is the tantalising vision of what looks like the connection string when I hover over the table name in the navigation bar (it shows like a tool-tip). But how can I edit it???

 

[isladogs]

I would so love to "directly edit the connection string in the linked table manager." as Isladogs recommends. How do I do that? My LTM doesn't seem to offer an editing facility.
All I can see is the tantalising vision of what looks like the connection string when I hover over the table name in the navigation bar (it shows like a tool-tip). But how can I edit it???

You cannot edit the connection string shown in the tooltip from the nav pane

Hopefully this screenshot is self explanatory. It is only possible using the new LTM supplied in recent versions of Access

For example, I've used it several times in the last few days to swop between the old SQL Server driver and the newer ODBC Driver 17 for SQL Server

 

[Robert Shields]

Hi isladogs - many thanks - that looks terrific - just what I want! But the Edit button is always grayed out in my LTM. I wonder if that's because I'm using the SQL Server Native Client 11.0 which seems to be deprecated now? Or could there be some other reason why I can't edit?

 

[isladogs]

It is only enabled when you tick a complete group of linked tables as shown in my screenshot

 

[KitaYama]

It is only enabled when you tick one group of linked tables as shown in my screenshot

@isladogs Not the OP But I think I had this conversation with you in some other thread too.
The Edit button is always grayed out. Maybe because I'm using DSNless connation?

 

[isladogs]

I also use DSN-less connections. That's not relevant here. Nor is the SQL driver used

Tick all the tables from a group and it should become enabled. You have only ticked one which won't work
The easiest way is to click the - sign to compact a group then tick the box so that all tables in that group are selected

If that doesn't work for you then perhaps an Office repair is needed or possibly your IT administrator may have applied a group policy to prevent users modifying links

 

[KitaYama]

I also use DSN-less connections. That's not relevant here. Nor is the SQL driver used

Tick all the tables from a group and it should become enabled. You have only ticked one which won't work
The easiest way is to click the - sign to compact a group then tick the box so that all tables in that group are selected

View attachment 111850

If that doesn't work for you then perhaps an Office repair is needed or possibly your IT administrator may have applied a group policy to prevent users modifying links

Selecting a group enables the Edit button.
Thanks

 

[Robert Shields]

Fantastic - all done! I can't thank you enough for the help you have given me - am really grateful! 'Ticking all the boxes' was, of course, the very simple answer! Then I could edit. Once I had copied the correct connection string and pasted it into the box for the recovered table, all the table links became grouped (as they should be) in one connection.
Just one warning to anyone else who might read this: Don't 'Refresh' the linked table until you have saved and closed the accdb file and re-opened it. Otherwise Access will Refresh the link with the old connection string, and you'll be back to square one!

 

[isladogs]

Good to see that both of you are now able to use the Edit feature of the new LTM.

I have found it invaluable e.g. for quickly swopping between development & production versions, testing different SQL Server drivers against the latest big and also for making linked Excel tables editable.
Its one of the most useful features introduced inj recent years.