Transferring Data Between Connections
Office-QB has a powerful data transfer feature, which may be used to transfer the resulting data of the currently active open query into any database defined by the connections in the Connection pane. There are two options for importing data into a selected connection, namely:
· Inserting the result of the active query into a new table in the destination database defined by the selected connection, in which case the new table structure is created before import.
· Appending the result of the active query into an existing table in the destination database defined by the selected connection.
To transfer data between connections, first open a query in the source connection defining the source database. This query must be a SELECT statement, in particular a query that returns records. Test your query and execute it against the backend database to check or see that the data is to your liking. Then from your application’s menu, select either Insert into new table or Append to existing table menu item under the menu Import Result. In either case, the Import dialog is displayed for further selection or specification of destination connection, database and table name (see Figure 1.18 for more details).
Figure 1.18. Showing the Import dialog
The Import dialog allows import into any of the existing connections in the Connections pane (i.e. all CON-files in the QB-project). Therefore, a good habit towards the aim of data transfer, is to define connections for each and all databases that interests you in your work. Given this then, the Import dialog makes it very easy to transfer data from one connection into the other. Essentially, all you have to do is to specify the destination connection from the Select destination connection list-box, check the Import Option, then select the database from the Database Name combo-box, and finally either define a table name (if you selected Insert into new table option in the Import Option) or select a table from the list of existing tables in the selected database (if you selected Append to an existing table option in the Import Option).
Once the proper values in the Import dialog is selected, you may click the Import button to run the import process. The import process will create the table structure if necessary, and then import the collection of all records resulting from the active query into the destination table.
Note that when importing data into a new table in the destination database, the structure of the destination table (which was created as a result of the import process) will not have any field constraints.