VBA for preventing duplication during import

[cprobertson1]

Good morning folks!

I am currently developing a database that will be used to keep track of procedures and specifications for an engineering plant.

One of the features requested is the ability to import excel tables for a particular job, containing a list of the required procedures and specs - but I've ran into a problem.

Each job is tied to a contract number, so that if we get a repeat job - they just import the old job under a different contract number - simple right?

But the problem is I need to prevent importing the same job under the an existing contract number - meaning I need to prevent any entries that are an exact duplicate of a previous record - at the import stage.

Can anybody point me in the right direction?


Many thanks in anticipation!

NOTE: import is currently being handled by a DoCmd.TransferSpreadsheet function.

 

[NauticalGent]

Instead of trying a VBA fix (too labor intensive), why not try it in a two part process? You could import the excel sheet in its native format to a temp table (!) and then do an append query from that table. Only records that do not exist will be added/appended.

There are some on here to absolutely HATE the use of temp tables but if used in moderation, they have their uses...

 

[CJ_London]

There are some on here to absolutely HATE the use of temp tables

I use them all the time because they can be so flexible - e.g. lookup a lookupID from a description, multiple table updates from a single flat file etc. However I always create a temp db to put it in, then delete the temp db when done so I do not suffer from front end bloat

 

[cprobertson1]

Sounds like a plan!

I even have a temporary table already in use to add a couple of columns on and sanitise some data

Will that still work if access is adding a primary key to the main table? In fact, I'll try it just now and find out!

 

[NauticalGent]

Sounds good, and good luck. Let us know how it works and please mark this thread as "solved" if it does!

 

[arnelgp]

index on fields on your table with no duplicate, to prevent it.

 

[cprobertson1]

Nae luck!

When it gets appended to the main table, it just adds it on with a new index regardless of the presence of already-existing data.

The problem is there is no data that uniquely identifies a given part that is imported - the contract number and Purchase Order (PO) number is the same across an entire job - and parts within that PO may be the same (but under different branches of the part data-tree) meaning the part number can be duplicated as well.

Im currently working on an SQL INSERT INTO query to merge them while ignoring exact duplicates: any ideas where to start?

(I'm thinking SELECT INTO [all columns] WHERE NOT EXISTS (compare the columns)) - but that will mean writing out all the columns individually, and there are a lot of them...

--EDIT--
would probably be easier with a JOIN explicitly ignoring existing entries (but again, this will need to be done individually for each column unless I'm forgetting something!)

 

[Simon_MT]

You could try putting the excel spreadsheet functionality into Access?

Simon

 

[cprobertson1]

You could try putting the excel spreadsheet functionality into Access?

Simon

The problem is the excel data is imported from an external company's document control system - we have no control over it's content - we're importing it in order to keep track of dates (and more importantly, what state each of the flurry of documents bouncing back and forth between vendors and clients is at)

--EDIT--
Yeah, 25 column names that would all need typed out if I'm doing it using explicit SQL - is there a way to select all the column names using SQL itself? I know you can do it in other flavours of SQL - not so sure about access though)

 

[CJ_London]

(I'm thinking SELECT INTO [all columns] WHERE NOT EXISTS (compare the columns))

more something like


WHERE Destination.fld1<>Source.fld1 AND Destination.fld2<>Source.Fld2 etc

 

[cprobertson1]

I'm nearly there!

Having a slight problem with the SQL side of things now: for some reason it's not pulling the values through from table_CORE properly - access asks me to put the parameters in.

I reckon I'm missing something! Any ideas? :banghead:

[SIZE="1"](VBA)[/SIZE]

sql_str = "INSERT INTO [table_CORE] SELECT * FROM [table_IMPORT] WHERE " & _
            "table_CORE.[Supplier Number] <> table_IMPORT.[Supplier Number] AND " & _
            "table_CORE.[Supplier Name] <> table_IMPORT.[Supplier Name] AND " & _
            "table_CORE.[PO] <> table_IMPORT.[PO] AND " & _ etc etc etc

DoCmd.RunSQL (sql_str)

--edit--
its just the table_CORE values that aren't turning up correctly - the table_IMPORT values are fine)

Only difference between the two tables is that table_CORE has an ID column (autonumbered, primary key)

 

[CJ_London]

not much use without telling us what parameters are being asked for. but almost certainly you have a typo somewhere or with the SELECT * a field name is different

 

[cprobertson1]

Haha! Whoops!

Sorry, the parameters being asked for are the field names from table_CORE

field names between table_CORE and table_IMPORT are the same, except CORE has an extra ID field (primary key) - explicitly naming each field insteaed of using "*" gave the same result

 

[cprobertson1]

FIGURED IT OUT

Had to do some messy SQL to get it to work:

INSERT INTO table_CORE
SELECT *
FROM table_IMPORT
WHERE not exists(
	SELECT *
	FROM  table_CORE 
	WHERE 
	(
		NZ(table_CORE.[FMC Project Name],"EMPTY") = NZ(table_IMPORT.[FMC Project Name],"EMPTY") AND  
		
		NZ(table_CORE.[contract_no],"EMPTY") = NZ(table_IMPORT.[contract_no],"EMPTY"))
	);

that's the gist of it, the NZ() is to cover the blank entries that the client left in some of their records (since null = null returns false)

Thanks everybody!