Tutorial: Integrating Database of Two or More Applications
Integrating Databases of Two or More Apps
Integrating databases is an easy way to share data between two or more DMXReady applications.
In this example, we will integrate Mailing List Manager with Members Area Manager so that we can send regular e-mails to all of our members. You can adapt these instructions to the specific applications you are merging.
NOTE: You will need Microsoft Access and Excel in order to integrate these databases. DMXReady ranks this procedure as Advanced Customization, and should be undertaken carefully to avoid data loss.
- Identify which database you want to be your “main” database. Because we already have data in our Members Area Manager database and want to add Mailing List Manager, the Members Area Manager will be our “main” database. The Mailing List Manager will be our “source” database.
- Download both the Mailing List Manager and the Members Area Manager databases from your server and save backup copies of to somewhere else on your hard drive to keep your original data safe.
- In Microsoft Access, open the Members Area Manager database from your hard drive (not from the server).
- Highlight the file tblEntity and click File-> Export
- Click on the "Save Formatted" checkbox, and save as an Excel file (your current version).
- Repeat steps 2-4 with the Mailing List Manager database (with the table: tblMemberList). NOTE: there may be several tables in your application – choose the file that contains data that you are trying to merge e.g. contact lists, etc.
- Open both the files you created in Excel.
- Identify the column headers in “source” database (Mailing List Manager) that are not present in the “main” database (Members Area Manager). In this case, those would be “LastMessageDate” and “SentMessages.” Also double-check to make sure that all other headers are spelled exactly the same way. Spaces and capitals count here too. Make a note of any differences you find – you will fix this in Step 25.
- Add the missing column headers from the source database to the main database, ensuring that they are spelled exactly the same way. You can edit and use the “ExtraField” headers if present, or add new columns.
- Column by column, cut and paste the data from the source database to the corresponding column in the main database. Do not skip any rows. (*NOTE: If you do not have any data in the source database, you do not have to do this step.)
- Save the file as NewTable in Excel format within your local website folder called "databases" (e.g. C:/MyDocuments/YourWebsite/Public/databases).
- In Microsoft Access, open the Members Area Manager database and go to File-> Get External Data -> Import
- Change the File Type to Excel, and select the NewTable file.
- This will bring up the Import Spreadsheet Wizard. On the first page, click the "First Row Contains Column Headings" check box and click the Next button.
- Select "In a New Table" and click the Next button.
- Do not edit any of the fields on this page, simply click the Next button.
- Select "No Primary Key" and click on the Next button.
- In the "Import to Table" text box, type "NewTable" and click on the Finish button.
- In Microsoft Access, double-click "NewTable". Ensure that all the information transferred and is listed correctly.
- Once you are satisfied that everything is in order, delete the current "tblEntity" file and rename "NewTable" to "tblEntity". Make sure the table name is spelled exactly the same way.
- Drag and drop all Mailing List Manager tables except tblMailingList (which you have just merged with tblEntity) into the Members Area Manager database.
- In Dreamweaver, put the Members Area Manager database to the server. If prompted to overwrite, click "Yes".
- Open the Mailing List Manager connection file, found at /Connections/mailinglistmanager.asp in your local site.
- Change the connection pointer from “/databases/mailinglistmanager.mdb” to “/databases/membersareamanager.mdb”.
- If you found different column headings in Step 8, you will have to change all references to them within your Mailing List Manager files. For example, the first column in Mailing List Manager is called “MemberID” and in Members Area Manager it is called “EntityID”.
- In Dreamweaver, use the Find tool to find “MemberID” and replace with “EntityID.” Make sure the “Find In:” pull-down menu is set for “Entire Current Local Site.”
- The files should open and appear in your Design Area as you make the changes. If not, take note of all files that are changed by referring to the Results Panel Group (usually below the Design Area).
- Save and upload all edited files to your server.
- Open the Mailing List Manager admin page through your web browser (i.e. www.yoursite.com/admin/MailingListManager/admin.asp). All the members from your Members Area Manager database should appear on the Mailing List Manager admin page, ready for emailing!
- DMXReady recommends that all new contacts be added through the Members Area Manager application, and not the Mailing List Manager.
You can actually take this one step further and combine your applications for easy navigation:
- Create a new page in Dreamweaver using a blank page or your own website template, and save it in your Admin/ (or any other app) folder as “controlpanel.asp”
- Create virtual links (relative to root) to all the application admin pages. For example, /admin/MembersAreaManager/admin.asp
- Secure the page using Members Area Manager.
Save and upload to your server. You now have one centralized Control Panel from which to access all your apps!