DCount

[peljo via AccessMonster.com]

I am trying to build a code that compares two tables that should be identical.
If they are not identical, which means that the second table may contain less
or more products than the original table products, a warning
must be sent.
One of my efforts:
Dim lngCount As Long
lngCount = DCount("*", "products","products1",products.productid <> products1.
productid)
If lngCount <> 0 Then
MsgBox " The table Products1 does not correspond to the Table Products",
vbExclamation
End If
i am not sure whether this is the right method and also i have not written
the above code properly.I get the message "variable not defined"
Could you help me ?

 

[TC]

Do the two tables have the same fields, or different fields?

Are they only different if the number of records are different? Or
could they be different because they have the same # of records, but
some of the values /in/ those records are different?

Finally, why are you trying to do this anyway? What are you trying to
achieve from an end-user viewpoint? There may be a better way.

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[Douglas J. Steele]

Your DCount statement isn't correct. DCount only takes 3 arguments, and
you're passing it 4. As well, the WHERE portion of the statement
(products.productid <> products1.productid) needs to be a string.

If what you're trying to do is determine whether one table has fewer rows
than the other, you can use:

If DCount("*", "products") <> DCount("*", "products1") Then

 

[John Spencer]

Build a union query that looks like the following

SELECT Products.ProductID, "Products" as InTable
FROM Products Left JOIN Products1
ON Products.ProductID = Products1.ProductID
WHERE Products1.ProductID is Null
UNION ALL
SELECT Products1.ProductID, "Products1" as InTable
FROM Products1 Left JOIN Products
ON Products1.ProductID = Products.ProductID
WHERE Products.ProductID is Null

Save the query as qMissingProducts
Then you can use
DCount("*","qMissingProducts")
to return a count of the unmatched productid

You can also do this all in VBA using recordsets.

Warning: UNTESTED AIRCODE follows

Sub sMissing()
Dim DbAny as DAO.Database
Dim rstAny as DAO.Recordset
Dim strSQL as String

strSQL = "SELECT Products.ProductID, ""Products"" as InTable" & _
"FROM Products Left JOIN Products1" & _
"ON Products.ProductID = Products1.ProductID" & _
"WHERE Products1.ProductID is Null" & _
"UNION ALL" & _
"SELECT Products1.ProductID, ""Products1"" as InTable" & _
"FROM Products1 Left JOIN Products" & _
"ON Products1.ProductID = Products.ProductID" & _
"WHERE Products.ProductID is Null"

Set DbAny = CurrentDB()
Set rstAny = Dbany.OpenRecordset (StrSQL)

If rstAny.RecordCount >0 then
MsgBox "Missing Products"
End if
End Sub

If you only need to know that ProductIDs are missing from the Products table,
you can drop the Union All and the SELECT QUery following that.