With Jet, I don't think you can fire off one statement to do that. I
think you'd need at least two - but there shouldn't be any need to fire
it per each record.

Say table a and b with identical structure, I think you'd first do the
update where the PK exists

UPDATE a INNER JOIN b ON a.id = b.id
SET
    a.field1 = b.field1
    a.field2 = b.field2
....

then try the insert

INSERT INTO a
SELECT *
FROM b
WHERE Not Exists (
    SELECT id
    FROM a
    WHERE a.id = b.id)

Technically You're not connecting to an Access database, as such beast
doesn't really exist ;-)

You're connecting to a Jet database, which also happens to be the
default database of Access. But Access can also be used as front end for
other platforms.

I don't know much about the .Net world, but aren't there supposed to be
some automatic update/insert thingies in connection with the data
adapter or datatable (commandbuilder?)? Perhaps an inquiry to a NG
dedicated to the technology you're using might give more precise
answers?

Greetings,

.Net is tricking you a little bit.  You can combine a
select/Insert/Update/Delete command in a single dataAdapter object.  So
you really have 4 separate commands combined into one when you first
create the dataAdapter object.  You have to declare each command
individually, then when you fill a dataset with data from a database
table (Access, MS Sql Server, Oracle...) all you have to do is edit your
in memory dataset datatable and the commands you declared will take care
of the rest automatically.

Note:  I would steer clear of the command builder object.  It creates
generic commands that may not necessarily suit your needs.  Here is how
you declare all your command in one shot (at the Form Level):

Imports System.Data
Imports System.Data.OleDB

Dim da As OleDBDataAdapter, conn As OleDBConnection
Dim ds As DataSet

Private Sub Form1_Load(...)...
ds = New DataSet
conn = New OleDBConnection
conn.ConnectionString = "provider=microsoft.jet.oledb.4.0; Data Source =
db2test.mdb"
da = New OleDBDataAdapter
da.SelectCommand = New OleDBCommand
da.SelectCommand.Connection = conn
da.InsertCommand = New OleDBCommand
da.InsertCommand.Connection = conn
da.UpdateCommand = New OleDBCommand
da.UpDateCommand.Connection = conn

da.InsertCommand.CommandText = "Insert Into Table1(FName, LName, Phone)
Select @FName, @LName, @Phone"
da.InsertCommand.Parameters.Add("@Fname", OleDBType.Varchar, 50,
"FName")
da.InsertCommand.Parameters.Add("@LName", OleDBType.Varchar, 50,
"LName")
da.InsertCommand.Parameters.Add("@Phone", OleDBType.Varchar, 50,
"Phone")

'--do the same for the UPdate Command

da.SelectCommand.CommandText = "Select * from Table1"
da.Fill(ds, "tbl1") '--tbl1 gets created automatically inside ds when
calling da.Fill

'--Note:  if you are entering/editing data through datagridview control,
then you will have to loop through your dataset table to copy the
entries/edits from the datagridview to the dataset table and then call

da.Update(ds, "tbl1")

the da.Update call will automatically invoke either/and/or the
Insert/Update commands.  da.Fill only applies to the Select command.
So, if you are entering data directly into your dataset table from
textboxes, then you don't need to worry about making sure that
entries/edits are already in the dataset table:

Private Sub btnAdd_Click(...)handles btn1.Click
Dim dr As DataRow
dr = ds.Tables("tbl1").NewRow
dr("FName") = txtFName.text
dr("LName") = txtLName.Text
dr("Phone") = txtPhone.Text
ds.Tables("tbl1").Rows.Add(dr)
da.Update(ds, "tbl1")
End Sub

Another Note:  .Net may seem a little bit on the busy side or complex -
i.e. for Access.  Where ADO.Net is real nice is when you are dealing
with large tables on a sql server DB.  Actually, if you have to upload
data from a bunch of Access MDB's to the sql server, ADO.Net is real
nice for that too.  It is a straight forward pull and push.  You use the
da.Fill command to pull the data from Access to your memory table
da.Fill(ds, "tbl1") and then use the dataTable.CreateReader property of
the memory table to push the data straight to the sql server table.  No
data looping involved (I had to write a routine to import data from 50
mdb's in one shot to a sql server db table - it was a snap with
ADO.Net).  I did have to set up a loop to import the data from each MDB
to my memory table (all the tables were the same structure for each
mdb).  Then I pushed the data in my memory table to the sql server in
one shot:

Dim reader As DataTableReader = dsSql.tblImport.CreateDataReader
dsOle.Tables("tbl1").Load(reader, LoadOption.Upsert)
daSql.Update(dsSql, "tblSql")

Note:  have to create the sqlInsert, sqlUpdate commands and all the
parameters for this to work.  And if you have at least 2gigs of memory
and at least a 2.8 gig processor, this will upload 500,000 records in a
matter of seconds (the hardware is the catch with .Net).

Rich