I have a macro that imports data into and access database. Sample
below
Is it possible that during the

.AddNew ' create a new record

line of code to have excel return the autonumber\primary to was
created in access key back to excel. Excel update access row by row
starting column A-L. I want the code to put the primary key in column
j.







Do While Len(Range("A" & r).Formula) 0
' repeat until first empty cell in column A
With rs




..AddNew ' create a new record
' add values to each field in the record
.Fields("DateSubmitted") = Range("A" & r).Value
.Fields("JobNumber") = Range("B" & r).Value
.Fields("JobName") = Range("C" & r).Value
.Fields("TotalPCS") = Range("D" & r).Value
.Fields("Area") = Range("E" & r).Value
.Fields("STREr") = Range("F" & r).Value
.Fields("GTREs") = Range("G" & r).Value
.Fields("Status") = Range("H" & r).Value
.Fields("MailMethod") = Range("I" & r).Value
.Fields("ImportDateTime") = Now()
' add more fields if necessary...
.Update ' stores the new record
End With



r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing







Little Penny

Well, *IF* it is possible the only way you could do it would be to
swap out the update statement with a select statement.

I know it sounds strange if you're not using Access - but with Access
you can update with a select statement.

The first step is to find out if you can set up a trigger to assign
the primary key within Access.

It probably isn't possible to do it - so you might have to add a
select statement that inserts the data just before the Loop statement.

What you're doing sort a *wrong* approach.

You're using Excel as a database instead of as a static database or as
a spreadsheet.

With this approach you risk loading the data multiple times and
corrupting the data.

I recommend doing all inserts and updates within Access unless you are
fully up to speed on ADO and you've got the time to merge rows into
your database instead of just inserting rows.

What I meant is that it probably isn't possible to do the insert, set
off the trigger, and do a select all within the single select.

But try it and see what happens.

It probably isn't possible to do it - so you might have to add a
select statement that inserts the data just before the Loop statement.