Check for newest DB version?

[Frothingslosh]

And I just responded to it.

 

[psyc0tic1]

And I just responded to it.

Thank you very much sir that worked perfectly.

A great addition to my database.

 

[psyc0tic1]

And I just responded to it.

Is it possible to call a custom form instead of the ugly built-in msgbox in the module?

I made a form that says the same things but is formatted to match the database styling and colors with a command button to simply close the form.

I have not tried to call a form from within a module before and the

DoCmd.OpenForm "frm_msgbx, acNormal, , , , acDialog"

in place of the

'Notify the user that the application will update.
          MsgBox "UPDATE REQUIRED" & vbCrLf & vbCrLf & _
          "Your program is not the latest version." & vbCrLf & vbCrLf & _
          "The front-end needs to be updated. The program will now close and then should reopen automatically.", _
          vbCritical

doesn't work. It causes the next line of code in the module (Execute 'UpdateFrontEnd'.) to bring up an error.

 

[Frothingslosh]

You have it formatted incorrectly.

Here is the Microsoft page on the OpenForm method: https://msdn.microsoft.com/en-us/VBA/access-vba/articles/docmd-openform-method-access?f1url=https%3A%2F%2Fmsdn.microsoft.com%2Fquery%2Fdev11.query%3FappId%3DDev11IDEF1%26l%3Den-US%26k%3Dk(vbaac10.chm4160)%3Bk(TargetFrameworkMoniker-Office.Version%3Dv16)%26rd%3Dtrue

Please keep in mind that the answer to your issue can be found by googling, and that intelligent use of Google is a required skill for programmers.

Your error was that instead of placing the quotes around the string literal, you placed them around the entire set of arguments, which means that the engine is looking for a form named "frm_msgbx, acNormal, , , , acDialog", and opening it with default parameters.

What you want is

DoCmd.OpenForm "frm_msgbx", acNormal, , , , acDialog

Now, that said, are you going to implement a unique message box form for every single warning that Access can show? There are thousands of messages that can come up, after all. While it is possible to do a custom message box form that can be adapted to every occasion, it is pretty low ROI and, to be brutally honest, beyond your apparent current ability.

 

[psyc0tic1]

You have it formatted incorrectly.

Here is the Microsoft page on the OpenForm method: https://msdn.microsoft.com/en-us/VBA/access-vba/articles/docmd-openform-method-access?f1url=https%3A%2F%2Fmsdn.microsoft.com%2Fquery%2Fdev11.query%3FappId%3DDev11IDEF1%26l%3Den-US%26k%3Dk(vbaac10.chm4160)%3Bk(TargetFrameworkMoniker-Office.Version%3Dv16)%26rd%3Dtrue

Please keep in mind that the answer to your issue can be found by googling, and that intelligent use of Google is a required skill for programmers.

Your error was that instead of placing the quotes around the string literal, you placed them around the entire set of arguments, which means that the engine is looking for a form named "frm_msgbx, acNormal, , , , acDialog", and opening it with default parameters.

What you want is

DoCmd.OpenForm "frm_msgbx", acNormal, , , , acDialog

Now, that said, are you going to implement a unique message box form for every single warning that Access can show? There are thousands of messages that can come up, after all. While it is possible to do a custom message box form that can be adapted to every occasion, it is pretty low ROI and, to be brutally honest, beyond your apparent current ability.

Thank you for pointing out my mistake and I do a lot of reading from google searches and yes... my abilities are novice at best, most likely moronic.

I have made unique message box forms as there are only 3 that come up in my database... this one was just different being called from within a module and not realizing I screwed up on the quotation marks.

Thanks again.

 

[Frothingslosh]

No, I've seen moronic. We've had some SPECTACTULAR moronic issues here before, but yours aren't. Yours are just rookie.

 

[psyc0tic1]

So I have been informed that I have one user who this does not work for.

He claims that when the message box pops up alerting him to the new version and he clicks the OK button it just loops back tot he same message box again.

I remembered the warning about having the versions correct in both the back end table and the new version front end and that is not the problem or everyone would be complaining.

I asked for his computer information and he said he had

Windows 7 Professional 32-bit
Office 2013

since I use Windows 7 Enterprise 64-bit
Office 2013

and others use Windows 10 64-bit
Office 2016

and everyone has no trouble but this one person... is it because of the 32-bit?

If it is... I am sure I cannot figure out what to add in the code to make it work for both.

 

[isladogs]

Bear in mind that I've never used Frothy's update code but ...
1. In general, the Windows bitness shouldn't matter
2. The Access bitness will matter if you have any APIs in the module declarations. Unless you allow for that with conditional compiling, ACCDB applications won't even run in the wrong bitness and will show an error message.
ACCDE files will only run in the same bitness they were created in and if you try to run 32-bit in 64-bit Access or vice versa, you will again get an error message.

However from what you are saying neither of these sound to be an issue.
Nevertheless, it would be easy to check by testing in a 32-bit setup.

Suggest you check whether the affected user has any missing references or a folder permissions issue which stops him/her accessing the upgrade folder

 

[Frothingslosh]

For the record, that code was written for and tested on 32-bit Office and Windows settings, and I've used it successfully on both 32- and 64- bit operating systems. Also, Larson's code (mine is just a minor modification of the original) doesn't use any API calls.

If you compiled the file in 32-bit Access, then it should run just fine on any other machine using 32-bit Access.

If Colin's suggestion doesn't solve the issue (DEFINITELY DO IT FIRST), you might want to create a development copy with STOP added to the start of the update procedure, transfer it to his desktop, and run it yourself. That will make the code suspend execution and open the VBA editor, and you can step through it a line at a time using F8. That, in turn, will let you check variable and reference values (just hover over them, or check them in the immediate window). With any luck, you might find what's going on there, and why it's breaking on his machine but no other.

 

[psyc0tic1]

Thank you very much both of you for the suggestions.

I cannot test on a 32-bit system as we do not have any at my facility. The one in question is in another state.

As for the folder permissions.... there is a remote possibility this could be the issue. Our IT department (I think) gave all users read/write permissions for the back end rather than the folder it is in and the new front end version is in the same folder because I was hoping they gave the permissions to the folder.

Since the code is not giving any errors... the permissions might just be the culprit.

I will find out.

Thank you again.

 

[Frothingslosh]

Yeah, the OS shouldn't be an issue; what's important is the bit version of Office. Most companies have stayed with 32-bit Office, regardless of the bitness of the OS, due to severe compatibility issues. However, as Colin pointed out, if you have 64 bit Office, are distributing accde files, and he has 32 bit Office, then you have a problem.

Again, I'm talking about the bit value for OFFICE, not the bit value for Windows. The latter really shouldn't matter.

 

[Frothingslosh]

Here's a thought - can you RD into his desktop? Or does your company have an instant messaging platform you use? Both Microsoft Communicator and Skype have features that allow you to share desktops, and that would allow you to walk him through the test I recommended earlier.

Also, I just finished lunch, so don't be surprised if I don't respond much until this evening.

 

[psyc0tic1]

I was able to take control of his computer and test whether or not he could get to the back end file location and he could not. Folder permissions was the culprit. As I suspected after ridders references or folder permissions suggestions... our IT department gave some people permissions to the back end folder and others just access to the back end file.

I am making them fix the issue.

 

[Frothingslosh]

Glad it was an easy fix!

 

[psyc0tic1]

I'm probably going to get scolded for this but here goes.

Now that I have all of this in place... there is no way for me to look in the back end and see who has what version of the front end running on their computer. I understand that they should all be running the latest if it is working for them but I have no proof without looking through the data table and see who is updating records and who hasn't in a while.

Is it possible to have some code in the main form onload to write the version number from the tbl-fe_version, fe_version_number field (which is a local table in their fe) to a field in the tbl_users, Version field (which is a linked table from the be) and just keep overwriting it when they update? I know this is redundant data.

This way I know who is and who is not doing any updates to data and using the database?

Been doing a lot of reading but as you well know... I not very good at this. If I saw something similar I could adapt it but again I am sure I am not using the correct search terms.

 

[psyc0tic1]

FEVersion = DLookup("fe_version_number", "tbl-fe_version")

    DoCmd.SetWarnings False
    
    DoCmd.RunSQL "UPDATE tbl_users SET Version = '" & FEVersion & "' WHERE ID = " & Credentials.UserId

 

[Gasman]

I would have thought that was unnecessary, as soon as they start the app, they have to update.
The only thing I had to do was to make sure each user had a copy of the versioning app to start with. Once they had that it looked after itself.

 

[psyc0tic1]

I would have thought that was unnecessary, as soon as they start the app, they have to update.
The only thing I had to do was to make sure each user had a copy of the versioning app to start with. Once they had that it looked after itself.

In the scope of things it is definitely unnecessary as it will take care of itself but I just wanted it so I can keep track of who is actually using the database and who has even bothered to start using the first version containing the updating feature without having to dig around in the data table to see userid's of records and dates.

 

[psyc0tic1]

Back to this thread again.

In an attempt to make an admin panel form for my database I wanted to add a popup form with a text box and a command button. From this I would like to be able to type in the new version number and click the button and have it update the fe_version_number field in the local table tbl-fe_version... AND update the fe_version_number field in the linked table tbl-version_fe_master.

The purpose of this is to avoid having to go into the tables and make these edits manually and also avoiding making a mistake in one of them causing a delete loop.

Please help me with the code required to make this happen. I have not tried this before and do not even know where to start. Everything I have been reading about similar situations were for related tables and these are not. I also don't know if relating these tables will cause the version updater code to stop working.

Is it something like this?

Option Compare Database
Option Explicit

Private Sub cmdUpdateVersionNumber_Click()

    If Me.txtVersionNumber & "" <> "" Then
        DoCmd.RunSQL ("UPDATE tbl-fe_version  SET tbl-fe_version.fe_version_number = ' " & Me.txtVersionNumber)
        DoCmd.RunSQL ("UPDATE tbl-version_fe_master  SET tbl-version_fe_master.fe_version_number = ' " & Me.txtVersionNumber)
    Else
     MsgBox ("You Must First Enter a Version Number")
     txtVersionNumber.SetFocus
   End If

   txtVersionNumber = ""
End Sub

 

[psyc0tic1]

That code gave me a Run-Time error 3144 syntax error in UPDATE statement.