Go Back   Access World Forums > Microsoft Access Discussion > Macros

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-10-2018, 09:39 AM   #1
stell
Newly Registered User
 
Join Date: Jun 2017
Posts: 11
Thanks: 1
Thanked 0 Times in 0 Posts
stell is on a distinguished road
Need a macro to import from excel but replace existing entries

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.

stell is offline   Reply With Quote
Old 01-10-2018, 10:03 AM   #2
ridders
AWF VIP
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 4,842
Thanks: 73
Thanked 1,194 Times in 1,115 Posts
ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough
Re: Need a macro to import from excel but replace existing entries

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...d.php?t=297727
__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left and leave a comment.

New example databases:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
ridders is offline   Reply With Quote
Old 01-10-2018, 10:57 AM   #3
stell
Newly Registered User
 
Join Date: Jun 2017
Posts: 11
Thanks: 1
Thanked 0 Times in 0 Posts
stell is on a distinguished road
Re: Need a macro to import from excel but replace existing entries

Quote:
Originally Posted by ridders View Post
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.

stell is offline   Reply With Quote
Old 01-10-2018, 11:21 AM   #4
ridders
AWF VIP
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 4,842
Thanks: 73
Thanked 1,194 Times in 1,115 Posts
ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough
Re: Need a macro to import from excel but replace existing entries

Quote:
Originally Posted by stell View Post
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
Attached Images
File Type: png Capture.PNG (19.2 KB, 117 views)
File Type: png Capture2.PNG (13.5 KB, 109 views)

__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left and leave a comment.

New example databases:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
ridders is offline   Reply With Quote
Reply

Tags
delete query , duplicates , excel , macro , macro builder

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to import from excel to existing table in access abodayehia Modules & VBA 3 06-24-2014 03:00 AM
Import Excel to existing table without duplicates cathalfarrell Tables 4 10-12-2010 07:20 PM
Macro to export access data to existing excel worksheet PRodgers4284 Macros 6 10-12-2009 04:14 PM
Updating Existing Records from Excel Import veraloopy Queries 1 09-03-2009 11:50 PM
Excel Import and Replace Data in Table andy_dyer Modules & VBA 14 04-28-2009 09:33 AM




All times are GMT -8. The time now is 03:25 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World