Microsoft Access 2003 Linked Server on SQL Server 2008 using the JET and MSDASQL Providers from SSWUB.org

About this Author

article source http://www.sswug.org/articles/viewarticle.aspx?id=44218

Microsoft Access 2003 Linked Server on SQL Server 2008 using the JET and MSDASQL Providers

For accessing geographically distributed data linked servers provides a good solution. MS SQL Servers always
provided remote access to servers through RPC mechanisms, but they had the limitation of being confined to
invoking procedures on remote locations. A linked server (a virtual server) may be considered a more flexible
way of achieving the same thing, with the added benefits of remote table access and distributed queries. This
linking is achieved using OLE DB technology. Both MySQL and EnterpriseDB servers can access remote data
but not as efficient. This remote access capability requires creating links by a suitable data source provider.
Microsoft's MSDASQL, a OLE DB provider of ODBC can be used. In addition to MSDASQL the Microsoft Jet OLE
DB Provider can also be used.

Over view of the tutorial

The steps needed to create and verify linked server are:

1. Create a System ODBC DSN to establish a linked server
2. Use the Management Studio to create a linked server
3. Verify that the linked server is functioning as expected

In what follows we first create a Linked Server using the Microsoft OLE DB Provider for ODBC (MSDASQL)
followed by another linked server using Micrsoft Jet 4.0 OLE DB Provider for purposes of comparison.  It may
be noted that linked servers may also be created using stored procedures.

Creating a System ODBC DSN

It is very easy to create a System ODBC DSN by invoking the ODBC Data Source Administrator from Start | Control
Panel |Administrative Tools |Data Sources (ODBC)When the ODBC Data Source Administrator window gets displayed, click on the Add button to open the Create New
Data Source window as shown. Scroll down and highlight Microsoft Access Driver and click on Finish.



In the ODBC Microsoft Access Setup window provide a name for the Data Source. Herein LinkedAcces
was the name chosen. This will be the System DSN you will be using while configuring the linked server.
Provide a description for the data source (Optional). Then click on the Select...button.

This brings up the Select Database window where you locate the Northwind database used in this tutorial.
On this computer, the Northwind database file Northwind.mdb was found at C:\Program Files\Microsoft
Office\OFFICE11\Northwind.mdb. This could be different in your case. Choose the file and Click on OK.
The location information gets into the ODBC Microsoft Access Setup window. Click OK on this window.
You will have created a System ODBC DSN. The ODBC Data Source Administrator shows up listing your file as shown here.



Click OK and close the ODBC Database Administrator window.

Creating a Linked Server

Open the SQL Server 2008 Management Studio and log on to the Database Engine. Expand the Server Objects 
node and right click to open the drop-down window as shown.



Click on the New Linked Server...to open the New Linked Server Window. Provide a name for the linked
server, herein NWIND_LINK was used. Change the provider from default (MediaCatalog OLE DB Provider)
to Microsoft OLE DB Provider for ODBC Drivers.
For Data source you should use a System DSN, the LinkedAccess that was created in earlier. For Product
Name you can use "Microsoft Access 2003" as shown.



This is all that is really needed (The wizard will get the required items from the System DSN). A fully detailed
entry would be as shown here.



Click on the Security navigational link on the left to open the Security related page on the right as shown.



Accept the default for login namely ' Be made without using a security context ' for the purposes of this tutorial.
Please review the other options. In this tutorial  the Access default user, ADMIN is also the computer owner.

Click on Server Options navigational list item to open the Server Options page as shown. Make no changes
in this window for this tutorial.



When you click OK on the above screen you will have created a Linked MS Access server on SQL Server 2008.
You may need to refresh the Linked Servers node to see your newly created NWIND_LINK server as shown in
the next figure. The Tables and Views on this can be accessed.



It may also be instructive to script out the linked server by right clicking the linked server and choosing
Script Linked Server as | Create TO | [New Query Window or File... or Clip Board...] and review the
script. The script contains all information collected from the wizards.

Exploring the Linked Server

There are a number of stored procedures that can be used to test the linked server. Only
two of them are described here.Not all stored procedures are supported by all providers.
The stored procedure, sp_helpserver provides details of the linked server as shown here.



The stored procedure sp_tables_ex provides information regarding the tables in the linked server
as shown.



The linked server can be queried as if it is a regular database on the server. For this you can use the openquery()method
with the following syntax to access the data on the database.

OPENQUERY(linked_server, 'query') 
where query is the query string executed against the linked server. The next figure shows an example of this query applied to
the linked server created  in this tutorial.



Data can also be accessed using the 4 part fully qualified table name while calling remote servers with the
following syntax:

linked_server_name.catalog.schema.object_name
As there is neither a schema nor a catalog associated with Microsoft Access this reduces to the following:

linked_server_name...ObjectName
When a query is executed in the present case with the above syntax the following message is returned.



MSDASQL does not work in this case. It is not supported. This will not work even if the dbo in the
above query is removed.
 

Providers for Linked Servers

Microsoft supports a number of OLE DB providers so that linked servers can be set up with disparate database
vendor products. Under Server Objects node in the Management Studio you can expand the Linked Servers
node to access the Providers as shown in the next figure. There are two OLE DB Providers that can be used
to create a Microsoft Access linked server on SQL Server 2008 as shown. In the previous section the MSDASQL provider was used.



 

Creating a linked server using the Jet OLE DB Provider

The procedure is very similar to the linked server NWIND_LINK described earlier. The following screen
shows the attributes used in setting up the linked server.

In this case the Microsoft Jet 4.0 OLE DB Provider is used. The information needed to set up the linked
server takes just the provider name and the location of the mdb file as shown.


 

Querying the Linked Server

When the linked server is set using the Microsoft Jet 4.0 OLE DB provider the linked server may be queried using the
openquery() method as shown in the next figure.



It may be noted that the linked server may also be queried by the (reduced )fully qualified 4 part syntax
as shown in the next figure.

Summary

The article describes how a linked Microsoft Access server can be created in the SQL Server 2008 using the SQL Server 2008
Management Studio. Two inked servers are created using two different providers. The linked server using the
JET provider is easy to set up requiring nothing more than the location of the mdb file. Also data can be accessed by both the
openquery() method as well as the reduced 4 part fully qualified syntax for the Jet provider created linked server. On the other
hand the MSDASQL provider requires a System DSN and the data can be accessed using only the openquery() method. 

Adam Anderson's Dynamic SQL-like Linq OrderBy Extension

For personal reference the post have been copied from 

http://aonnull.blogspot.com/2010/08/dynamic-sql-like-linq-orderby-extension.html

Dynamic SQL-like Linq OrderBy Extension

So, it's been a while, but I thought I take moment and do my annual blog post ;).
I've been playing around with ASP.NET MVC and the Linq stuff for NHibernate recently. I was in need of an OrderBy extension method that could take a SQL-Like OrderBy string and sort a IQueryable<> or IEnumerable<> collection. I wrote up an implementation that worked, but I just wasn't satisfied with its internals (quite a bit of reflection to get the correct type to construct a LambdaExpression, etc)
At any rate, I couldn't leave well enough alone, and, after a bit of Googling, I ran across this StackOverflow answer aboutDynamic LINQ OrderBy. The extension method wasn't exactly what I was looking for, but that ApplyOrder method is slick, and solved the portion of my implementation that was bothering me.
So, I though I would post up my version in case anybody finds it useful. It handles the following inputs:

list.OrderBy("SomeProperty");
list.OrderBy("SomeProperty DESC");
list.OrderBy("SomeProperty DESC, SomeOtherProperty");
list.OrderBy("SomeSubObject.SomeProperty ASC, SomeOtherProperty DESC");

Dynamic SQL-like Linq OrderBy Extension

    public static class OrderByHelper
    {
        public static IEnumerable OrderBy(this IEnumerable enumerable, string orderBy)
        {
            return enumerable.AsQueryable().OrderBy(orderBy).AsEnumerable();
        }

        public static IQueryable OrderBy(this IQueryable collection, string orderBy)
        {
            foreach(OrderByInfo orderByInfo in ParseOrderBy(orderBy))
                collection = ApplyOrderBy(collection, orderByInfo);

            return collection;
        }

        private static IQueryable ApplyOrderBy(IQueryable collection, OrderByInfo orderByInfo)
        {
            string[] props = orderByInfo.PropertyName.Split('.');
            Type type = typeof(T);

            ParameterExpression arg = Expression.Parameter(type, "x");
            Expression expr = arg;
            foreach (string prop in props)
            {
                // use reflection (not ComponentModel) to mirror LINQ
                PropertyInfo pi = type.GetProperty(prop);
                expr = Expression.Property(expr, pi);
                type = pi.PropertyType;
            }
            Type delegateType = typeof(Func<,>).MakeGenericType(typeof(T), type);
            LambdaExpression lambda = Expression.Lambda(delegateType, expr, arg);
            string methodName = String.Empty;

            if (!orderByInfo.Initial && collection is IOrderedQueryable)
            {
                if (orderByInfo.Direction == SortDirection.Ascending)
                    methodName = "ThenBy";
                else
                    methodName = "ThenByDescending";
            }
            else
            {
                if (orderByInfo.Direction == SortDirection.Ascending)
                     methodName = "OrderBy";
                else
                     methodName = "OrderByDescending";
            }

            //TODO: apply caching to the generic methodsinfos?
            return (IOrderedQueryable)typeof(Queryable).GetMethods().Single(
                method => method.Name == methodName
                        && method.IsGenericMethodDefinition
                        && method.GetGenericArguments().Length == 2
                        && method.GetParameters().Length == 2)
                .MakeGenericMethod(typeof(T), type)
                .Invoke(null, new object[] { collection, lambda });

        }

        private static IEnumerable<OrderByInfo> ParseOrderBy(string orderBy)
        {
            if (String.IsNullOrEmpty(orderBy))
                yield break;

            string[] items = orderBy.Split(',');
            bool initial = true;
            foreach(string item in items)
            {
                string[] pair = item.Trim().Split(' ');

                if (pair.Length > 2)
                    throw new ArgumentException(String.Format("Invalid OrderBy string '{0}'. Order By Format: Property, Property2 ASC, Property2 DESC",item));

                string prop = pair[0].Trim();

                if(String.IsNullOrEmpty(prop))
                    throw new ArgumentException("Invalid Property. Order By Format: Property, Property2 ASC, Property2 DESC");
               
                SortDirection dir = SortDirection.Ascending;
               
                if (pair.Length == 2)
                    dir = ("desc".Equals(pair[1].Trim(), StringComparison.OrdinalIgnoreCase) ? SortDirection.Descending : SortDirection.Ascending);

                yield return new OrderByInfo() { PropertyName = prop, Direction = dir, Initial = initial };

                initial = false;
            }

        }

        private class OrderByInfo
        {
            public string PropertyName { get; set; }
            public SortDirection Direction { get; set; }
            public bool Initial { get; set; }
        }

        private enum SortDirection
        {
            Ascending = 0,
            Descending = 1
        }
    }

Anyway, hope someone finds it useful. And if you see any areas that could use some TLC please let me know A

Microsoft Report Designer - Undocumented Error could not load file or assembly...

Try putting your custom assembly in the [ProgramFiles32]\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies folder and see if that solves the issue.