VBA Code for Update Query Not working

  • Thread starter Thread starter FA
  • Start date Start date
F

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
 
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....
 
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
 
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.
 
Back
Top