network temporary table

[Guest]

All the tables are on the server's BE.
Each user has a copy of the FE.

I'm using the following queries to get a value (table 1) based on the
currect form's InventoryID

DoCmd.OpenQuery "InventoryResultEmptyTable"
To empty the results table -> table 1

DoCmd.OpenQuery "ReceivedInventory"
To get the quantity/location received and append to table 1

DoCmd.OpenQuery "ShippedInventory"
to get the quantity/location shipped and append to table 1

It works fine, but if two users run the queries at the same time, the result
is not correct.

Should I put table 1 with the FE of each user?

Thank you for your help!

 

[John W. Vinson]

All the tables are on the server's BE.
Each user has a copy of the FE.

I'm using the following queries to get a value (table 1) based on the
currect form's InventoryID

DoCmd.OpenQuery "InventoryResultEmptyTable"
To empty the results table -> table 1

DoCmd.OpenQuery "ReceivedInventory"
To get the quantity/location received and append to table 1

DoCmd.OpenQuery "ShippedInventory"
to get the quantity/location shipped and append to table 1

It works fine, but if two users run the queries at the same time, the result
is not correct.

Should I put table 1 with the FE of each user?

Thank you for your help!

Two suggestions:

1. If Table1 is indeed specific to the user, then yes... each user must have
their own copy.

2. Don't copy the ReceivedInventory into Table1 AT ALL. Just use a shared
inventory table containing all of the inventory and append data into it.

It's only rarely necessary or appropriate to create new tables just because
there is new data! Why do you feel that you need to store the "results" in a
table, rather than using a Select Query to display a subset of the data?

John W. Vinson [MVP]

 

[Guest]

Hi,

In the ideal world, you don't use temporary tables. You use a select query
to gather up the records as needed for forms and reports.

But few of us live in the ideal world! In your situation, I would put the
InventoryResultEmptyTable in the FE. Like all temp tables, it could cause a
little probably with bloat, but I wouldn't worry too much about it.

 

[Guest]

Thank you!

The reason is:
I have a received table.
I have a shipped table.
I don't store the quantity on hand. I get it when ever I need it.

The same item could be stored in a few locations and it could be shipped
from different locations.

First I empty the Results table.

My Received query gets the received items in "groups" (locations) and
appends the results to the Results Table.

My Shipped query gets the shipped items in "groups" (locations) and appends
the results to the Resutls Table.

Then I have to show in my form how many items (linked by the ItemID on the
current form) I have and the location where they are. I'm getting this info
from my Results Table -> Received (-) Shipped by location.

I appreciate any suggestion about doing it in another way.

 

[Guest]

Thanks a lot!

Hi,

In the ideal world, you don't use temporary tables. You use a select query
to gather up the records as needed for forms and reports.

But few of us live in the ideal world! In your situation, I would put the
InventoryResultEmptyTable in the FE. Like all temp tables, it could cause a
little probably with bloat, but I wouldn't worry too much about it.