Replacing/Adding entrie in Access

I'm trying to fill a table in access from a table in Excel. The code is enbeded in an Excel worksheet.
I'm using the following code:

Code:

---

Sub Export()

Dim db As Database
Dim rs As Recordset
Dim col, row, field_count, i As Integer
Dim MyField, tabla As String

Set db = OpenDatabase("C:\Base.mdb")

table = "TABLE"

Set rs = db.OpenRecordset(table, dbOpenTable)

row = 2

Do While Len(Range("A" & row).Formula) > 0
    With rs
      field_count = 5
        .AddNew
        For i = 1 To field_count
            MyField= Worksheets("FIELDS").Cells(i + 3, 1).Value
            col= Worksheets("FIELDS").Cells(i + 3, 3).Value
            .Fields(MyField) = Worksheets(table).Cells(row, col).Value
        Next i
        .Update
    End With
    row= row+ 1
Loop

rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
   
End Sub

---

The problem is that using .AddNew, I'm just adding a new entry and it won't just replace prior entries. I thought of two solutions. Either deleting the whole entries of the table and the adding the new entries, or replacing every entry one by one. How could I solve this?

In your code, this will delete all your records prior to your import.

db.execute "Delete * from table"

Or some variation, depending on your database language.

Code:

---

Set db = OpenDatabase("C:\Base.mdb")
db.execute "Delete * from table"

---

It works perfectly. Just one question left. By adding new entries, the primary key keeps increasing. How could I reset it, in order to start from 1,2,3,.. ?

These Dim statements don't do what you probably intend. Only i as being dimmed as Integer, only tabla as string. The rest are Variants, not normally what you want.

Quote:

---

Originally Posted by Fonty

Code:

---

Dim col, row, field_count, i As Integer
Dim MyField, tabla As String

---

---

Instead you have to specify each variable's data type:

Code:

---

Dim col As Integer, field_count As Integer, i As Integer
Dim MyField as String, tabla As String

---

Thanks. I didn't realise that.
Do you know howw could I reset the ID number?

I don't know Excel that well. In Access you could export the data to a new table, or drop the field and then recreate it.