Hi. I am working on a particular sql statemtent that looks like this, but I simplified it greatly.
x="INSERT INTO TableA (T1.Key,T1.Field1,T2.Field1,T2.Field2,T3.Field1,T3.Field2) FROM (T2 RIGHT JOIN T1 on T2.Key = T1.Key) LEFT JOIN T3 ON T1.Key = T3.Key"
That is how Access created the statement. All 3 tables have the Key field. This works in Access, but I am unable to get it to perform in VB6.
Details: Key is unique in Table 1, which is the source for T2 & T3.
Table 1 holds a product list, with an autonumber for Key. The key cannot repeat. Pulling product descriptions from this table.
Table 2 holds purchase orders for those keys. The key can repeat. Pulling 'order qty' from this table.
Table 3 holds prices from 3 tables (inserted) for 3 vendors catalogs.
Table A is the place I am wishing to store every entry from Table 1 (product list), possibly with each Key listed more than once due to Table 2 (orders) having more than one instance of a Key, and on each record, corresponding to the Key, the current price, which comes from Table 3.
The reason for this is to create one table to do many different selects from for reports/viewing inventory & availabilities. I am forced to segregate the actual data from each vendor into seperate tables, which is fine. But mulitple selects into many different tables is proving to be much too slow. Thus the desire to merge the data to one table that is deleted and repopulated on data changes.
I have restructured this select into VB in lot's of different manners, but I still get the same error. Error is -
-2147217904 - No value given for one or more required parameters.
I have not found the answer on the web yet. I think it is in the nesting. But I have not found why.