Some thoughts

[gtcoglan]

Your thoughts thanks

Hi All,

I am currently using some of the code from ghudson's browsing2.mdb for importing XLS files into a database that I making. After pressing the import button which triggers the importing of the file it imports the contents of the XLS into a table called import and into another table at the same time. Both the import table and the other table have the same structure. For the sake of argument lets call the other table "table B".

I want to know of a way of how during the import of the XLS it can flag with a message box to say that that the information already exists in table B, and exit out of it. I was thinking this would be done in VBA. I have written some part of it (very basic) and would appreciate someone's opinion if I am on the right track. It basically searches if the same data lies in the 2 tables and pops up a message box. I would welcome any other solutions, but I would prefer it to be VBA.

----Start of Code---------------------------------------------
Dim strCompare1, strCompare2, strAppend1 As String

strCompare1 = "Select table B.field A from table B where (select import.field A from import);"

strCompare1 = "select import.field A from import where (select table B.field A from table B);"

CurrentDb.Execute strCompare1
CurrentDb.Execute strCompare2

If strCompare1 <> strCompare2 Then
strAppend1 = "INSERT INTO table B ( field A, field B, field C) SELECT
Import.field A, Import.field B, Import.field C FROM Import;"
CurrentDb.Execute strAppend1
Else
MsgBox "Piece-Marks already exist in the database, so if it is an update
then use the update function"
End If

--------End of Code--------------------------------------------------

Cheers
Greg

 

[The_Doc_Man]

Conceptually, you are thinking correctly. I won't offer alternatives because I don't think you are on the wrong path here. The only details you need to worry about are that if the data exists in table B you cannot import to table B directly. You can import to your "import table" and then in the second stage import to table B UNLESS there is a potential duplication such as you describe.

The basic concept: Do whatever you can do monolithically. BUT when you need to make exceptions within the "monolith" then it is time to break it apart and do the pieces-parts separately.

Stated another way... "Divide and conquer." Break the problem into parts and attack the parts. It is far better than attacking the whole problem at one time.