Error

[Tonyony]

Hello,



I've been working with my database for several years and suddenly there are some errors.

1 * When choosing a year this message shows:
-2147217900 (80040e14) Instruction UPDATE gives SYnTAX error


2 * I also gets following message when I try to create a new record
The command INSERT INTO contains SYNTAX ERROR



I don't know why this suddenly happens.

Can anyone help me?

Thanks in advance
Tony

[CJ_London]

you may have a corruption - suggest try a compact and repair.

Other thing to check if you are using VBA that you have all the required references.

Can you revert to a backup? and if so is that OK?

Is the database split? if no, it should be.

is the database multi user? If so does each user have their own copy of the front end? If no, they should do

not doing so is often the reason for corruption.

[Tonyony]

Thanks for the quick response.

you may have a corruption - suggest try a compact and repair.

I did that, but nothing seems to happen.

Other thing to check if you are using VBA that you have all the required references.

Not sure.

Can you revert to a backup? and if so is that OK?

I also used several backups but doesn't seem to work.

Is the database split? if no, it should be.

I don't know. How can i check this?

is the database multi user? If so does each user have their own copy of the front end? If no, they should do
not doing so is often the reason for corruption.

It's not multi user. It's just for my own use.

I also did a Microsoft Office Repair but this doesn't seem to have any effect.

I also came across this when changing the field YEAR:
Private Sub cmbJaar_AfterUpdate()




Dim adorst As ADODB.Recordset
Dim adocmd As New ADODB.Command
Set adorst = New ADODB.Recordset
adorst.Open "SELECT tblJaarMaand.intJaarId FROM tblJaarMaand WHERE (((tblJaarMaand.idsJaarMaandId)=" + CStr(txtMaand.Value) + "));", CurrentProject.Connection, adOpenStatic
If CInt(cmbJaar.Value) <> adorst("intJaarId").Value Then
Set adocmd.ActiveConnection = CurrentProject.Connection
adorst.Close
adorst.Open "SELECT tblJaarMaand.idsJaarMaandId FROM tblJaarMaand WHERE (((tblJaarMaand.intJaarId)=" + CStr(cmbJaar.Value) + ") AND ((tblJaarMaand.intMaandId)=" + CStr(cmbMaand.Value) + "));", CurrentProject.Connection, adOpenStatic
adocmd.CommandText = "UPDATE tblFirma SET tblFirma.intMaandId=" + CStr(adorst("idsJaarMaandId").Value) + " WHERE (((tblFirma.idsFirmaId)=" + CStr(Form_frmFirmalijst.cmbFirma.Value) + "));"
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Me.Refresh
adocmd.Execute
End If
adorst.Close

Does this mena anything to you?

Thanks for the help.
Tony

[CJ_London]

vba references - in the vba window click on tools>references.

If you are using ADODB then you should have a library - think it is called Microsoft ActiveX Data Object 2.1 library

Not sure what you mean here - what does 'doesn't seem to work' mean? you get the same error?

Can you revert to a backup? and if so is that OK?
I also used several backups but doesn't seem to work.

Also I see from your code you are using domenuitem. I could be wrong but I thought that was deprecated in 2007 - see this link, with particular reference to the remarks section

https://msdn.microsoft.com/en-us/lib.../ff822447.aspx

database split, means data (tables) is in one db (the backend), forms/queries/reports/code in another (the front end) which then links to the back end. This should be the case for all databases except perhaps for 'quick and dirty'. The reason is you can get corruptions in any area, by splitting the db you reduce the risk of not being able to make a recovery.

[Tonyony]

Ok. Thanks for the reply.
The database I'm working with is made in 2002 (with Access 2002), so it is very old, but up until now worked OK.
Should the term domenuitem be rewritten?
Is there maybe a way to send the database to you for a check-up???
Tony

[CJ_London]

Sorry, I don't have time to check your db - the link I provided tells you what to do, or google for other ideas.

Have you recently upgraded to 2010 and it has not worked since, or it has been working OK in 2010 until now?

The other possibility is you have missing data in your query so the syntax is incorrect - usually you use the ampersand (&) rather than + to concatenate strings together

put

debug.print "UPDATE tblFirma SET tblFirma.intMaandId=" + CStr(adorst("idsJaarMaandId").Value) + " WHERE (((tblFirma.idsFirmaId)=" + CStr(Form_frmFirmalijst.cmbFirma.Value) + "));"

just after the adOpenStatic line and check that it is valid sql

[Tonyony]

Have you recently upgraded to 2010 and it has not worked since, or it has been working OK in 2010 until now?

It has been working ok unitl now.
I'll follow your directions and maybe i'll get the job done.
Thanks for so far.
Tony