Synchronise tables

[Michael Thomas]

Hi everyone

Is there any way to compare tables and automatically update values. I
import a list of products with associated cost prices and selling prices
from an external database into a table called Products which is then linked
to other tables as required. The problem is that the external database
changes on a regular basis. If a product is added today, I can then import
the new database into a temporary table called ProductsWork and compare it
with Products. Any records that are in ProductsWork that aren't yet in
Products will then be added to Products. This is done using the following
query:

INSERT INTO Products ( StockCode, Description, MajDeptID, CostPrice,
SellingPrice )
SELECT ProductsWorkTable.StockCode AS Expr1, ProductsWorkTable.Description
AS Expr2, ProductsWorkTable.MajDeptID AS Expr3, ProductsWorkTable.CostPrice
AS Expr4, ProductsWorkTable.SellingPrice AS Expr5
FROM ProductsWorkTable
WHERE ((([ProductsWorkTable].[StockCode]) Not In (SELECT StockCode from
Products)));

The problem that I'm having is when products are deleted from the main
database. The above query only adds records that weren't found in Products
but which were found in ProductsWork. It doesn't delete records that are in
Products but NOT in ProductsWork. Also, if one of the non-primary key
fields changes (for instance the cost price of one of the products changes)
is there any way for it to update the cost price in Products.

Basically, I'd like to synchronise the two tables in every way, whether it
means adding records, deleting records, making fields identical, whatever.

Any help will be much appreciated.

Thank you in advance,

Michael Thomas
Stock Controller
The Cock 'n Bull cc
Cape Town
South Africa

[Rick Collard]

Michael,

I do something quite similar in archiving software for one of our
products. This does require some vba but it boils down to a three
step process.

1. Synchronize existing records by opening a recordset in VBA that
does a join on the two tables:

SELECT ProductsWorkTable.*, Products.*
FROM ProductsWorkTable INNER JOIN Products ON
ProductsWorkTable.StockCode = Products.StockCode;

For each record in the recordset the VBA code iterates through each
field and if the field name does not include "WorkTable." you assign
the value from the same named field in ProductsWorkTable to this
field. I am simplifying a bit here but that's the basic idea.

2. Append new records that don't exist in Products by executing the
SQL statement you already have.

3. Using another SQL statement, delete the records in Products that
don't exists in ProductsWorkTable:

DELETE FROM Products WHERE StockCode Not In (SELECT StockCode FROM
ProductsWorkTable);

Our software does not do step three since we are archiving data but
the first two steps make sure everything from one table exists in a
second and all records are synchronized. HTH

Rick Collard
www.msc-lims.com

On Tue, 31 Aug 2004 10:45:05 +0200, "Michael Thomas"
<mi*****@cocknbull.co.za> wrote:

Hi everyone

Is there any way to compare tables and automatically update values. I
import a list of products with associated cost prices and selling prices
from an external database into a table called Products which is then linked
to other tables as required. The problem is that the external database
changes on a regular basis. If a product is added today, I can then import
the new database into a temporary table called ProductsWork and compare it
with Products. Any records that are in ProductsWork that aren't yet in
Products will then be added to Products. This is done using the following
query:

INSERT INTO Products ( StockCode, Description, MajDeptID, CostPrice,
SellingPrice )
SELECT ProductsWorkTable.StockCode AS Expr1, ProductsWorkTable.Description
AS Expr2, ProductsWorkTable.MajDeptID AS Expr3, ProductsWorkTable.CostPrice
AS Expr4, ProductsWorkTable.SellingPrice AS Expr5
FROM ProductsWorkTable
WHERE ((([ProductsWorkTable].[StockCode]) Not In (SELECT StockCode from
Products)));

The problem that I'm having is when products are deleted from the main
database. The above query only adds records that weren't found in Products
but which were found in ProductsWork. It doesn't delete records that are in
Products but NOT in ProductsWork. Also, if one of the non-primary key
fields changes (for instance the cost price of one of the products changes)
is there any way for it to update the cost price in Products.

Basically, I'd like to synchronise the two tables in every way, whether it
means adding records, deleting records, making fields identical, whatever.

Any help will be much appreciated.

Thank you in advance,

Michael Thomas
Stock Controller
The Cock 'n Bull cc
Cape Town
South Africa

[Michael Thomas]

Hi

Thanks for the help, everything worked fine except for the fact that I'm not
sure what VBA code to use? Adding and deleting the products are perfect,
and when I run the SQL statement:

SELECT ProductsWorkTable.*, Products.*
FROM ProductsWorkTable INNER JOIN Products ON
ProductsWorkTable.StockCode = Products.StockCode;

to perform the inner join Access 2002 brings up the window listing both
Tables in one with the related fields in one row. How do I run VBA code to
then work on those fields (or iterate through them as you said?) Could you
point me somewhere to find VBA code that could help me do this, or would I
have to learn VBA?

Thanks,
Michael
"Rick Collard" <no****@nospam.nospam> wrote in message
news:41****************@news.west.earthlink.net...

Michael,

I do something quite similar in archiving software for one of our
products. This does require some vba but it boils down to a three
step process.

1. Synchronize existing records by opening a recordset in VBA that
does a join on the two tables:

SELECT ProductsWorkTable.*, Products.*
FROM ProductsWorkTable INNER JOIN Products ON
ProductsWorkTable.StockCode = Products.StockCode;

For each record in the recordset the VBA code iterates through each
field and if the field name does not include "WorkTable." you assign
the value from the same named field in ProductsWorkTable to this
field. I am simplifying a bit here but that's the basic idea.

2. Append new records that don't exist in Products by executing the
SQL statement you already have.

3. Using another SQL statement, delete the records in Products that
don't exists in ProductsWorkTable:

DELETE FROM Products WHERE StockCode Not In (SELECT StockCode FROM
ProductsWorkTable);

Our software does not do step three since we are archiving data but
the first two steps make sure everything from one table exists in a
second and all records are synchronized. HTH

Rick Collard
www.msc-lims.com

On Tue, 31 Aug 2004 10:45:05 +0200, "Michael Thomas"
<mi*****@cocknbull.co.za> wrote:

Hi everyone

Is there any way to compare tables and automatically update values. I
import a list of products with associated cost prices and selling prices
from an external database into a table called Products which is then linkedto other tables as required. The problem is that the external database
changes on a regular basis. If a product is added today, I can then importthe new database into a temporary table called ProductsWork and compare itwith Products. Any records that are in ProductsWork that aren't yet in
Products will then be added to Products. This is done using the followingquery:

INSERT INTO Products ( StockCode, Description, MajDeptID, CostPrice,
SellingPrice )
SELECT ProductsWorkTable.StockCode AS Expr1, ProductsWorkTable.DescriptionAS Expr2, ProductsWorkTable.MajDeptID AS Expr3, ProductsWorkTable.CostPriceAS Expr4, ProductsWorkTable.SellingPrice AS Expr5
FROM ProductsWorkTable
WHERE ((([ProductsWorkTable].[StockCode]) Not In (SELECT StockCode from
Products)));

The problem that I'm having is when products are deleted from the main
database. The above query only adds records that weren't found in Productsbut which were found in ProductsWork. It doesn't delete records that are inProducts but NOT in ProductsWork. Also, if one of the non-primary key
fields changes (for instance the cost price of one of the products changes)is there any way for it to update the cost price in Products.

Basically, I'd like to synchronise the two tables in every way, whether itmeans adding records, deleting records, making fields identical, whatever.
Any help will be much appreciated.

Thank you in advance,

Michael Thomas
Stock Controller
The Cock 'n Bull cc
Cape Town
South Africa

[Rick Collard]

Michael,

You will need to learn a little VBA to make this work so now is a good
time to dive in. The developer's/programmer's guides that come with
Office/Access are a good place to start.

To get you started, open a new module in your database then paste the
following code (watch for line wraps):
Public Sub SynchronizeProducts()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim sSQL As String, sField As String
Dim i As Long

' Step 1: Synchronize existing records
sSQL = "SELECT ProductsWorkTable.*, Products.* " & _
"FROM ProductsWorkTable INNER JOIN Products ON " & _
"ProductsWorkTable.StockCode = Products.StockCode;"

Set db = DBEngine.Workspaces(0).Databases(0)
Set rs = db.OpenRecordset(sSQL, dbOpenDynaset)
Do Until rs.EOF
rs.Edit
For i = 0 To rs.Fields.Count - 1
Set fld = rs.Fields(i)
If InStr(fld.Name, "ProductsWorkTable.") Then
sField = Right$(fld.Name, Len(fld.Name) _
- Len("ProductsWorkTable."))
rs("Products." & sField) = rs(fld.Name)
End If
Next i
rs.Update
rs.MoveNext
Loop
rs.Close

' Step 2: Append new records
sSQL = "INSERT INTO Products ( StockCode, ..."
db.Execute sSQL

' Step 3: Delete old record
sSQL = "DELETE FROM Products WHERE StockCode Not In " & _
"(SELECT StockCode FROM ProductsWorkTable);"
db.Execute sSQL

End Sub

Below the step 2 comment you will need to complete the INSERT INTO
statement with the one you have already used to add new product
records. This is just air code so you will have to debug and test.
To run the procedure just open the immediate window in the VBA editor
and type "SynchronizeProducts" without the quotes and hit enter.

You can save the module then whenever you need to run the procedure
just open the VBA editor and enter "SynchronizeProducts" in the
immediate window. Later you may want to give yourself a better user
interface. For example, you could have a button on a form invoke the
SynchronizeProducts procedure. HTH

Rick Collard
www.msc-lims.com

On Thu, 2 Sep 2004 11:20:07 +0200, "Michael Thomas"
<mi*****@cocknbull.co.za> wrote:

Hi

Thanks for the help, everything worked fine except for the fact that I'm not
sure what VBA code to use? Adding and deleting the products are perfect,
and when I run the SQL statement:

SELECT ProductsWorkTable.*, Products.*
FROM ProductsWorkTable INNER JOIN Products ON
ProductsWorkTable.StockCode = Products.StockCode;

to perform the inner join Access 2002 brings up the window listing both
Tables in one with the related fields in one row. How do I run VBA code to
then work on those fields (or iterate through them as you said?) Could you
point me somewhere to find VBA code that could help me do this, or would I
have to learn VBA?

Thanks,
Michael