Hi,

I am trying to Update a Access 2002 DB Table using the data from a Excel Sheet (Visual Basic 6).

---------------------------------------------------------
Dim cn As ADODB.Connection
Dim rsT As ADODB.Recordset

Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & "c:" & _
"\b.xls;Extended Properties=" & """Excel 8.0; HDR=No;"""
.CursorLocation = adUseClient
.Open
End With
Set rsT = cn.OpenSchema(adSchemaTables)

rsT.Open "SELECT * FROM [Sheet1$]", cn
----------------------------------------------------------

ok with that I can get the data in Excel to a RS, great. Now I need to use SQL to update it, "UPDATE SET;" to update the access table.

Why dont I use the rs.Update() method with a cn to the DB well that makes things complicated, and I can do it with the SQL faster and basically in one step, if I could set that part up.

Help would be appriciated.

Are you updating a table in access or adding new records to a table in access?

If you are adding new records do this:

rsT.MoveFirst
Do While not rsT.Eof

cn.Execute "INSERT Table1(Field1) SELECT '" & rsT.fields("Field1") & "'"

rsT.MoveNext

Loop

If you are updating existing records in a table do this (I am giving a simple linking example as I do not know how your recordset links to your table):

rsT.MoveFirst
Do While not rsT.Eof

cn.Execute "UPDATE Table1 SET Field1='" & rsT.fields("Field1") & "' FROM Table1 WHERE Table1.Field2='" & rsT.fields("Field2") & "'"

rsT.MoveNext

Loop

SCIROCCO,

thanks for your reply, yes im trying to update the recs, and u code works for that. when i saw it, i went - why didnt i think of that.

boy did i feel stupid.

again thanks alot for you input.

-VX-