Connecting to an Access 2007 Database Using ColdFusion

The 2007 version of Microsoft Office introduced new file formats for Word, Excel, and Access.  If you need to connect to an Access 2007 database file, one with the "accdb" file extension, you will not be able to use the Access database drivers bundled with ColdFusion.  These drivers are designed for the previous Access file format which used "mdb" as a file extension.  In this post I will walk through creating a ColdFusion data source for an Access 2007 file.  This process was tested on a Window 2003 server using ColdFusion 9.0.1 on a 32-bit machine.  


Prerequisites


1. A ColdFusion installation which includes ODBC the optional services.

2. The Office 2007 System Driver package should be installed on your server.  This will install the ODBC drivers for Access 2007.  This may not be required if Office 2007 has been installed on the machine.
http://www.microsoft.com/downloads/en/details.aspx?familyid=7554f536-8c28-4598-9b72-ef94e038c891&displaylang=en


You must set up your data source twice.  First an ODBC connection must be created in Windows, and second a data source must be created in the ColdFusion administrator.


Create the ODBC Connection in Windows


1. Open Control Panel > Administrative Tools > Data Sources (ODBC).


2. Select the System DSN tab.


3. Click Add...

4. In the Create New Data Source window Select Microsoft Access Driver (*.mdb, *.accdb), then click Finish.


5. In the ODBC Microsoft Access Setup window fill in the Data Source Name, then click Select... 


6. In the Select Database window browse to the .accdb file you wish to use, then click OK.


7. In the ODBC Microsoft Access window click OK.


8. In the ODBC Data Source Administrator window click OK.



Create the Data Source in ColdFusion


1. Log into the ColdFusion administrator.

2. Open the Data & Services > Data Sources page.


3. Enter a Data Source Name and for Driver select ODBC Socket, then click Add.


4. On the Data & Services > Datasources > ODBC Socket page select your System DSN from the ODBC DSN list, then click Submit. 


Conclusion
There a few caveats to be aware of.  First, I have not tested this process with a password protected database.  Second, Microsoft's Office 2007 drivers are not intended for use in a service or web application.  I would not recommended the use of Access in a production system.

Sitemaps in XML Format and Submission

A sitemap is a way of organizing a website, identifying the URLs and the data under each section. Its intended use is to allow search engines to track URLs more efficiently.

urlset tag - Encloses the file and references the current protocol standard.

loc tag - Contains the URL of a file.

proiority tag - List the priority of this URL compared to the other URLs on your site.


Sample Sitemap - click on code to make larger

As you can see from the XML above, the parent tag goes first followed by the child tags.




Escape Codes must be used for the characters below.




Submitting a Sitemap

Google

1. www.google.com/webmasters/sitemaps


2. To submit a sitemap to Google, to must first create a Google email account if you have not already done so.


3. Click the “Add a site…” button and add your url.


4. You will be asked to verify your site by either downloading a file and putting it in your wwwroot directory or by modifying your site index page by inserting a meta tag.


5. Once your site is verified by Google, click on the “Site configuration” link.


6. Click the Sitemaps link.


7. Click the “Submit a Sitemap” button, entered the name of the site map at your root directory and click the “Submit Sitemap” button.


8. Your status will show a clock for “pending”. The next day it will show a check mark for “ok”.

This does not mean that Google has indexed your site yet, just that your sitemap has been submitted and accepted.

Bing

1. Bing does not accept sitemaps. They expect their web crawler to find your site.

Ask

1. Ask does not have a sitemap submission form. To let Ask to know about your sitemap, you must use their ping service.


2. Type in http://submissions.ask.com/ping?sitemap=http://www.NameOfSite.com/NameOfSitemap.xml in your browser.

Yahoo

1. Yahoo works much the same way that Google does. Go to https://siteexplorer.search.yahoo.com/submit/


2. Enter your url (ex: www.wardlawclaims.com) and click the “Submit URL” button.


3. You will be asked to create a yahoo email account if you don’t already have one.


4. Click on the “Submit a Website or Webpage” link.


5. As with Google you will be asked to download a file to your wwwroot directory or insert a meta tag to your index page.


6. Once your website has been verified. Click the “Feeds” link, type the name
   of you xml sitemap file and click the “Add Feed” button.


7. Your status will show pending until it has been submitted to Microsoft then a check mark will be (usually the next day) display when it has been processed.

Again this does not mean that Yahoo has indexed your site, just that your sitemap has been submitted and accepted.

Facade Pattern in C#

O'Reilly's modern classic Head First Design Patterns describes the Facade pattern as a unified interface to a set of interfaces in a subsystem. Facade defines a higher-level interface that makes the subsystem easier to use.

This pattern is nothing revolutionary and something that all programmers have done at some point, but it is good to have a common name to go by. Today I will demonstrate how to create and remove groups in Active Directory by building a facade.

First, start Visual Studio and create a new project using the class library template. Once created, add a reference to ActiveDs in the project. Next, add a new class named ActiveDirectoryFacade.cs and add Using System.DirectoryServices to that class. Now, add the following function to create a group...

   public static bool CreateGroup(string groupName, out string result)
      {
         bool returnValue = false;
         result = "";

         try
         {
             DirectoryEntry groups = new DirectoryEntry();
             groups.Path = "LDAP://ldap/OU=EmailGroups,DC=yourdomain,DC=com";
             groups.AuthenticationType = AuthenticationTypes.Secure;

             DirectoryEntry group = groups.Children.Add(String.Format("CN={0}", groupName), "group");
             group.Properties["groupType"].Value = ActiveDs.ADS_GROUP_TYPE_ENUM.ADS_GROUP_TYPE_GLOBAL_GROUP;
             group.Properties["mail"].Value = String.Format("{0}@yourdomain.com", groupName);
             group.CommitChanges();

             result = String.Format("Successfully created distribution list {0}", groupName);
             returnValue = true;
         }
         catch (Exception ex)
         {
             result = ex.Message;
             returnValue = false;
         }

         return returnValue;
     }

...and add the following function to remove a group...

     public static bool RemoveGroup(string groupName, out string result)
     {
         bool returnValue = false;
         result = "";

         try
         {
             DirectoryEntry groups = new DirectoryEntry();
             groups.Path = "LDAP://ldap/OU=EmailGroups,DC=yourdomain,DC=com";
             groups.AuthenticationType = AuthenticationTypes.Secure;

             DirectoryEntry group = groups.Children.Find(String.Format("CN={0}", groupName));
             if (group != null)
             {
                 groups.Children.Remove(group);
                 group.CommitChanges();
             }

             result = String.Format("Successfully removed distribution list {0}", groupName);
             returnValue = true;
         }
         catch (Exception ex)
         {
             result = ex.Message;
             returnValue = false;
         }
            
         return returnValue;
     }

We have just created a facade to add and remove Active Directory Groups with two functions. We can now use these functions in our applications.