HELP me! How to update table in sql server from access form

[ges via AccessMonster.com]

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

 

[Sylvain Lafontaine]

First: help you and God will help you.

Second, show us the result of the string strSQL. You can use Debug.Print to
display it and make a Cut&Paste. Giving some information about the relevant
parts of the structures of the tables would aslo be a good idea, too,
because a query by itself can be more or less meaningless.

Third, you can simplify your life - and our - by making a simpler test:
remove things down to the backbone of what's not working properly.

Fourth, there are differences of syntaxe between Access and SQL-Server.
Usually, JET will make the conversion but not always; especially if the
query is a little complicated. By taking a look with the SQL-Server
Profiler, you'll see exactly what's Access is attempting to do on
SQL-Server. It's quite possible that you will have to build and use a
passthrough query instead but by doing so, you will have to use the exact
syntaxe required by SQL-Server. Some knowledge of SQL-Server is often
required if you want to work against it; even by using ODBC linked tables.

Also, make sure that all upsized tables have their primary keys defined.

Finally, this newsgroup is about ADP and SQL-Server and has nothing to do
with ODBC linked tables. You should post in a more appropriate newsgroup
next time.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

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