Check for newest DB version?

[Gasman]

I use this from a Maintain Version Form button

Private Sub cmdUpdate_Click()
Dim db As DAO.Database
Dim strSQL As String

Set db = CurrentDb()

strSQL = "UPDATE [tbl-fe_version] SET [tbl-fe_version].fe_version_number = " & Chr(34) & Me.txtVersion & Chr(34)
db.Execute strSQL

strSQL = "UPDATE [tbl-version_fe_master] SET [tbl-version_fe_master].fe_version_number = " & Chr(34) & Me.txtVersion & Chr(34)
db.Execute strSQL

Me.Refresh
Set db = Nothing
End Sub

Tip: Build the string, then you can debug it and execute it.

You have opening single quote, but no closing single quote.?

 

[NauticalGent]

Frothy’s/Larson’s code is an excellant solution. That being said, it use a VBScript to check for the most current version and install the application.

The are many FE updater utilities, but this one worked the best for me:
https://www.access-programmers.co.uk/forums/showthread.php?t=207187&highlight=Mosca

 

[psyc0tic1]

You have opening single quote, but no closing single quote.?

Yea... told ya I didn't know what to do.

You code worked perfectly. Thanks a million Gasman.

 

[psyc0tic1]

Sorry to post again in this long thread but Microsoft has done something to access that is causing this line to throw the 3144 Syntax error in UPDATE statement error

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

What is wrong with the syntax now? This was working up until a couple of days ago.

 

[Gasman]

Make it a string and run the string, after you debug.print the string

 

[psyc0tic1]

Make it a string and run the string, after you debug.print the string

I do not know how to do that

 

[psyc0tic1]

I don't have an issue on my computer... it is happening to one of my users overseas. I have no idea what version of office they have or what version of windows they are using. Terrible language barrier and always 24 hours delay in getting answers

 

[psyc0tic1]

This is the whole block of code

Private Sub Form_Open(Cancel As Integer)
    Dim dbs As DAO.Database
    Dim FEVersion As Variant

    Set dbs = CurrentDb
    If Credentials.AccessLvlID = 0 Then
        DoCmd.OpenForm "frm_loginform"
        Cancel = 1
    End If
    
    If Credentials.UserId = 2 Then
        If Weekday(Now) = vbMonday Then
            WaitVis
            WaitLab
            SendEMail
        End If
    End If

    If Credentials.UserId = 2 Then
        dbs.Execute "AppendNewPONumbers", dbFailOnError
        dbs.Execute "AppendNewPoNumbers_WNK", dbFailOnError
    End If

        FEVersion = DLookup("fe_version_number", "tbl-fe_version")
        DoCmd.SetWarnings False
        DoCmd.RunSQL "UPDATE tbl_users SET Version = '" & FEVersion & "' WHERE ID = " & Credentials.UserId
    
    If Credentials.AccessLvlID = 6 Then
        MsgBox "Your Account Has Been Deactivated. Please Contact the Administrator."
        DoCmd.Quit
    End If
End Sub

 

[Gasman]

I don't have an issue on my computer... it is happening to one of my users overseas. I have no idea what version of office they have or what version of windows they are using. Terrible language barrier and always 24 hours delay in getting answers

Well that complicates matters.
My suggestion was to do this

Dim strSQL as String
...
....
strSQL = "UPDATE tbl_users SET Version = '" & FEVersion & "' WHERE ID = " & Credentials.UserId
DoCmd.RunSQL strSQL

Now you can set a breakpoint on the DoCmd line or insert a Debug.Print between those lines and see what the string actually contains.

At present you are assuming there are valid values for FEVersion and Credentials.UserId. If there were, the syntax should be correct.?

So my 'guess' is one of those has no or invalid content.? if this has been working fine and not been changed, then it can only be the data that is causing it.?

HTH

 

[psyc0tic1]

Well that complicates matters.
My suggestion was to do this

Dim strSQL as String
...
....
strSQL = "UPDATE tbl_users SET Version = '" & FEVersion & "' WHERE ID = " & Credentials.UserId
DoCmd.RunSQL strSQL

Now you can set a breakpoint on the DoCmd line or insert a Debug.Print between those lines and see what the string actually contains.

At present you are assuming there are valid values for FEVersion and Credentials.UserId. If there were, the syntax should be correct.?

So my 'guess' is one of those has no or invalid content.? if this has been working fine and not been changed, then it can only be the data that is causing it.?

HTH

The printout is

UPDATE tbl_users SET Version = 'v018' WHERE ID = 1

ID = 1 is my login

I tested the users login that is having the issue on my computer and their login works and their version number is already correct because I haven't published any updates in weeks.

I asked them to send me a screenshot of the highlighted code when they get the error but they have not responded. I know it is the update code I posted because it is the only update code running when they click the OK button on the message box preceding the error.

Hard to fix a problem you can't reproduce.

 

[Gasman]

Send the offending user another accde that has a MSGBOX to show the SQL string.

Whilst MS have issued a few updates to **** up Access functionality, I've not heard of this one.

Alternatively login on to their computer via your support portal and do what you can over the net.

Regardless, you really need to see what that statement is.?

 

[psyc0tic1]

Send the offending user another accde that has a MSGBOX to show the SQL string.

Whilst MS have issued a few updates to **** up Access functionality, I've not heard of this one.

Alternatively login on to their computer via your support portal and do what you can over the net.

Regardless, you really need to see what that statement is.?

That is a good idea about the message box... I think I will do that... as far as connecting to their computer... they are in Viet Nam... and I am not a network admin so this probably will be difficult. Maybe I can catch them tonight (their morning) over Skype for business and have them allow me control of the computer that way.

Thanks Gasman

 

[Gasman]

I do not know how to do that

Rereading this thread, I can see I showed you how in post #41 ?