Check for newest DB version? (1 Viewer)

Gasman

Enthusiastic Amateur
Local time
Today, 08:56
Joined
Sep 21, 2011
Messages
14,050
I use this from a Maintain Version Form button

Code:
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.?
 

psyc0tic1

Access Moron
Local time
Today, 03:56
Joined
Jul 10, 2017
Messages
360
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. :D
 

psyc0tic1

Access Moron
Local time
Today, 03:56
Joined
Jul 10, 2017
Messages
360
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
Code:
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

Enthusiastic Amateur
Local time
Today, 08:56
Joined
Sep 21, 2011
Messages
14,050
Make it a string and run the string, after you debug.print the string
 

psyc0tic1

Access Moron
Local time
Today, 03:56
Joined
Jul 10, 2017
Messages
360
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

Access Moron
Local time
Today, 03:56
Joined
Jul 10, 2017
Messages
360
This is the whole block of code
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

Enthusiastic Amateur
Local time
Today, 08:56
Joined
Sep 21, 2011
Messages
14,050
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
Code:
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

Access Moron
Local time
Today, 03:56
Joined
Jul 10, 2017
Messages
360
Well that complicates matters.
My suggestion was to do this
Code:
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
Code:
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

Enthusiastic Amateur
Local time
Today, 08:56
Joined
Sep 21, 2011
Messages
14,050
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

Access Moron
Local time
Today, 03:56
Joined
Jul 10, 2017
Messages
360
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
 

Users who are viewing this thread

Top Bottom