Overwrite on existing Record

[waqas]

Dear Experts,

i am uploading data from excel using below code.

now i want to do overwrite on existing data in access table.



what should i do? how can i get.

Public Sub DoExcelImport()
' Excel object variables
Dim appExcel As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet
'*********Leave ReocardSet
Dim LeaveRec As Recordset, dbs As Database
'*******************
Dim rs2 As New ADODB.Recordset, IDFind As String
Dim cnn2 As New ADODB.Connection
Dim cmd2 As New ADODB.Command, SourceRange As String

SourceFile = CurrentProject.Path & "\Leave1.xlsx"

Set rsCon = CreateObject("ADODB.Connection")

With cnn2
   .Provider = "Microsoft.Jet.OLEDB.4.0"
   .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & SourceFile & ";Extended Properties=""" & "Excel 12.0 Xml;HDR=YES;IMEX=1;" & """"
   .Open
End With
Set cmd2.ActiveConnection = cnn2
cmd2.CommandType = adCmdText
cmd2.CommandText = "SELECT * FROM [Sheet1$]"

rs2.CursorType = adOpenStatic

rs2.Open cmd2

Set dbs = DBEngine(0)(0)
Set LeaveRec = dbs.OpenRecordset("Leave", dbOpenTable)

For i = 1 To rs2.RecordCount
   With LeaveRec
      .AddNew
      .Fields(0) = rs2.Fields(0)
      .Fields(1) = rs2.Fields(1)
      .Fields(2) = rs2.Fields(2)
      .Fields(3) = rs2.Fields(3)
      .Update
   End With
   rs2.MoveNext
Next

Set wks = Nothing
Set appExcel = Nothing
rs2.Close
cnn2.Close
doExcelAutomationExit:
Exit Sub
doExcelAutomation:
MsgBox Err.Description, vbOKOnly, Err.Source & ":" & Err.Number
Resume doExcelAutomationExit
End Sub

[June7]

If you want to replace all records in the table, don't need recordset and AddNew certainly doesn't replace.

Delete all records in table then import. Consider the TransferSpreadsheet method instead of writing to recordset.

[waqas]

thanks june
but i need to overwrite because it is my application requirement. any solution please.

[Rawb]

Even if you can't or don't want to use the TransferSpreadsheet method, you can still use June's other suggestions.

1. Delete all Records in the Table
2. Import the Excel Workbook's Spreadsheet
3. ???
4. PROFIT!

[June7]

Deleting all records and then importing all the data in is the same effect as overwriting, unless you only want to selectively replace certain fields and don't want differences in other fields to be applied. In which case it might be easier to import or link to the spreadsheet and run an UPDATE sql action.

Then the question is does the spreadsheet have new records not currently in the table or does the table have records you want to eliminate?

Regardless, AddNew does not overwrite existing records in table. You would have to move to record in table recordset with unique ID that matches unique ID of current record of the spreadsheet recordset and then simply set the values in the desired fields.

[waqas]

yes june.
i know your suggestion is very good. but i am receiving different excel files from other source having new information included some changes for existing records which is very hard to find from thousands record
for this i export full table into excel than use excel formula match and index compare then upload it and changes one by one therefore i need to overwrite to save time. i thought before to make one recordset from table one recordset from excel but question how can i compare both recordset matching records then update it. for example one id number 444333 has leave from 12-Apr-12 to 17-Apr-12 Annual Leave
after three days i received 500 entries to add from 500, 10 already existing only we have to change date period and leave type like same id number 444333 new information 12-Apr-12 to 19-Apr-12 Emergency Leave these types of information i have to overwrite because date period is already exist and leave type also is changed. how can i do it?

[June7]

Okay, so the spreadsheet is not a complete set of data, it is new records and corrected records.

Sounds like two things need to happen:

1. add new records (490 of the 500 are new)

2. update existing records (10 of the 500 are data revision)

This means two SQL actions: INSERT, UPDATE

If you can set a link to the spreadsheet, both will be simplified. Link can be set programmatically.
The addition of new records would be an INSERT SELECT action.
The edit of existing would be an UPDATE action.

If link is not practical then your code would have to be modified to determine if the spreadsheet record ID is in the table (or its VBA recordset) and if it is change the data, if it is not add new record. The INSERT or UPDATE sql action could be executed to modify the table or can modify recordset of the table.

The table recordset will probably have to be DAO so can use FindFirst method to search for the ID.

[waqas]

ok thanks.

it is ok find id number to use findfirst what about date period. how can i find matching date peirod of same id number. which i have mention in my previous reply.

[June7]

The FindFirst can have multiple criteria in the search expression.