{"id":184,"date":"2012-09-16T18:17:10","date_gmt":"2012-09-16T18:17:10","guid":{"rendered":"http:\/\/regina-whipp.com\/blog\/?p=184"},"modified":"2016-01-01T14:12:17","modified_gmt":"2016-01-01T19:12:17","slug":"update-database-front-ends","status":"publish","type":"post","link":"https:\/\/regina-whipp.com\/blog\/?p=184","title":{"rendered":"Update database front ends…"},"content":{"rendered":"

Everyone who has split a database needs a way to update the Frontends. There are a few choices out there…<\/p>\n

Tony\u2019s Auto FE Updater<\/a>
\nBTAB Development Front-End Auto-Update Enabling Tool<\/a>
\nAutomatically Deploy a New Access Client<\/a><\/p>\n

…and here\u2019s another one:
\nIn Backend (and this can be an SQL Server) create a table…
\ntblVersionControlMaster
\nvcmVersionControlID (PK, AutoNumber)
\nvcmVersion (Number, Double)<\/p>\n

In the Frontend where the forms, queries, etc. reside create table…
\ntblVersionControlLocal
\nvclVersionControlID (PK, AutoNumber)
\nvclVersion (Number, Double)<\/p>\n

Then create a link to the table in the Backend. It should look like this…<\/p>\n

\"\"<\/a>

If on a Networked Drive<\/p><\/div>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

\"\"<\/a>

If on an SQL Server<\/p><\/div>\n

Each table must have a matching PK, so, if tblVersionControlMaster has a PK of 1 then so must tblVersionControlLocal. That is needed for the UPDATE query to work. Once the tables are created, place a 1 on vcmVersion and vclVersion. That will become your starting number. If you want to start at another number that is fine, just make sure they are the same in both tables. You then need a folder on the Network, that everyone has the necessary permissions to, that will hold the updated Frontend.<\/p>\n

Now, we want to create a message to let the Users know what is going on. Open your database and go to Design Mode of your Main Menu (or Switchboard) and create a Label naming it lblVersionControl. Leave the Caption blank, if the Caption won\u2019t let you leave it blank, add a period, save and then go back and remove the period. I made my font color Red (#BA1419) but you can make yours whatever color you want. Next…<\/p>\n

…go to the VBA Editor behind the Main Menu (or Switchboard). At the top of the Form Module place…<\/p>\n

Private Sub Form_Current()<\/p>\n

\r\n'Optional\r\n'This is to put the Version in the Caption of the Main Menu or Switchboard\r\n'For more tips http:\/\/www.access-diva.com\/tips.html\r\nMe.Caption = "Main Menu version " & DLookup("vclVersion", "tblVersionControlLocal") & ".x"\r\n<\/pre>\n
End Sub<\/p>\n
Private Sub Form_Open(Cancel As Integer)<\/p>\n
\r\n'Because the Frontends will be sent via eMail and\/or ftp\r\n'you will want the one you send you UPDATE the tblVersionControlMaster\r\n'For more tips http:\/\/www.access-diva.com\/tips.html\r\n\r\nDim strSQL As String\r\n\r\nstrLocalVersion = DLookup("vclVersion", "tblVersionControlLocal")\r\nstrNetworkVersion = DLookup("vcmVersion", "tblVersionControlMaster")\r\n\r\nIf strLocalVersion= strNetworkVersion Then\r\n     strSQL = "UPDATE tblVersionControlLocal INNER JOIN tblVersionControlMaster " & _\r\n               "ON tblVersionControlLocal.vclVersionControlID = tblVersionControlMaster.vcmVersionControlID " & _\r\n                "SET tblVersionControlMaster.vcmVersion = [tblVersionControlLocal]![vclVersion]"\r\n              CurrentDb.Execute strSQL, dbFailOnError\r\nEnd If\r\n<\/pre>\n
End Sub<\/p>\n
Private Sub Form_Load()<\/p>\n
\r\n'This part that checks if there is a newer Frontend on the Network\r\n'If there is one, the message will display informing the User\r\n'For more tips http:\/\/www.access-diva.com\/tips.html\r\n\r\nstrLocalVersion = DLookup("vclVersion", "tblVersionControlLocal")\r\nstrNetworkVersion = DLookup("vcmVersion", "tblVersionControlMaster")End Sub\r\n\r\nIf strLocalVersion = strNetworkVersion Then\r\n     lblVersionControl.Caption = "Your version is out of date... EXIT, count to 10 and reopen!"\r\nElse\r\n     lblVersionControl.Caption = ""\r\nEnd If\r\n<\/pre>\n
End Sub<\/p>\n
Private Sub Form_Unload(Cancel As Integer)<\/p>\n
\r\n'This part calls the .CMD file that will actually do the copying\r\n'For more tips http:\/\/www.access-diva.com\/tips.html\r\n\r\nstrLocalVersion = DLookup("vclVersion", "tblVersionControlLocal")\r\nstrNetworkVersion = DLookup("vcmVersion", "tblVersionControlMaster")\r\n\r\nIf strLocalVersion = strNetworkVersion Then\r\n     Shell "c m d \/c ""C:\\Folder\\update.c m d"",vbHide"\r\n     '***Extra spaces in C M D, remove after copy\/paste***\r\nEnd If\r\n<\/pre>\n
End Sub<\/p>\n
Now to create the file that actually does the work, open Notepad and copy\/ paste…<\/p>\n
\r\nEcho Off\r\nping 1.1.1.1 -n 1 -w 2000\r\nCOPY \/Y "\\\\ServerName\\Folder\\YourNEWFrontEnd.accdb" "C:\\Folder\\YourOLDFrontEnd.accdb"\r\nREM START "Msaccess.exe" "C:\\Folder\\YourNowNEWFrontEnd.accdb"\r\n<\/pre>\n
Make sure you change the names to match your Server\\Path\\FrontEnd.accdb. Once you are done, save, naming it Update.cmd <\/strong>and place in the same folder as the Frontend, for everyone except your primary User.<\/p>\n
Now, you will need a primary User on the other end to send the updated file to, I generally pick a Manager or Supervisor. Send the file to them and tell them to open and close, making sure they detach from eMail and place in their Local folder first. This is done so that the UPDATE query in the On_Open event tells tblVersionControlMaster that there is a new version. Immediately<\/strong>, after closing, the file must be copied out to the folder on the Server… everyone else\u2019s Frontend will automatically update.<\/p>\n
Enjoy!<\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"excerpt":{"rendered":"
Everyone who has split a database needs a way to update the Frontends. There are a few choices out there…<\/p>\n
Tony\u2019s Auto FE Updater BTAB Development Front-End Auto-Update Enabling Tool Automatically Deploy a New Access Client<\/p>\n
…and here\u2019s another one: In Backend (and this can be an SQL Server) create a table… tblVersionControlMaster vcmVersionControlID (PK, […]<\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5,63],"tags":[64,18,19,21],"class_list":["post-184","post","type-post","status-publish","format-standard","hentry","category-access-tips","category-database-design","tag-access-tips","tag-database-design","tag-update-frontend","tag-vba","odd"],"_links":{"self":[{"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/184","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=184"}],"version-history":[{"count":36,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/184\/revisions"}],"predecessor-version":[{"id":1122,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/184\/revisions\/1122"}],"wp:attachment":[{"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=184"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=184"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=184"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}