Can I Update entire record in SQL ?

Heya,

I am working on making a SQL database an exact mirror of a local
Access database. I check the .mdb database every few minutes for new
entries, if there are new entries, I retrieve them and write them to
an exact replica table in SQL. This all works but when I write the
values to the SQL table, If I use "INSERT INTO" or UPDATE I have to
write the value of each field from the Access database into each field
of the SQL database.

Is there any way I can update the entire record into SQL in one go ?
Like This:
--------------------------------------------------------------------------------
My tables are called "JobCard" in SQL and Access.

On Access side: Get the data:

rsax.Open "Select * From JobCard Where UPD = False", dbax,
adOpenStatic, etc


(On SQL side)
(This doesn't work but I would like something like it: )

db.Execute "Update INTO Jobcard Values * "

Where I want to update all the fields from the Access statement into
SQL without having to iterate through each field.

I have over 100 tables, each with at least 10 fields so I would save a
lot of time if I could just update all the fields at once.

Your help is much appreciated.

JimInDurbs.
RSA

On Tue, 22 Feb 2005 11:27:20 +0200, only.DeleteThis@here.co.za wrote:

 >(This doesn't work but I would like something like it: )
 >
 >db.Execute "Update INTO Jobcard Values * "
 >
 >Where I want to update all the fields from the Access statement into
 >SQL without having to iterate through each field.
 >
 >I have over 100 tables, each with at least 10 fields so I would save a
 >lot of time if I could just update all the fields at once.

There's no way I know of to UPDATE without listing each field value.

UPDATE JobCard
SET [Field_1] = 'First', [Field_2] = 'Second'
WHERE RecordId = 55

however, you will see that you can update a single record in one
statement. So, write a function to generate the SQL statement. You
could look for the SmartSQL Class from <a style='text-decoration: underline;' href="http://www.freevbcode.com," target="_blank">www.freevbcode.com,</a> or
something similar - it may save you some time.

Dim oSQL As New SmartSQL.clsSmartSQL, sSQL As String

oSQL.AddTable "Order Items"
oSQL.AddFields "ItemCost", "Tax", "ShippingCost", "TotalCost"
oSQL.AddSimpleWhereClause "OrderID", 1
sSQL = oSQL.SQL

‘ Resuting sSQL = "SELECT ItemCost, Tax, ShippingCost, TotalCost FROM
[Order Items] WHERE (OrderID = 1)"

You can change the SQL to an update query using a property, I think.
_______________________
Michael B. Johnson<!-- ~MESSAGE_AFTER~ -->