ODBC Connection Fails when I remove a Field from SQL Server Table

[ions]

Hello MS Access Expert,

I noticed that when I delete a field from the SQL Server table the ODBC connection subsequently breaks. Additionally, if I add fields to the SQL Server Table the ODBC connection does not break but the newly added fields do not show up.

The only solution that I have discovered for the above is to delete the ODBC link and recreate it which is time consuming. Is there another more efficient way to resolve this issue?

Thank you.

 

[pbaldy]

You should be able to right click on the table in the navigation pane and select "Refresh Link".

 

[June7]

Can maybe use VBA to refresh link.
Fairly common topic and more than one way to code.

 

[ions]

You should be able to right click on the table in the navigation pane and select "Refresh Link".

Thank you so simple.

For multiple ODBC links the code below works well but the code is basically deleting the table definition and recreating it without DSN in the connection string. I already have this code in the program so I will use it as a solution.

http://www.accessmvp.com/DJSteele/DSNLessLinks.html

Thank you again.

 

[pbaldy]

I use DSNLess myself, but during development it's easiest to just refresh the link of a table I've just made a change to so forms, etc will see it.

 

[ions]

I just learned that creating the ODBC connection via a DSN file makes the links DSNless. Why the need for the DSNless code that I referenced?

 

[pbaldy]

I'm not sure what you're saying. If you link via a DSN in the ODBC Data Source Administrator (User, System, or File) you're using a DSN. The code you linked to does not use one.

 

[ions]

When I connect using a DSN file the ODBC connection is the following without any reference to DSN on my machine:

1)

ODBC;DRIVER=ODBC Driver 17 for SQL Server;SERVER=DESKTOP-5FFFFFF;Trusted_Connection=Yes;APP=Microsoft Office;DATABASE=NewBrunswick;;TABLE=dbo.Employees

When I connect using User, the ODBC connection expects DSN on my machine:

2)

ODBC;DSN=NewBrunswick;Description=NewBrunswick;UID=jrwei;Trusted_Connection=Yes;APP=Microsoft Office;DATABASE=NewBrunswick;;TABLE=dbo.Employees

When I connect using DSNless VBA Code in the reference:

3)

ODBC;DRIVER=sql server;SERVER=DESKTOP-5FFFFFF;UID=jrwei;Trusted_Connection=Yes;APP=Microsoft Office;DATABASE=NewBrunswick;TABLE=dbo.Employees

Items 1) and 3) do not rely on a DSN but item 2 does unless I am not fully comprehending something.

Thanks

 

[Pat Hartman]

When the BE is Jet or ACE, Access automagically "sees" schema changes. That doesn't happen with SQL Server or other RDBMS'. For those, you MUST refresh the links whenever a schema change is made to the BE.

 

[ions]

Thanks Pat. How about my interpretation of table links being DSNless using a File DSN? Is my understanding correct.

Only User Machine Data includes the DSN in the connection string.

ODBC;DSN=NewBrunswick;Description=NewBrunswick;UID=jrwei;Trusted_Connection=Yes;APP=Microsoft Office;DATABASE=NewBrunswick;;TABLE=dbo.Employees

File DSN does not.

ODBC;DRIVER=ODBC Driver 17 for SQL Server;SERVER=DESKTOP-5FFFFFF;Trusted_Connection=Yes;APP=Microsoft Office;DATABASE=NewBrunswick;;TABLE=dbo.Employees

 

[Pat Hartman]

Doesn't matter whether the links use a DSN or not. Access still does not see schema changes automatically. So, you either need to always refresh the links when the FE opens or you open the master copy of the FE and refresh and then force the new FE to be distributed.

 

[GPGeorge]

Both connection string alternatives (DSN-Less or via a DSN) only tell Access where to go to get the data; neither tells Access anything about the data itself.

• You can take a bus to the downtown library.
• You can take an Uber to the downtown library.
• Neither the bus driver nor the Uber driver knows what books are actually inside that library.

 

[ions]

Pat and George,

I apologize for my late response and thank you for yours.

I understand that for Schema Changes in the SQL Server Backend the ODBC links need to be refreshed. My follow up question was why do we need DSNless VBA code, if when using a DSN file to create the ODBC links the links are already DSNless.

Why the need for the code in this link if a DSN file makes DSNless ODBC connections? Perhaps DSN files were not available when the article was written? http://www.accessmvp.com/DJSteele/DSNLessLinks.html

Thank you

***********

Only User Machine Data includes the DSN in the connection string.

ODBC;DSN=NewBrunswick;Description=NewBrunswick;UID=jrwei;Trusted_Connection=Yes;APP=Microsoft Office;DATABASE=NewBrunswick;;TABLE=dbo.Employees

File DSN does not.

ODBC;DRIVER=ODBC Driver 17 for SQL Server;SERVER=DESKTOP-5FFFFFF;Trusted_Connection=Yes;APP=Microsoft Office;DATABASE=NewBrunswick;;TABLE=dbo.Employees

Hence why the need for DSNless VBA code other than to

 

[sonic8]

why do we need DSNless VBA code, if when using a DSN file to create the ODBC links the links are already DSNless.

For a DSNless connection you set the connection string by other means than a DSN. No DSN is involved in the process at all!

If you use a File DSN, the File DSN has to exist and it is used to create the connection string of the linked table(s). So, this is not DSNless, even though the resulting linked table has the same connection string as with the DSNless approach.

 

[ions]

even though the resulting linked table has the same connection string as with the DSNless approach.

Yes that is what I wanted to confirm. If I use a DSN File to create the ODBC links than I can distribute the FE to another machine without the other machine having the DSN file.

Thank you

 

[GPGeorge]

Look at the connection string in the Linked Table Manager. Does it include a reference to a DSN or not?

 

[ions]

Look at the connection string in the Linked Table Manager. Does it include a reference to a DSN or not?

It doesn't George.

ODBC;DRIVER=ODBC Driver 17 for SQL Server;SERVER=DESKTOP-5FFFFFF;Trusted_Connection=Yes;APP=Microsoft Office;DATABASE=NewBrunswick;;TABLE=dbo.Employees

 

[GPGeorge]

It doesn't George.

ODBC;DRIVER=ODBC Driver 17 for SQL Server;SERVER=DESKTOP-5FFFFFF;Trusted_Connection=Yes;APP=Microsoft Office;DATABASE=NewBrunswick;;TABLE=dbo.Employees

So that should tell you the DSN is not required.

 

[Isaac]

And I think generally when linking to sql server views, the Access linking mechanism is much more picky.

 

[Pat Hartman]

And I think generally when linking to sql server views, the Access linking mechanism is much more picky.

No, not more picky. It is just that it is more obvious that tables need unique identifiers and so developers get sloppy with views and forget those need unique identifiers also or Access will not allow updates to them. The alternative is to create DDL queries to add pseudo indexes and run those queries when the tables are relinked.