VBA Code for Update Query Not working

[FA]

Hi Friends, If someone can suggest me the possible solution to my error
" Too few parameters, expected 17" i would really really appreciate it.

Thanks in advance

Moe

Private Sub Command93_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strFlagSQL As String
Set db = CurrentDb
'Set rst = CurrentDb().OpenRecordset("qryContactsForFullATAR",
dbOpenSnapshot, dbSeeChanges)
strFlagSQL = "Update dbo_SYS_INFO Set dbo_SYS_INFO.RIT_AT_DATE =
dbo_CSA_AT.ResultDateGMT," & _
" dbo_SYS_INFO.RIT_AT_CTAC_NME = dbo_CSA_AT.YourName," & _
" dbo_SYS_INFO.RIT_AT_CTAC_NO = dbo_CSA_AT.YourPhone," & _
" dbo_SYS_INFO.SYS_EXPD_PROD_DATE = dbo_CSA_AT.qr320," & _
" dbo_SYS_INFO.SYS_DEV_TECH_CTAC_NME = dbo_CSA_AT.DeveloperName," & _
" dbo_SYS_INFO.SYS_DEV_TECH_CTAC_NO = dbo_CSA_AT.DeveloperPhone," &
_
" dbo_SYS_INFO.SYS_DB_NME = dbo_CSA_AT.DatabaseName," & _
" dbo_SYS_INFO.SYS_DB_SERV_NME = dbo_CSA_AT.DatabaseServerName," & _
" dbo_SYS_INFO.SYS_WEB_SERV_NME = dbo_CSA_AT.WebServerName," & _
" dbo_SYS_INFO.SYS_WEB_SERV_URL = dbo_CSA_AT.WebServerURL," & _
" dbo_SYS_INFO.SYS_URL = dbo_CSA_AT.URLtoTest," & _
" dbo_SYS_INFO.SYS_2_CC = dbo_CSA_AT.CharacterApplicationCode," & _
" dbo_SYS_INFO.SYS_DESC = dbo_CSA_AT.qr30y," & _
" dbo_SYS_INFO.SYS_PROJ_DESC = dbo_CSA_AT.qr32y," & _
" dbo_SYS_INFO.RIT_AT_SCORE = dbo_CSA_AT.Total," & _
" dbo_SYS_INFO.SYS_CLASS_INFO_ID = dbo_CSA_AT.SystemClass Where
dbo_SYS_INFO.SYS_ID_CODE = dbo_CSA_AT.ResultID "
'DoCmd.RunSQL
db.Execute strFlagSQL, dbFailOnError Or dbSeeChanges

 

[Douglas J Steele]

You're missing the reference to table dbo_CSA_AT in your SQL statement.
Simply putting it in the field names (and the WHERE clause) isn't
sufficient.

Try something like

"UPDATE dbo_SYS_INFO INNER JOIN dbo_CSA_AT " & _
"ON dbo_SYS_INFO.SYS_ID_CODE = dbo_CSA_AT.ResultID " & _
"Set dbo_SYS_INFO.RIT_AT_DATE = dbo_CSA_AT.ResultDateGMT, " & _
"dbo_SYS_INFO.RIT_AT_CTAC_NME = dbo_CSA_AT.YourName, " & _
"dbo_SYS_INFO.RIT_AT_CTAC_NO = dbo_CSA_AT.YourPhone, " & _
"dbo_SYS_INFO.SYS_EXPD_PROD_DATE = dbo_CSA_AT.qr320, " & _
"dbo_SYS_INFO.SYS_DEV_TECH_CTAC_NME = dbo_CSA_AT.DeveloperName, " & _
"dbo_SYS_INFO.SYS_DEV_TECH_CTAC_NO = dbo_CSA_AT.DeveloperPhone, " & _
"dbo_SYS_INFO.SYS_DB_NME = dbo_CSA_AT.DatabaseName, " & _
"dbo_SYS_INFO.SYS_DB_SERV_NME = dbo_CSA_AT.DatabaseServerName, " & _
"dbo_SYS_INFO.SYS_WEB_SERV_NME = dbo_CSA_AT.WebServerName, " & _
"dbo_SYS_INFO.SYS_WEB_SERV_URL = dbo_CSA_AT.WebServerURL, " & _
"dbo_SYS_INFO.SYS_URL = dbo_CSA_AT.URLtoTest, " & _
"dbo_SYS_INFO.SYS_2_CC = dbo_CSA_AT.CharacterApplicationCode, " & _
"dbo_SYS_INFO.SYS_DESC = dbo_CSA_AT.qr30y, " & _
"dbo_SYS_INFO.SYS_PROJ_DESC = dbo_CSA_AT.qr32y, " & _
"dbo_SYS_INFO.RIT_AT_SCORE = dbo_CSA_AT.Total, " & _
"dbo_SYS_INFO.SYS_CLASS_INFO_ID = dbo_CSA_AT.SystemClass"


Of course, I do question the validity of duplicating your data in two
different tables....

 

[FA]

Thanks Millions Doug, actually i have a excel sheet that i import the
data on a daily basis. First i dump that excel sheet into a temp table
called dbo_CSA_AT, and then update the main table's records
(dbo_SYS_INFO). That's why i have duplicate data. But the temp table
gets deleted everytime i upload the excel sheet.

Any other seggutions to do this tasks? please let me know.

Thanks
Moe

 

[Douglas J Steele]

I wouldn't bother importing the data unless you're doing some massaging to
it once it's imported. Instead, link to the spreadsheet, so as to avoid
bloating your database.

You can store a query that does the append. Even if the table gets deleted,
the query will still be there, and you can run it again once the linked
table gets recreated.