vba equivalent of converting select query to append query like clicking append query button 1

[sxschech]

Have put together various query templates for appending data that is imported. User now is requesting to have the query prepopulated with all the fields from the import table, rather than have to predefine or drag them onto the grid. I can through vba create a select statement that lists all the fields, but haven't figured out the equivalent of clicking on the (green +) Append Query button in design view which automatically attempts to map the fields to the destination table. Short of building the query as a select query, opening in design view and telling the user to click the Append query button, can this be done or do I need to know all the fields ahead of time in order to do this as there won't be a 1 to 1 match, meaning that not all fields in the source table are in the destination table as well as the fact that the field names may differ.

 

[MajP]

If the goal is to append the records from a source table to a destination table where the source table has a subset of fields of the destination table, i think I would simply use DAO or ADO and use recordsets. You could easily populate the destination table where the source table fields map (same name) without knowing the fields ahead of time. But you say the names are different. So do you know ahead of time which fields map to the destination table? Does this ever change? If you have an example that would help.

 

[sxschech]

I am using recordsets where possible and have an elaborate vba set up that iterates through the fields to map data exceptions by bringing up the query and allowing the user to edit it design view, but in the above situation, the user is "bypassing the templates" because it doesn't fit the standard and may be a one time import. Even though I don't know all the fields that are coming in, I can build the standard select statement by extracting the field names from the table, what I can't figure is how to write out the rest of the statement for the portion that will be for the append without the magic of the button on query design. We already have many template queries set up that are mapped, but as data come from multiple outside vendors, can't always predict what we will get and can't tell them how to send their data to us. Source fields are usually not named properly which is why we import them into a "universal format" table. Examples: if the field name is one of these "Company Name" "Co" "CompanyName" "Company_Name" "Vendor Company" would be mapped to Company. The user is ok with doing some manual mapping on the query grid, it is just that would like to get the query in the right format for editing. The reason this came up is my original method (in use for the past year) was to create and display an append query with the one known field that is always available and then they would need to manually click or drag fields onto the grid, but now they would rather have all the fields displayed on the grid and only remove the ones not needed.

 

[MajP]

The runcommand constant to change a select query to an append query is
acCmdQueryTypeAppend

http://access.mvps.org/access/RunCommand/list/listq.htm

Not sure if that is what you are looking for. I think I would build my own gui to first match the fields with the same name and then allow the user to possibly match the fields that are in the source but not in the destination.

 

[sxschech]

Thanks for the tip/link. Tried it out and also searched on that term and so far, can't seem to figure out how to programmatically provide the table name ahead of time or pass the table name into the Append popup in order to avoid the popup altogether as this kind of reduces its usefulness since user may type in the wrong table name.

 

[MajP]

I still think building an interface to do this may be the easiest approach.

 

[sxschech]

I'd like to build an interface, but now exactly sure how that would be. Would I create a table of field names and then a continuous form displaying all the fields on the left and a combo box of all the fields in the destination table on the right and the user would then click on combo to map the fields? But how to avoid user from picking the same field twice, cascading combo (can that cascade in a continuous form)?

Anyway, here is the code I got so far...

I told them for now, will have to do a paste and click ok or press enter.

Dim stsql As String
Dim sthasdata As String

sthasdata = ColHasData(me.cboTableName)

stsql = "SELECT " & sthasdata & " " & _
        "FROM ALL_FIELDS;"
Set qd = EditQryDef("qryTEMPLATE_ALL_FIELDS")
qd.SQL = stsql
CopyToClip ("tblImport")
DoCmd.OpenQuery "qryTEMPLATE_ALL_FIELDS", acViewDesign

DoCmd.RunCommand acCmdQueryTypeAppend
'For now, instruct user to do a paste (ctrl+v; shift+insert; right click + paste)
'and press OK

'Below didn't work since code is halted until
'dialog box is closed
'Tried:
    'PasteFromClip ("tblImport")
'Also Tried:
    'SendKeys ("^v")
    'SendKeys ("~")  method

End Sub

 

[MajP]

Maybe something like this. Have a form with 3 listboxes. Listbox 1 lists all the fields from destination table, listbox 2 lists all the fields from source table. Listbox three has 2 columns with the matched fields (destnation field on left and source field on the right).

1. Have a means to select source and destination tables
2. Populate list 1 and list 2
4. Automatically pre-populate the 3rd listbox with names that match from the source and destination
Fields added to list three are removed from possible choices in list 1 and list 2
5. To create a new match the user clicks a field in List 1 and adds it to list 3, then clicks on list 2 and adds it to list three next to the previously selected value from list 1. When you click on a value from list 1 or 2 and add it to list three that value gets removed from list 1 or 2.
6. Now you would also need a way to break an incorrect association. This could be a double click in list three. Removing that row from list three and putting the choices back into list 1 and list 2.
7. I would also use DAO to check the datatype of the association. If the user tries to associate a numeric to text then pop up a warning.

This could be done a different way using a table to store the values. In this approach you would have a continuous form with two fields. First field would be displayed in a textbox the second field a is also displayed with a textbox, but right behind it is a combobox with only the pull down showing. This trick if done properly makes the second control look like a combobox, allowing you to pull choices but stores it in the textbox. The reason for this is in a combobox on a continous form if you pick value "A" from a combobox on a record then change the query so that "A" is no longer included in the combobox query the previously selected record appears to disappear from your form. Even if bond to a field a combox can only display values in the select query.

The form would be bound to a table, call it tblMatches. Also have another table called tblSource
1. Have a means to select source and destination tables
2. Push each destination field name into field one of tblMatches
3. Push each source field name into tblSource
3. For each matching name in the source table push it into the second field of tblMatches to prepopulate
4. Make the query for the combobox be something like
Select fieldName from tblSource where fieldName not in (Select field2Name from tblMatches) Union Select Null as BlankField from tblSources
5. Requery the combo on the combo's on enter event

So now you will have field one with all the destination fields listed and a what appears to look like a combobox next to it with only the remaining choices. The stacked textbox/combox works pretty well. The textbox has to be locked so that the user can only select from the dropdown and not type a value in. The drawback then is there is no autopopulate feature.

 

[sxschech]

Sorry for delay, got pulled onto something else and hope to get back to it soon. In meantime, thank you for the great tips. Even though the user has to manually paste the destination in the query when using DoCmd.RunCommand acCmdQueryTypeAppend, the nice thing is that anything they don't map to goes away when saved so that saves them effort from how it was done before in having to do a lot of clicking to populate or depopulate fields on the grid. Hope it will be ok to post back if I have issues when trying out your form recommendations.

 

[MajP]

I demoed the form idea and will post when I get a chance. I tried a version using multiple listboxes and a version using a continous form. I think the continous form idea works better as an interface. You will have to look at it. None of it is too complicated, but there is just a lot of different moving pieces. Basic Idea is just like you said.

1) The user has a combo box to choose the source table and one for the destination table. For the demo I hard wired the two tables I wanted, but you can use the system table to get a list of available tables and make it so you cannot pick the same table twice.
2) For ease I created three tables. One holds all the source table field names, one holds all the destination field names, and one is the main table that holds the matches.
3) Once you pick the source and destination it runs code to put all the source fields into the matches table and any field from the destination that has the same gets "prepopulated" in the destination field of the matches table. Also populates the fields names into the tables to hold the source and destination field names. This just helps to simplify code.
4) You therefore see all source fields and the prepopulated destination fields on the form. On the form, the destination field then has a combo box where only the unselected fields from the destination can be picked. This requires a little form trickery.

Would I create a table of field names and then a continuous form displaying all the fields on the left and a combo box of all the fields in the destination table on the right and the user would then click on combo to map the fields? But how to avoid user from picking the same field twice

So basically yes with some trickery this is done. Definitely needs some more bells and whistles, but the code to do this is not that much.