Duplicate entries and calling query from Form (1 Viewer)

[jray9242]

I created a query that I thought would only add new records. I seem to work if I call the query standalone, but when I call it from the form, it adds all the records. I have included the SQL and the command I use to call the Query.

FName should be a unique name.

Thank you for the help.

QUERY:

INSERT INTO [Catalog] ( FName, FPath, FileLink )
SELECT CatalogImport.FName, CatalogImport.FPath, CatalogImport.FileLink
FROM CatalogImport LEFT JOIN [Catalog] ON Catalog.FName = CatalogImport.FName
WHERE Catalog.FName Is Null;

FORM:

DoCmd.OpenQuery "MergeData"

 

[sneuberg]

I don't see anything wrong with the query, i.e, I think it should only insert records into Catalog if they are not already there. Also I don't see any reason why using DoCmd.OpenQuery would cause it to operate differently

Could you upload your database so that we could take a closer look.

 

[jray9242]

Thanks for the help.

I am trying to upload the file but the system keeps telling me it failed. It is really small so I am not sure what the problem is.

Could be I am across the pond in the US.

I'll try again later.

Thanks again!

Jim

 

[jray9242]

Here is a link to the file.

Let me know if you get this ok.

"https://drive.google.com/file/d/0B2k_dZrEoNYQU05sbFBmckpQUGM/view?usp=sharing"

Jim

 

[sneuberg]

I got the file ok but I guess I don't understand the problem. The CatalogImport table was empty so I appended the records in the Catalog table to it. When I run the MergeData query either by itself or with the button it doesn't add any records to the Catalog table. It shouldn't as the records are the same. It's working as it is suppose to. Can you tell me what I should do to replicate the problem you are experiencing?

 

[sneuberg]

Here's some unsolicited information you may find informative

I noticed that there are duplicate FNames in the Catalog table already. If you run a query to get the distinct FNames like:

SELECT DISTINCT Catalog.FName
FROM [Catalog];

you get 1818 records out of 2012 records in the table. I also notice that the combination of FName and FPath defines unique records, i.e., the query

SELECT DISTINCT Catalog.FName, Catalog.FPath
FROM [Catalog];

produces 2012 records, i.e., there are no duplicate records when you consider both the FName and the FPath.

 

[jray9242]

If you select Create Catalog (select your folder to use). It will put the data into the Import then the Catalog table.

View the Catalog and add some notes.

Create the Catalog again. What happens is the Notes are no longer there which is the problem

If I run the query from the Query section, it works. This is where I am confused.

Jim

 

[sneuberg]

The ListFilesToTable procedure which is the first thing called when you click the button deletes everything from the Catalog table with this code found in the ajbFileList module, line 27

Dim DeleteEverything As String
DoCmd.SetWarnings False
DeleteEverything = "DELETE * FROM [Catalog]"
DoCmd.RunSQL DeleteEverything

Inherited this database from someone else?

 

[jray9242]

Steve,

Thank you for being patient with me. I haven't been good in explaining my code.


Call ListFilesToTable([Directory], , True) --- This calls the routine that creates the Import table

DoCmd.OpenQuery "MergeData" --- This calls the query that adds the data to Catalog. If ran again, it should omit dup files and not erase the Notes field in the Catalog table.

The next set of command will delete the Import table because I no longer need the data.
Dim DeleteEverything As String
DoCmd.SetWarnings False
DeleteEverything = "DELETE * FROM [CatalogImport]"
DoCmd.RunSQL DeleteEverything

As you can see, this is a Genealogy program keeps track of my information and information like photos are added to the folder.

Running the Create Catalog section adds the new information on my HD to the table.

I hope this helps better. It's a bummer getting old and not remembering how to do these things.

Thanks again.

Jim

 

[sneuberg]

Call ListFilesToTable([Directory], , True) --- This calls the routine that creates the Import table

It also deletes the catalog table. I suggest commenting out the lines I mentioned in my previous post and I think you'll find it works a lot more like what you want. At least the comments won't disappear.

 

[sneuberg]

Also now that I kind of see what this application does I think the information I provided in post 6 is important. In general since the same file name can exist in different folders the file names are not unique. Since the FPath includes both the folder(s) and file name it is unique. Since you can't form a join on a hyperlink field I suggest you add another text field to the tables to hold the FPath as text and change the MergeData append query to join on that field.

 

[jray9242]

I'll go back and review those parts and didn't know I had the DELETE code in there twice.

My bad..

Thank you and I will let you know how it goes.

Jim

 

[sneuberg]

I looked at it again and noticed that after it deletes the catelog table it starts doing inserts in the CatalogImport table. Maybe this code

Dim DeleteEverything As String
DoCmd.SetWarnings False
DeleteEverything = "DELETE * FROM [[COLOR="Red"]Catalog[/COLOR]]"
DoCmd.RunSQL DeleteEverything

should be

Dim DeleteEverything As String
DoCmd.SetWarnings False
DeleteEverything = "DELETE * FROM [[COLOR="red"]CatalogImport[/COLOR]]"
DoCmd.RunSQL DeleteEverything

That would make more sense.

 

[jray9242]

Steve.

Thank you!

It looks like it is working. I'll let you know more later.

Thanks again!

Jim

 

[jray9242]

Steve,

The program is now working PERFECT! Thank you so much for helping this old man with this. It turned out the extra code in my module was the problem.


One last question. When I run the query within the form, is there a way to not have the user be prompted with the screen, "You are about to run an append query...."?

I would like it to execute with having the user to press "Yes" or "No".

Thanks again!

Jim

 

[sneuberg]

I'm not see that but try adding

[COLOR="Blue"]DoCmd.SetWarnings False[/COLOR]
DoCmd.OpenQuery "MergeData", , acViewPreview
[COLOR="blue"]DoCmd.SetWarnings True[/COLOR]

 

[jray9242]

I found a setting under OPTIONS that shuts it off. Not sure if that is the best way, but will try yours as well.

My next query I am going to work on is the compare the 2 tables. If the file is in Catalog but not in Import, then delete the one in Catalog.

This has been a fun project and will be a work in progress for a while and thank you for you experts in helping us out.

I do appreciate your help!

Jim

 

[sneuberg]

My next query I am going to work on is the compare the 2 tables. If the file is in Catalog but not in Import, then delete the one in Catalog.

Please be aware that joins don't work in delete queries. At least I could never get one to work. The typical way to delete records in one table that are not in another is to put a subquery in a WHERE xxx Not In or WHERE NOT EXISTS. I suggest checking out the Delete unmatched records section of Allen Browne's web page on this.

 

[jray9242]

Thank you. I will check it out.

Jim

 

[jray9242]

Steve that is great site ana bookmarked it.

Now I need to learn how to read and display the IPTC metadata for the photo.

This one is going to take a bit of research I am sure.

Thanks again for all your help!

Jim