Method on adding new table records

[colintis]

I'm looking to see if there's any better solution to add new records from one table to another.

In table A there's >200k of records, and increase hundreds daily. Table B stores a copy of table A, used for the Access functionalities ' better speed. When every time I update table B, it will takes 15 minutes to remove all the records in table B, then insert all records from table A.

Does any one have a better idea on putting new records to table B?

[nico5038]

In general we don't copy a table, and that's the fastest solution, just use it :-)

You could copy the whole database in the explorer and use the copy to work with for reporting. Guess that's the fastest.

When speeding up the table copy internally, there are two situations:
1) Existing rows with the same ID can change
2) Existing rows with the same ID can't change

In the last case a simple append query will work, just ignore the errors for duplicate inserts.

The first case is "harder" as the changed records cause the need for removing existing ID's. This can be done with a delete "WHERE EXISTS ()" construction query, to be executed before the INSERT.

Getting the idea ?

Nic;o)

[colintis]

Yes I get your point, but sadly the database source is set to be stored in the local computer that creates the records, on schedule it copy its current information to the network everyday. Sometime this copy process may corrupt the records inside the files, so I need to make a local copy to the access database and incase the source is showing me errors by its corruptions.

In your situations listed, the last case may most likely what I may take in action. For the INSERT statement, would this be enough the WHERE clause criteria should be taken?

WHERE ID NOT IN (SELECT * FROM A)

[nico5038]

Situation 1: Changed existing rows

DELETE * from B WHERE ID IN (SELECT ID FROM A)
next
INSERT ...

Situation 2: No change in existing rows:
Only an append without WHERE clause will do the trick.
You'll have to accept the "duplicates " error message. You could execute this INSERT query from code with the currentdb.execu te statement to suppress the message.

Just let me know how much faster this is :-)

Nic;o)

[colintis]

Thanks for that, I'll let you know when I get back to the office......in 3 days later...

When you say using the VBA to suppress the error, do you mean to place the continuing codes to the part that suppose to show the error details?

[nico5038]

Hmm, lost me.
For VBA you would create a button on a form and place the query in the OnClick event like:

' option 1
currentdb.execute ("qryDeleteDupes")
currentdb.execute ("qryInsert")

' option 2:
currentdb.execute ("qryInsertAppend")

Without an additional parameter in this command, Access won't show error messages or popups.

Nic;o)

[colintis]

The new method works a double faster, many thanks for your solution Nic ;)

[nico5038]

Glad I could help and success with your application !

Nico