QweryBuilder has a built in data transfer utility which allows a developer to move data from one data source to another. In this post, I'll provide a quick overview of how you can use this utility to move data from Access to any other registered data source. Note: QB 6.5.3 has one small issue that needed to be resolved in order for this to work properly with ODBC connections. You can download an updated version of QweryBuilder here.
Step 1: Ensure that you create an ODBC profile for your Access database. After you do this, register a new ODBC data source in QweryBuilder.
Step 2: Open a new data transfer window. Look under the Tools menu and select Data Transfer.
Step 3: Click the Add New Table toolbar button. This will add a "step" to the transfer process. You'll be prompted for a name. We suggest naming it after the table that will be used in the transfer.
Step 4: Select the Source and Target data sources. In the case of our example, the Source data source will be the Access data source and the Target will be the data source that the information will copy to.
Step 5: Select the transfer mode. Delete\Insert will delete all the rows from the target table and insert all the ones selected from the source. Update\Insert will update any tables that match in the source and target table and insert any rows from the source that don't exist in the target. Insert Only will only insert new rows from the source that do not exist in the target. Update Only will only update rows in the target that exist in the source.
Step 6: Organize the source and target columns so they match up. Columns can be removed, added, moved up and moved down. You can also add static text values.
Step 7: Optionally, you can include a WHERE clause for your source data source. For example, you may only want to transfer data that is older than a year.
Step 8: Click the save button to save your new data transfer configuration. You will be able to open the saved file any time to do the transfer over again.
Step 9: Repeat steps 3 to 8 for each table that you want to be included in the data transfer process.
After you have set everything up, you can click on the Transfer All toolbar button to start the transfer. A report will display after it complete's which will let you know what actions on the database were performed or if any errors occurred.
You can transfer one table at a time by clicking on the Transfer button in the sub window that opens.
Hint 1: You can preview the SELECT statement that will be used to retrieve information from the source data source. The SELECT statement will open in a new SQL Editor window.
Hint 2: You can validate your WHERE clause with the validate button. This will run a count on the generated SELECT statement then display the results next to the button.
Hint 3: Transfer steps can be disabled by clicking the Enable\Disable Table toolbar button. Make sure you select an item from the list prior to clicking this button. Disabled tables will not be included in the transfer. You can enable the step by clicking the Enable\Disable Table toolbar button again.
IMPORTANT: In order for any Update related transfer mode to work properly, QweryBuilder matches source and target records based on primary keys. If your table does not have a primary key, it most likely won't be able to make a match. With that said, it appears that the Access ODBC driver doesn't support the ODBC API function GetPrimaryKeys. This means that for Access, QweryBuilder can't determine what keys to base the updates on. For Access, your best bet will be to use the Delete\Insert option to refresh the target table.
IMPORTANT: This is a standard disclaimer that applies to many types of database operations. It's advised that you take caution when selecting the target data source that will be updated. Information in that data source will be updated, inserted and deleted (based on transfer type). Anything could happen which could result in lose of data (e.g. the power goes out while in the middle of the transfer). We suggest that you do not transfer to production databases. If you must, then make sure you perform the appropriate backups.
If there are any questions about this feature, please feel free to contact us at support@werysoft.com.
Thanks,
The Werysoft Team
No comments:
Post a Comment