Automated Query

[Rick Deemer]

I have to implement some tables for our shipping department. I know the
table that holds the shipping data in our system, which will be pulled from
the system using ODBC, but I have a few problems in figuring out the best
way to accomplish this task.

My problem is we use 2 distinct companies for our orders. So each company
will have a unique table containing the data for shipping their orders but
we have only one shipping department. What I would like to do is to make
copies of the tables, then merge them to be one single table. Next, copy
this merged table to an accessible place so that when the order comes
through, it can be scanned with a barcode reader, which will pull up the
shipping information, and the delivery information will be populated. The
additional issue with all of this is that because orders are constantly
being added to the system, I need to have a way of automating this so that
it runs on a fairly quick schedule approximately every 30 minutes or so.
Can this be done?

 

[Ed Robichaud]

Well, you could use a hidden/transparent form and set its timer interval to
10000 (every 10 seconds) and put code in the OnTimer event to run your make
table query. Or you could use the OnActivate event (every time you return
to that form) of your main shipping form to re-run that query, then refresh
the form.

One problem with temp tables, as you've noted, is their currency. Could you
not just use a query to get both data sources and work directly in that?
-Ed

 

[John Vinson]

I have to implement some tables for our shipping department. I know the
table that holds the shipping data in our system, which will be pulled from
the system using ODBC, but I have a few problems in figuring out the best
way to accomplish this task.

My problem is we use 2 distinct companies for our orders. So each company
will have a unique table containing the data for shipping their orders but
we have only one shipping department. What I would like to do is to make
copies of the tables, then merge them to be one single table. Next, copy
this merged table to an accessible place so that when the order comes
through, it can be scanned with a barcode reader, which will pull up the
shipping information, and the delivery information will be populated. The
additional issue with all of this is that because orders are constantly
being added to the system, I need to have a way of automating this so that
it runs on a fairly quick schedule approximately every 30 minutes or so.
Can this be done?

As Ed suggests, your proposal of copying data from both tables into a
third table has some major concurrency problems (not to mention
validation problems if one of the source tables should be briefly
unavailable!)

Can you *LINK* to the two tables, using ODBC say, and create a UNION
query to combine them into one (non-updateable but current) recordset?
This will let you pull up the current shipping information from either
source, with confidence that it's up to date and without the very
major overhead of repeated MakeTable queries.

John W. Vinson[MVP]