Something awry this way comes?
but first a few concrete answers:
Always inclde the data sources in your relationships. Even if these are foregin sources such as linked tables or spreadsheets.
The " ... too complex parameters ... " simply means the update queries are poorly implemented - this (in turn) may imply the tables they were intended to update are also poorly designed, so a careful study of the overall study of the data structures needs to be an early (first?) step.
The only sensible difference between the .MDB and .MDE issues iw thre access to the objects designs. Saving as a .MDE only increases the opportunity for future frustration (and possible carrear altering confrontations).
By implication (this is where I depart from the "concrete") you are tracking inventory in a batch mode and hoping / expecting to convert this to an interactive or real time mode. If this is correct, you omit a centrial and crucial itemm: when/where/who/how does the actual (e.g. local to the product location) does the inventory get updated and what access do you have to this information?is this real time, on line continious; occassional as in event triggered? occassional as in when either you or the local system chooses?
More implication - there is some place that the individual inventories are maintained, and you are charged wigh the consolidation, but have only read access to these "sub stores". This would seen to imply you do not need to actually have "tables" for that data at all, as properly designed union queries can easily produce the data. Even if you "need" to actually consolidate the (sub) inventories, the (propsely designed) union queries can easily provide the recordsource for the delete/update/append queries to populate any local tables.
Inventory processing is -in my opinion- one of the more challenging aspects of database work. I have resigned myself to the torture of maintaining these three times and struggled with different aspects on each occassion. Particularly in 'retail', there are numerous pitfalls mostly in regard to the nature of human foibles. "Product" gets lost -and occassionally found. Pricing will vary by situation, location and other factors sometimes within a very short time span. There -and many more variables- need to be tracked to come 'close' to a real inventory. To do this has gotten me to desire and implement the transaction log which records each change to the database, including who changed what (at the table/field level) and whenthey changed it. Ms. A. (Jet db) does not have record triggers, so there is an extra challenge here requiring a "secured" database which permits access to data ONLY through forms (use of the dirty flag to detect changes). It would seem like you don't have this level of control over the actual inventory, so your task will be somewhat more complicated, but you can at least consider the concept and perhaps discuss it with those who do control the point of sale system(s).
Central to the issue of inventory is the concept of tracking. When item(s) are lost, there is a "hit" on the financial side (cost of goods sold does not increase, and the cost of inventory does does not decrease, so margin goes down -which is a BAD thing!!!).
Destroying/REcreating the inventory may omit some of the details which the financial side need to track, so be careful of doing it this way, and if you do go down this path, make sure the finance group is aware of what you are doing and prepared to audit the results and (formally) agree to the data preperation form their perspective during the transition.
You mention that your inventory tracking database is " ... an absoloute monster ... ". "Monster" generically implies a lack of familarity. You ask some 'generically' vague questions and supply few details. You do not mention the specific monster attributes. It is ... large? complex? divese geographically? require tracking at some extereme level of detail? just plain "ugly"? etc. How many persistient recordsets are involved in the current implementation? Does the overall database include historical (transaction) data? Is the databse split? How many objects (according to their type) are there? What is the physical size (bytes) of the overall database? Of the part you are working with? ...
MichaelRed
