append query question in Access97

  • Thread starter Thread starter sparky
  • Start date Start date
S

sparky

I have a macro which runs an append query from a table, appending the info
into another table. I run this macro once a month. I just realized that
each time I have run this, instead of appending only new info, it adds all
the info. Looking at my table that gets the info appended, for 11/08 I have
1 set of records, which is correct. For 10/08, I have 2 sets of records
(each set a duplicate of each other). for 9/08, I have 3 sets of alike
records. and so on......

I thought to append meant that only those records that are different would
get appended.

My main table, on which the macro is run, does contain the info from all
previous months, but does not contain duplicate sets.

Here is the code for the macro Update AB Sub data:

Private Sub AB_data_Click()
On Error GoTo Err_AB_data_Click

Dim stDocName As String

stDocName = "ABfromexcel"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_AB_data_Click:
Exit Sub

Err_AB_data_Click:
MsgBox Err.Description
Resume Exit_AB_data_Click

End Sub

Is there a way to keep the query from duplicating the info already in the
table? I could just delete the info in the table before running the macro,
but again I thought that appending would just add the new info to the
existing table, without duplicating info already in the table.

Does this make sense?

These are the steps I take to do this.

I have a .csv file in excel which I import monthly into Access97 using this
macro:
Transfer from Excel
transfer type: import delimited
spec name: not used
Table Name: Hour_log
file name : G://hour_log.csv
has field names: yes
HTML table name: not used

After I do this step, I then run my update macro, as outlined above.

Thanks in advance for any help or direction.

Rick
 
Create a unique index (AKA constraint) based on all the fields in the table
that would constitute a duplicate. Then when you run the query you should see
a message stating that X number of records won't be appended due to violating
a constraint. Once you press OK (or is it Yes?) it will allow the non-dupe
records to append.

If you don't want to see this message, turn Set Warnings to Off at the
beginning of the macro. IMPORTANT: Don't forget to turn Set Warnings back to
On at the end of the macro.
 
Back
Top