There are 3 steps to configuring BoogiePOP Enterprise to log to your MS Access database. They include:

1. Creating your email bounce log table in MS Access
2. Configuring BoogiePOP Enterprise to connect to your POP3 email bounce account
3. Configuring BoogiePOP Enterprise to connect to your MS Access database

Pre-Requisites:

This article assumes that you have Microsoft Access 2000 installed, and that you have some level of expertise with it. You should also have BoogiePOP Enterprise installed prior to starting. You can download BoogiePOP Enterprise from the BoogieTools website at http://www.boogietools.com.

Download:

If manually creating your own database isn't appealing to you, then you can download the Access 2000 database we used in this article. Download the zip file from here, and unzip the email.mdb file to your local hard drive.

If you downloaded the MS Access 2000 database provided on page 1 of this article, then skip to the end of this page by clicking here.

The first thing you need to do is create your email bounce log table within your MS Access database. You can create the email bounce log table in an existing database that you are already using, or you can create a new one. The database I'll be using for this article is one that already exists and is something I use quite regularly. Either way, using your Microsoft Access application, you'll need to create the new table with the following attributes.

Table Name: bBounceLog

Field Name	Data Type	Field Size	Indexed	Allow Zero Length	Default
BounceLogId	AutoNumber	Long Integer	Yes (no duplicates)
BounceType	Text	4	Yes (duplicates ok)	no
PopAccountName	Text	20	No	no
EmailAddress	Text	255	No	yes
RawMessageText	Memo	n/a	No	yes
InsertDate	Date/Time	n/a	Yes (duplicates ok)		Now()
Processed	Number	Long Integer	Yes (duplicates ok)		0

Note: The table name is prefixed with the letter "b" to help insure we don't create a table name that
may already exist in your database. The 'b' stands for BoogieTools.

When you are done adding the fields to your new table, you should see a screen that looks similar to the image below. Click File-Save, and when prompted for the name of the table, enter bBounceLog. You can ignore the dialog box that appears stating "there is no primary key defined". Just click the "no" button. You can make the BounceId field the primary key if you would like, but it is not necessary for this article.




After saving your new table, you will see a table named bBounceLog in the Access database window (see image below). Note that you see a couple of extra tables in the image below too. These tables existed in my database before creating my bBounceLog table. The table called "EmailList" is the table that stores all of my opt-in email addresses. You may or may not have additional tables in your database. I'll use these additional tables in Part 2 of this article.




Write Down Your Database Name And The Folder You Saved It Too

Before continuing, make a note of your MS Access database name, and the windows folder where you saved it. For instance, the name of my Access database is email.mdb, and I saved it in my c:\temp folder. You'll need this information later on in this article.

Using the Admin Utility, you must create a new BoogiePOP POP Account that BoogiePOP Enterprise will retrieve your bounced email messages from. The BoogiePOP POP Account that you create should be configured to use the POP email box where all of your "bounced" email messages are sent too. Typically, this is the same email account as the "FROM" email address of your outbound email messages. To create a new BoogiePOP POP account, follow the instructions below.

First, click the File menu and select "New POP Account" (see image below).



The following dialog will be displayed. Enter all of the required information as it pertains to you. For an explanation of each field, see the bottom of this page. For this article, I'll create a BoogiePOP POP Account called "Sales Newsletter".



The following image shows the information I entered. The information you enter should be different.



Click the "Save" button when you're done entering the required information. Your new BoogiePOP POP account will be created. In the image below, notice that a new POP account called "Sales Newsletter" is displayed in the BoogiePOP Admin TreeView Window.




POP Account Field Descriptions

Mail Account Name: This is the name that you'd like to refer to the new POP account as. The information you enter in this field will be displayed in the BoogiePOP TreeView Window under the "POP Accounts" folder. The "Mail Account Name" can be up to 20 characters in length.

POP3 Server: This is the name of the email server where your "bounce" email box resides. It can be a domain name such as "mail.boogietools.com" or an IP address in string form such as "127.0.0.1".

Login: Enter the username for your "bounce" email box here.

Password: Enter the password for your "bounce" email box here.

From Address: This is the email address of the "bounce" email box. All of your email bounces are sent to this email address.

Reply-To Address: The "Reply-To" email address is the email address that is used when your email recipients hit "reply" to your messages. When they hit "reply", their email message is sent to the "Reply-To" email box. It's highly recommended that you have two POP email boxes for all of your opt-in email communications. One email box for all of your "bounces" to go too, and one email box for all of your "replies" to go too. Having 2 POP email boxes for all of your opt-in email communications will allow all of the "email bounces" to end up in one email box, and all of the "replies" to end up in a separate email box. Doing this will improve BoogiePOP performance tremendously because BoogiePOP won't have to parse all of your "non-bounce" replies.

Ignore Addresses: The "Ignore Addresses" field isn't used by BoogiePOP in all cases. Sometimes BoogiePOP knows that a message is a bounce, but for some reason the email address of the recipient isn't in the position that BoogiePOP is expecting it to be in. When this occurs, BoogiePOP does a deeper scan of the bounced message to find the proper recipient email address. BoogiePOP doesn't want to pass back the From, Reply-To, or the Ignore Addresses of the original sender by mistake, so it checks for these while calculating who the original recipient was. You do not need to re-enter your "From" and "Reply-To" email addresses here, but you may use this field for any other email addresses that may appear in your outbound messages.

You can enter several email addresses in this field if you need too. Just make sure you separate each email address with a PIPE character. For example:

support@acme.com|accounting@acme.com
The example above is made up of 2 email addresses. support@acme.com and accounting@acme.com.

Account Inactive: Check this box to prevent BoogiePOP from retrieving messages from this POP account.

Before starting this step, you'll need to know the name of your MS Access database file, and the folder you saved it too. The name of my Access database is email.mdb, and I saved it in my c:\temp folder. I'll be creating a "System DSN" that points to it. Your Access database is probably in a different location.

In order for BoogiePOP Enterprise to connect and log to your database, you must first create a "System" Data Source Name (DSN), on the computer running BoogiePOP Enterprise, that points to your database. To create a new "System" DSN, click the Database TreeView item within the BoogiePOP TreeView Window (see image below) for the BoogiePOP POP account you created in the previous step. Next, click the "..." button next to the DSN selection box. This will launch the ODBC Data Source Administrator Applet.


Sample BoogiePOP Database Connection Window



Click on the "System DSN" tab, and then click the "ADD" button to launch the "Create New Datasource" dialog.


ODBC Data Source Administrator Applet



Select the "Microsoft Access Driver (*.mdb)" driver and click "FINISH" to display the ODBC Microsoft Access Setup dialog.


Create New Data Source Dialog



Enter a data source name and description for your database. For this article, my data source name (DSN) will be "Email Bounce Log Database" and the description will be "BoogiePOP Enterprise Bounce Logging". You can name your DSN whatever you like. Next, click on the SELECT button to launch the "Select Database" dialog.


ODBC Microsoft Access Setup Dialog



Browse to your Access Database (Our example database name is called "email.mdb". Your database will probably be named something different), highlight it, and click OK. At this point, you'll be taken back to the "ODBC Data Source Administrator Applet".


Select Database Dialog



You will see a newly created System DSN called "Email Bounce Log Database". Click OK to return to BoogiePOP Admin.


ODBC Data Source Administrator Applet



Select the "Choose DSN" dropdown box to select your newly created DSN. If your database has a password protection system then type in your login and password for the database.


Database Screen after selecting our newly created DSN.



Entering your SQL Statement

Now that you have configured the DSN, it's time to enter the SQL statement. In order for BoogiePOP Enterprise to log bounce data to your database, you must enter the SQL statement that BoogiePOP Enterprise will execute each time it retrieves a bounced message from your POP account. To acheive this goal, we'll be using a simple SQL INSERT statement (see below). You can copy/paste the following SQL into the "SQL Statement" text box in BoogiePOP Admin.

SQL Statement
INSERT INTO bBounceLog (BounceType, PopAccountName, EmailAddress, RawMessageText) VALUES ('#BOUNCETYPE#', '#POPACCOUNT#', '#EMAIL#', '#RAWMESSAGE#')

Once you've entered the above SQL statement, your database settings window will look like the following for your BoogiePOP POP account:






Using BoogiePOP Variables

One important concept to remember when creating your SQL statement is the idea of BoogiePOP variables. A BoogiePOP variable is a data value that can be used anywhere in your SQL statement. They are most commonly used in the VALUES clause of an INSERT statement, the SET clause of an UPDATE statement, or as parameter values for a SQL stored procedure.

You can use a variable by typing it into the SQL Statement text area using surrounding pound symbols "##". For example, to refer to the variable "EMAIL", you would type "#EMAIL#". Variables are case-sensitive.

The table below lists the standard BoogiePOP variables that BoogiePOP can use within your SQL statements.

Name	Description
BOUNCETYPE	The bounce 'type' of the bounced message. (ie. SB, HB, GB, MB, MBKS, etc.)
EMAIL	The recipient email address of the bounced message.
POPACCOUNT	The POP account that BoogiePOP retrieved the bounced message from.
RAWMESSAGE	The raw text of the email message BoogiePOP retrieved.

NOTE: There are several more BoogiePOP variables that you can use, but they are not covered in this article.


Testing Your Database Connection & SQL Statement

You'll need to test your BoogiePOP POP account database settings after you have selected your DSN and entered your SQL statement. To do this, click the "Test" button at the bottom of the POP account database settings window. If everything works correctly, you'll see a message like the one in the following image.


BoogiePOP Successful Test Dialog



To confirm that the "Test" button worked properly, you can also open your Access database and check to see if the "test" record was inserted into the bBounceLog table. Each time you click the "Test" button at the bottom of the POP account database setttings window, a record will be created in your bBounceLog table. If everything worked correctly, you'll see a test record in the bBounceLog table. See image below.


Successfully inserted test record



Enable This Database Connection and Sql Statement

Once you're done testing your BoogiePOP POP account database settings, you'll want to enable it. You do this by making sure the "Enable This Database Connection and Sql Statement" checkbox has a check mark in it. Selecting this checkbox will tell the BoogiePOP service to connect to the DSN, and execute the SQL statement for every email bounce message it retrieves. If, at anytime, you'd like to stop logging to your database then un-check this same checkbox.


Save Your Settings

In order for your new database settings to take affect, you must click the "Save" button at the bottom of the POP account database settings window.

Congratulations! If you've gotten this far, then you've successfully configured BoogiePOP Enterprise to log the email bounce data from your bounce email account to your Microsoft Access database. You can now start the BoogiePOP Bounce Service. You may want to do this while watching the POP activity log under the Current Activity TreeView item (see image below).






To watch the POP Log activity, follow these steps:

Step 1: Click the START button on the Current Activity - POP Log window (bottom right).
Step 2: Click "Start Service" on the File Menu.

If the "Start Service" option is greyed out, then the BoogiePOP Bounce Service is already running. You can re-start it by first clicking "Stop Service" on the File Menu, and then clicking "Start Service".

Questions?

If you have a question regarding this article, then please send an email to "support @ boogietools.com". We'll reply to your request as soon as possible. For more information regarding BoogieTools' products, please visit us on the web by CLICKING HERE.