Refreshing SQL-Linked Table in VBA

CPGDeveloper

Board Regular
Joined
Oct 8, 2008
Messages
154
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.
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

jackd

Well-known Member
Joined
Oct 19, 2006
Messages
1,307
Office Version
  1. 365
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

Board Regular
Joined
Oct 8, 2008
Messages
154
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

Well-known Member
Joined
Oct 19, 2006
Messages
1,307
Office Version
  1. 365
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

Board Regular
Joined
Oct 8, 2008
Messages
154

ADVERTISEMENT

Absolutely -- thanks for your time and help.
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,037
Office Version
  1. 365
Platform
  1. Windows
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?
 
L

Legacy 456155

Guest
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.

VBA Code:
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
 
Solution
L

Legacy 456155

Guest
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. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,023
Messages
5,622,268
Members
415,888
Latest member
Ifeanyi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top