SQL table updating

[Guest]

I am an Access developer and do much SQL backend and Access frontend work.
Current project is a form that is bound to table that has been populated by a
pass thru query from SQL. This table has approx 60+ fields to input, each
record is tied to a unique ID taken again from the source SQL table.
I am looking for the 'BEST WAY" most efficient way of taking the table thats
been edited and UPDATING the source SQL table using the ID (primary key) as
the criteria
I am very familiar with feeding parameters back to a SQL stored procedure
which does this however in this case it seems I have to create parameters for
every field which is cumbersome. Cumbersome too is creating the SQL statement
with all these fields in Access VBA. Is there away thru ADO or whatever to
just do some kind of SELECT * from the Access table and UPDATE the SQL table.
Or SELECT each record and UPDATE the SQL table one by one again without
having to create over 60 paramenters. The tables are exactly alike.

thanks again for any help.

 

[Guest]

Hi Bret

What ever you do, you'll have to set 60 fields, so there is no different
between Update query, record set, or SP
===============================
Update Query
Update TableName Set Field1 = " & Me.Field1 & " , Field2 = " & Field2 ......
================================
Record Set

Dim MyRec as Dao.Recordset
Set MyRec = MyDb.Openrecordset("Select * From TableName Where ....")
MyRec.Edit
MyRec!Field1 = Me.Field1
MyRec!Field2 = Me.Field2
;
;
MyRec!Field60 = Me.Field60
MyRec.Update
=====================================
Or SP, pass 60 fields
=====================================
So as you can see you will always need to use 60 fields, unless you bound
the fom to the table.
So I would go with the SP, less traffic on the network, less locking, and
quicker