Need a macro to import from excel but replace existing entries (1 Viewer)

stell

Registered User.
Local time
Today, 08:42
Joined
Jun 14, 2017
Messages
15
I am using an excel file to run queries on my database. I originally had it as a linked table, but when you split the database it won't allow people to run queries at the same time because of the excel file.

So I am trying to make a macro using the 'ImportExportSpreadsheet' macro and putting the data into a local table instead. I feel like this should work, but every time I do it re-adds the data to the table and I end up with double the amount of entries (duplicates). I need to do the import every time because the data in the excel file is constantly changing.

My question is, is there is a way to create a macro that will import a spreadsheet each time a query is run and avoid getting massive amounts of duplicates. My first thought was to add a delete query to the macro and just immediately delete the duplicates but I wasn't sure how to go about that.

Thanks for reading, and thanks for any help.
 

isladogs

MVP / VIP
Local time
Today, 15:42
Joined
Jan 14, 2017
Messages
18,186
If you have several people using your database, the database should be split with a common back end and each person having their own copy of the front end.

If you import the Excel file to a local table in the FE, it will not be available to other users. Therefore the table should be in the BE database

You should use the unmatched query wizard to ensure records are only added that are not already in the table.

NOTE: If your database isn't split, you WILL have problems with corruption at some point

EDIT: I've just seen that this thread is a near duplicate of another by the OP in which it is stated to be a split database. Why have you started a second thread?
https://www.access-programmers.co.uk/forums/showthread.php?t=297727
 

stell

Registered User.
Local time
Today, 08:42
Joined
Jun 14, 2017
Messages
15
EDIT: I've just seen that this thread is a near duplicate of another by the OP in which it is stated to be a split database. Why have you started a second thread?
https://www.access-programmers.co.uk/forums/showthread.php?t=297727


Sorry for posting similar, It's kind of an important tool we use at work and I figured this section might have more activity than the excel section (where I haven't had much luck). I did mention this was a split database in the first paragraph.

So is the idea to use the matched query to use for a delete query and then delete the duplicates?

Thanks for your help.
 

isladogs

MVP / VIP
Local time
Today, 15:42
Joined
Jan 14, 2017
Messages
18,186
So is the idea to use the matched query to use for a delete query and then delete the duplicates?

It would have been better still in the query section!
I hadn't noticed it was in Macros (which isn't really relevant!)

No - the unmatched query wizard creates a SELECT query showing records in one table that don't exist in the other.
To use this, click Create ...Query Wizard ... Find Unmatched Query Wizard

Choose your source & destination tables & select the fields to be imported.
Identify a matching field in both tables which is used to check for matched records.
The result will look something like this in the query designer:



Once you have this, change it to an APPEND query (Design ... Append)
and select the destination table name.
The design will now look similar to this



IMPORTANT - you MUST remove the duplicate field name with the IsNull criterion as shown

Run the query to add new records without any duplicates
 

Attachments

  • Capture.PNG
    Capture.PNG
    19.2 KB · Views: 916
  • Capture2.PNG
    Capture2.PNG
    13.5 KB · Views: 770

Users who are viewing this thread

Top Bottom