Update Access Table from Excel (1 Viewer)

I can take information from an Excel worksheet and import it into Access by specifying a path to an existing table and using DBS.AddNew to add a new record. I update the table DBS.Update and save the Primary key value to a variable which I return to the worksheet and add as a record number. intRecNum

I want to update the Access table periodically. I would like to go back to the Access Table from Excel ues the intRecNum value to pull up the specific record. I have looked for the info on Access Objects & Properties, but haven't found anything that works.

I think I need to replace DBS.AddNew something to point to an existing Record.... like DBS.ModifyRecord ?

I was thinking it would be something like :
DBS.Open "tblTest", ADOC, adOpenKeyset,
...
and then FindRecord.intRecNum (intRecNum being the Primary Key value I returned to Excel when I created the record.

I would like to know what command I would use to replace the DBS.AddNew and I would like to know the Object.Property I would use to access a specific Record in my table.

With my humble thanks,

I have made several attempts to do this, my most current attempt follows, I get "Runtime Error 424 Object Required" at the highlighted Line. Researching this error code suggests that I am not declaring a variable properly. Any help with the variable declarations, or the Object /Properties I am using to retrieve the Data from field "SRNum" would really be appreciated

Sub TestUpdate()

Dim intID As Long 'Define Primary Key Value Variable
Dim strSRNum As String 'Define Service Request # Variable
Dim ADOC As New ADODB.Connection 'Define DODB.Connection Variable
Dim DBS As New ADODB.Recordset 'Define DODB.Recordset Variable

'Select Current Document #, Assign to Variable
Sheets("Export").Select
Cells(1, 2).Select
intID = ActiveCell.Value

' Open DB to access & update record
ADOC.Open "Provider=Microsoft.Jet.oledb.4.0;" & "data source= \\[Path]\Checklist.mdb;"

DBS.Open "tblTest", ADOC, adOpenKeyset, adLockOptimistic
RecordSource = intID
CurrentRecord.TableField = strSRNum
strSRNum = CurrentValue.Field
MsgBox " SR Number is " & strSRNum

End Sub

CurrentRecord is not a part of the recordset object.
So that is why you get the object required. Normally on a form you would be able to use Me.CurrentRecord but you are not doing that. You are using ADO instead of the form's recordset object. Also, Me.CurrentRecord is only read-only and can't be set, it can only tell you where you currently are.

Thanks for replying

As I mentioned I want to do this from an Excel worksheet. It didn't seem as hard to create the original record set as it is to modify an existing record. It seems like there's a lot more secutity involved, which probably makes sense. I have been trying to go through "Help" in the Access VB editor and through a copy of the MS Access 2000 Bible (I use 2003) to figure this out, it seems like every question I answer generates 2 or 3 new questions.

Is there another, easier, way to do this other than using ADO?

Is there another Object I can use, insted or "CurrentRecord" that is read/write?

Are you going to be just inserting records and not updating existing records?

Late Reply

A quick apology, I took some time off, then had a crisis at work. Please don't take a two week gap in my reply the wrong way, I really appreciate the feedback, from you and this forum. All the incredibly bright people who contribute to it really humble me.

I need to be able to update data from Excel as well as enter new data. My spreadsheet tracks the progress of a project from pre-design through construction, so as the project progresses various values will change. If the project scope changes values could change as well.

It would be easier to do this with a form in Access, but not everybody that will input data has Access. Then there is the issue asking people to use a new and unfamiliar application. In fairness, some of them had never used Excel until this year. If I can slog through the update issue, Excel would be easier to sell to the users.

Maybe I'm not getting the whole problem here, but why not "link" excel & access table or link and update/append into an access table?