how to update table in sql 2005 from access opened form

G

ges

Hi,
I have upsizing access to sql server. I have three big tables that's heavily
updated daily by users. I have upsized these 3 tables to sql server 2005.
(I'm using Access 2003).

I'm using a form call "CollectorDashboard" to update these 3 tables. User
will type in values in the text boxes and click "update button". Below is my
code for "update button"

Private Sub cmdbtnUpdate_Click()
On Error GoTo Err_cmdbtnUpdate_Click
Dim Errorno As String
Dim errormsg As String
Dim strSQL As String

strSQL = "UPDATE (tblAccounts INNER JOIN tblCollectorNotes "
strSQL = strSQL & "ON tblAccounts.AcctIDAuto=tblCollectorNotes.
AcctIDAuto) "
strSQL = strSQL & "INNER JOIN tblCollectionsInfo ON tblAccounts.
AcctIDAuto=tblCollectionsInfo.AcctIDAuto "
strSQL = strSQL & "SET tblCollectorNotes.CN_MPELastPaidInfo_ArborSent =
" & """" & txtLastPaidDate & """" & ", "
strSQL = strSQL & "tblCollectorNotes.CN_LastPaidAmount = " & """" &
txtLastPaidAmount & """" & ", "
strSQL = strSQL & "tblCollectorNotes.CN_PersonContacted = " & """" &
txtContactPerson & """" & ", "
strSQL = strSQL & "tblCollectorNotes.CN_ContactPhone = " & """" &
txtContactPhone & """" & ", "
strSQL = strSQL & "tblAccounts.a_BillCycle = " & """" & txtBillCycle &
"""" & ", "
strSQL = strSQL & "tblAccounts.Agent = " & """" & txtAgent & """" & ", "
strSQL = strSQL & "tblCollectorNotes.CN_Letter1 = " & """" & txtLetter1
& """" & ", "
strSQL = strSQL & "tblCollectorNotes.CN_Letter2 = " & """" & txtLetter2
& """" & ", "
strSQL = strSQL & "tblCollectorNotes.CN_OutbCall1 = " & """" &
txtOutbCall1 & """" & ", "
strSQL = strSQL & "tblCollectorNotes.CN_OutbCall2 = " & """" &
txtOutbCall2 & """" & ", "
strSQL = strSQL & "tblCollectorNotes.CN_OutbCall3 = " & """" &
txtOutbCall3 & """" & ", "
strSQL = strSQL & "tblCollectorNotes.CN_InbCall1 = " & """" &
txtInbCall1 & """" & ", "
strSQL = strSQL & "tblCollectorNotes.[CN_InbCall2] = " & """" &
txtInbCall2 & """" & ", "
strSQL = strSQL & "tblCollectorNotes.CN_SoftDisc = " & """" &
txtSoftDisco & """" & ", "
strSQL = strSQL & "tblCollectorNotes.CN_HardDisc = " & """" &
txtHardDisco & """" & ", "
strSQL = strSQL & "tblCollectorNotes.CN_PromiseToPay = " & """" &
txtPromiseToPay & """" & ", "
strSQL = strSQL & "tblCollectorNotes.CN_PaymentArrangement = " & """" &
cboPaymentArrangement & """" & ", "
strSQL = strSQL & "tblCollectorNotes.CN_LastAction = " & """" &
cboLastAction.Column(0) & """" & ", "
strSQL = strSQL & "tblCollectorNotes.CN_NextFlwUp = " & """" &
txtFollowUp & """" & ", "
strSQL = strSQL & "tblCollectorNotes.CN_ManagersComment = " & """" &
txtManagerComment & """" & ", "
strSQL = strSQL & "tblCollectorNotes.CN_Notes = " & """" & txtNotes &
"""" & ", "
strSQL = strSQL & "tblCollectorNotes.CN_UpdateDate = " & """" & txtDate
& """" & " "
strSQL = strSQL & "WHERE (((tblAccounts.a_FileType)= " & """" &
lboCollectionsInfo.Column(1) & """" & ") "
strSQL = strSQL & "AND ((tblAccounts.a_Account)=" & """" & txtAccount &
"""" & "));"

CurrentDb.Execute strSQL

Before I upsize the table to sql server, the above procedure work fine. But
not after upsizing.
I got below error code:
"Error 3073" operation must use an updateable query"

Can any one please help me. I'm NEW to sql 2005. Just don't know how it
work.
Thank you in advance for any input.

Ges
 
D

dch3

There are differences in the SQL implementation between all database vendors
(IBM, Microsoft, Oracle, etc.) and even within Microsoft. Typically its going
to be something minor in the syntax. Grab a good SQLServer book and start
reading. I realize that doesn't solve the immediate question, but it'll take
care of the bigger issue at hand.

ges said:
Hi,
I have upsizing access to sql server. I have three big tables that's heavily
updated daily by users. I have upsized these 3 tables to sql server 2005.
(I'm using Access 2003).

I'm using a form call "CollectorDashboard" to update these 3 tables. User
will type in values in the text boxes and click "update button". Below is my
code for "update button"

Private Sub cmdbtnUpdate_Click()
On Error GoTo Err_cmdbtnUpdate_Click
Dim Errorno As String
Dim errormsg As String
Dim strSQL As String

strSQL = "UPDATE (tblAccounts INNER JOIN tblCollectorNotes "
strSQL = strSQL & "ON tblAccounts.AcctIDAuto=tblCollectorNotes.
AcctIDAuto) "
strSQL = strSQL & "INNER JOIN tblCollectionsInfo ON tblAccounts.
AcctIDAuto=tblCollectionsInfo.AcctIDAuto "
strSQL = strSQL & "SET tblCollectorNotes.CN_MPELastPaidInfo_ArborSent =
" & """" & txtLastPaidDate & """" & ", "
strSQL = strSQL & "tblCollectorNotes.CN_LastPaidAmount = " & """" &
txtLastPaidAmount & """" & ", "
strSQL = strSQL & "tblCollectorNotes.CN_PersonContacted = " & """" &
txtContactPerson & """" & ", "
strSQL = strSQL & "tblCollectorNotes.CN_ContactPhone = " & """" &
txtContactPhone & """" & ", "
strSQL = strSQL & "tblAccounts.a_BillCycle = " & """" & txtBillCycle &
"""" & ", "
strSQL = strSQL & "tblAccounts.Agent = " & """" & txtAgent & """" & ", "
strSQL = strSQL & "tblCollectorNotes.CN_Letter1 = " & """" & txtLetter1
& """" & ", "
strSQL = strSQL & "tblCollectorNotes.CN_Letter2 = " & """" & txtLetter2
& """" & ", "
strSQL = strSQL & "tblCollectorNotes.CN_OutbCall1 = " & """" &
txtOutbCall1 & """" & ", "
strSQL = strSQL & "tblCollectorNotes.CN_OutbCall2 = " & """" &
txtOutbCall2 & """" & ", "
strSQL = strSQL & "tblCollectorNotes.CN_OutbCall3 = " & """" &
txtOutbCall3 & """" & ", "
strSQL = strSQL & "tblCollectorNotes.CN_InbCall1 = " & """" &
txtInbCall1 & """" & ", "
strSQL = strSQL & "tblCollectorNotes.[CN_InbCall2] = " & """" &
txtInbCall2 & """" & ", "
strSQL = strSQL & "tblCollectorNotes.CN_SoftDisc = " & """" &
txtSoftDisco & """" & ", "
strSQL = strSQL & "tblCollectorNotes.CN_HardDisc = " & """" &
txtHardDisco & """" & ", "
strSQL = strSQL & "tblCollectorNotes.CN_PromiseToPay = " & """" &
txtPromiseToPay & """" & ", "
strSQL = strSQL & "tblCollectorNotes.CN_PaymentArrangement = " & """" &
cboPaymentArrangement & """" & ", "
strSQL = strSQL & "tblCollectorNotes.CN_LastAction = " & """" &
cboLastAction.Column(0) & """" & ", "
strSQL = strSQL & "tblCollectorNotes.CN_NextFlwUp = " & """" &
txtFollowUp & """" & ", "
strSQL = strSQL & "tblCollectorNotes.CN_ManagersComment = " & """" &
txtManagerComment & """" & ", "
strSQL = strSQL & "tblCollectorNotes.CN_Notes = " & """" & txtNotes &
"""" & ", "
strSQL = strSQL & "tblCollectorNotes.CN_UpdateDate = " & """" & txtDate
& """" & " "
strSQL = strSQL & "WHERE (((tblAccounts.a_FileType)= " & """" &
lboCollectionsInfo.Column(1) & """" & ") "
strSQL = strSQL & "AND ((tblAccounts.a_Account)=" & """" & txtAccount &
"""" & "));"

CurrentDb.Execute strSQL

Before I upsize the table to sql server, the above procedure work fine. But
not after upsizing.
I got below error code:
"Error 3073" operation must use an updateable query"

Can any one please help me. I'm NEW to sql 2005. Just don't know how it
work.
Thank you in advance for any input.

Ges
Click to expand...
 
G

ges via AccessMonster.com

Thanks for your advice. I did get TSQL book and try to learn the different
between sqlserver syntax and access vba sql syntax.
In sql server (tsql) I use syntax as follow:
UPDATE table1
SET field1 = 'x',
field2 = 'y',
field3 = 'z'
FROM table2 INNER JOIN table1
ON table2.ID = table1.ID
WHERE (table2.Account ='97') AND (table2.FileType ='Sales')

I can execute the update above in sql server 2005 with no problem, the row
was updated fine. But
it give me error when I run in thru opened form vba in Access. it said there
is syntax error (missing operator in 'z' and FROM table 2 INNER join table 1)

I also tried the following syntax in access query:
UPDATE table1
INNER JOIN table2
ON table1.ID = table2.ID
SET field1 = 'x',
field2 = 'y',
field3 = 'z'
WHERE (table2.Account ='97') AND (table2.FileType ='Sales')

It also give me the same error.

Do you know how to fix this syntax? thanks!

Ges



There are differences in the SQL implementation between all database vendors
(IBM, Microsoft, Oracle, etc.) and even within Microsoft. Typically its going
to be something minor in the syntax. Grab a good SQLServer book and start
reading. I realize that doesn't solve the immediate question, but it'll take
care of the bigger issue at hand.
Hi,
I have upsizing access to sql server. I have three big tables that's heavily
Click to expand...
[quoted text clipped - 76 lines]
Ges
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

HELP me! How to update table in sql server from access form 1
Syntax error in sql string to update a table from a form using co 2

Top