Emulate the QBE for Append Query

[NauticalGent]

Good morning everyone,

I have a situation where the input file format we receive changes periodically (column names, specifically). I discovered this when my saved import was putting the wrong data in the wrong columns.

I really want the user to be able to import the file, although it would be degree of job-security if I told them there was no way to achieve this unless I did it. My thought process on this would be to loop through both tables' fields and then populate a form that allows the user to match the import field to the destination field and then build the SQL statement from that.

Seems like a real PITA, but once it is done then that should be the extent of it. Before I went down that road, I wanted to ask you all your thoughts and maybe have a better solution...

Hit me...

 

[MajP]

Take a look at this demo. I think it is pretty close to allowing you to find matching fields and then map non matching.

More than 15 left joins for matching rows

@Jarek The famous quote by Donald Rumsfeld (US Secretary of State under George Dubya Bush) may come in useful here: As we know, there are known knowns; there are things we know we know. We also know there are known unknowns; that is to say we know there are some things we do not know. But...

www.access-programmers.co.uk

 

[NauticalGent]

Take a look at this demo. I think it is pretty close to allowing you to find matching fields and then map non matching.

More than 15 left joins for matching rows

@Jarek The famous quote by Donald Rumsfeld (US Secretary of State under George Dubya Bush) may come in useful here: As we know, there are known knowns; there are things we know we know. We also know there are known unknowns; that is to say we know there are some things we do not know. But...

www.access-programmers.co.uk

Thanks MajP, I am busier than a one-armed paperhanger right now but will take a look as soon as I get a chance...

 

[sxschech]

Here is a demo of how to work with the QBE to graphically map fields for append queries. It is in a zip file that includes instructions on how to use.

 

[CJ_London]

@sxschech - neat

 

[CJ_London]

My thought process on this would be to loop through both tables' fields and then populate a form that allows the user to match the import field to the destination field and then build the SQL statement from that.

What I use is two listboxes each populated with a field list.

Select a field from each list and click a button. Code then inspects the tabledef field properties to ensure they match (integer to long OK, long to integer maybe not) Also checks the destination has not already been assigned a value. If OK then updates a simple list (another listbox) of fields matched.

When all done, I save the matches to a table and the query is then built on the fly when required.

edit: the other trick if it is just column headers that change and the order and number of columns doesn't, is to import without headers. You will then get default headers of F1, F2, F3 etc