ado problems

[garethl]

thanks for that - at least now i understand what is going on

parameters resolved through literal string concatenation is the way i have pretty much gone up until now and it is now that i have a fairly complex set of stacked queries that this option becomes not available to me, queries lower down in the stack have to pull information from the form and so i can't just store the data in a variable or take straight from the form and use literal string concatenation

i think from now on i will avoid ado / dao whenever possible, i am also having a look at my design to see how it can be improved

unfortunatly this particular bit where i loop through a recordset building other queries cannot really be avoided as far as i can see, these are the make table queries

possibly they don't need to be make table queries, the end result is to export excel files (and this definitly definitly needs to happen it is one of the main purposes of the db) but there are other ways of making excel files - whatever happens though i'd still need to build some sort of query and its still going to be based on a stack of other queries

where i may well be able to improve the design however is to fundementally change the way in which the data is imported so that (hopefully) i can do all this processing in a single sql statement removing the need for the stacked queries, then my problem is solved.. back to literal string concatenation

i will have a look at the db and post my proposed change see what you think, i know what i intend to do pretty much but just to keep the length of this post down as much as anything else

 

[garethl]

ok so what i propose is this, quick description of whats happening first

we recieve excel files where each line represents a job done by a contractor and the work gets loaded into one big table of jobs done but first it is loaded to a temporary table

while the data is in the temporary table various string preprocessing operations are performed, then a duplicate check is carried out, a report is produced on the data (which displays counts of different types of jobs, the sum of the monetary value to check against the money they have claimed... stuff like that) and the user is prompted by another form to decide if they want to accept, reject or ammend the data

i realise duplicate checking is not the most complicated operation but it is complicated slightly in our case by the need for the duplicate check to be a check only of duplicates that relate to the current list of jobs being considered for accepting into the database - that is one half of a pair of duplpicates must exist in that list for it to be a valid duplicate

also we are not checking whole lines of data, we build a 'DupeCode' from three of the fields and search for duplication of that code, so i have the following queries (this is the stack i'm talking about)

- DupeCodes for the data already in database (the big table we append to)
- DupeCodes for the new data being considered (the temp table)
- A union all which links these first too together
- A query which searches the list produced by the union all to give those codes which occur more than once (i.e. the duplicated codes)


this next bit is probably really bad design!

- A query which pulls all the fields in for all the records in both tables (including the DupeCode)
- Finally a query which uses a left join from the query of duplicated codes to this last one to just show whole lines of data for duplicated jobs

i realise now that i didn't need the first two the second to last one there would do

anyway the change i propose making is that there is no need to leave the data in the temp table to perform the duplicate check - what i could do is do all my string preprocessing etc. in the temp table, append straight away but mark the records which are still 'pending' with an extra yes/no field in that table making them easy to remove should they be rejected and then i can just have one simple query searching for duplicates in that that table

the only thing is while it would be easy to just use access's find duplicates wizard to create a bog standard duplicate query i'm not sure how i would work implement the constraint that one half of each duplicate must be in the new submission - perhaps just produce the duplicates and then run another query on those results? to avoid stacking queries again i could use a temporary table or just make sure there is nothing being pulled from the form and then stack them anyway