Refreshing SQL-Linked Table in VBA

[CPGDeveloper]

Hello~

An application I'm working on has an MS Access Based Front End/Azure SQL Server Back End. Each end user has a copy of the .accde on their desktop.

Occasionally I have to add a field to a specific SQL table, and for that field to show up for the end user, I have to refresh the table in my development version and then re-distribute a new .accde front end. Is there a way to refresh that linked table, and have the new field accessible, through VBA?

Thanks in advance for your help.

 

[jackd]

You might find this video helpful re updating your front end. You update your "admin" copy as necessary; test it; convert to accde; then move it to server.
Each user has a version check routine at start up to see if their fe version is the latest; if yes then carry on; if not ,then download the latest version to their pc.

 

[CPGDeveloper]

Hi jackd, thanks -- I do use a version of that system that automatically updates each user to the latest version. I was just seeing if there was a way of avoiding creating a new version every time I added a field to a SQL-linked table.

 

[jackd]

Not that I am aware of. Consider that you are changing the structure of a table in the backend database to which the current Front end is linked. The front end has to be made aware of the latest structure and any related processing elements. To do that, the structure change has to be made and effected in the Back end, then the Front end has to be adjusted to use the new field(s) and then relinked to the modified Backend. You don't want to be modifying the back end database table structure while users are working with that data base.
Good luck with your project.

 

[CPGDeveloper]

Absolutely -- thanks for your time and help.

 

[Micron]

I find that answer interesting because I have zero experience with that type of back end. However, I have linked to Oracle db's before, and I am pretty sure that if they added a field to a table that I had linked to, it was already available in the front end. The only thing is, none of the forms/reports/queries would have been aware of it as they would all be designed according to the fields that they were created with (which would not include the newly added one). So I'm puzzled that the Azure be doesn't work that way. You're saying you would have to relink the table to see the new field?

 

[Legacy 456155]

Another option is to have a timestamp or counter in a server table and provide your front ends with the ability to adapt to certain changes on the server db. FESettings.RefreshLinks, for example.. The front ends will compare their local timestamp or counter with that of FESettings.RefreshLinks on the server BE and refresh accordingly, updating their local timestamp or counter after refreshing. You could even narrow it down to individual tables if you wish.

Sub RefreshLinks()
    Dim td As TableDef
    
    For Each td In CurrentDb.TableDefs
        If td.Attributes And dbAttachedODBC Or td.Attributes And dbAttachedTable Then td.RefreshLink
    Next
End Sub

 

[Legacy 456155]

So I'm puzzled that the Azure be doesn't work that way. You're saying you would have to relink the table to see the new field?

With a SQL Server BE, I must refresh the link whenever I add or remove columns. I'm not sure about other changes such as changing datatypes, constraints, or what have you. Is that the same as re-linking under the covers? I don't know. When I do make changes, sometimes I get an error and Access cannot read the data from the table. Other times, Access simply does not recognize the changes. A call to RefreshLink clears it up in either case.