SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SQLRally 2011 Scripting Guy Guest Blog Post

Ed Wilson (Blog|Twitter) aka Scripting Guy has series of SQL Server related posts the week of  May 2nd 2011 including my guest blog post. The post, Use ACE Drivers and PowerShell to Talk to Access and Excel, demonstrates querying Excel and Access files from PowerShell and loading the data into a SQL Server table. There several ways to get data from Excel and Access, but I find using ADO.NET to be the most straight forward.

An important consideration when using ADO.NET against Excel and Access files is selecting the right OLE DB drivers. In the post I talk about using Access Control Entry (ACE) drivers.  ACE is completely free, and it even includes a 64-bit version. For SQL Server professionals having a 64-bit driver for Excel and Access is a big deal as ACE’s predecessor, JET only supported x86. ACE is included with Office 2007 or higher and Office 2010 has a 64-bit version. If you don’t have Office or you’re installing on a server go to Microsoft Access Database Engine 2010 Redistributable, and download AccessDatabaseEngine.exe or AccessDatabaseEngine_x64.exe, depending on your operating system.

I’ve mentioned this in the post, but I think this is a key takeaway which I’ll restate–When you have ACE drivers, there is no reason to use the old deprecated JET drivers—even for older versions of Microsoft Access and Excel. A common mistake I see, even with seasoned developers, is to drop to JET for .mdb and .xls files when you don’t need to. I’ve even made this mistake myself.

I found a helpful blog post on MSDN from the CSS SQL Server Engineers that talks about different data providers and discusses a migration strategy.

My guest blog post Use ACE Drivers and PowerShell to Talk to Access and Excel doesn’t delve into other uses of the ACE driver including working with delimited text files which I’ll blog about in a future post.

Chad Miller

Chad Miller is a Senior Manager of Database Administration at Raymond James Financial. Chad has worked with Microsoft SQL Server since 1999 and has been automating administration tasks using Windows Powershell since 2007. Chad is the Project Coordinator/Developer of the Powershell-based Codeplex project SQL Server PowerShell Extensions (SQLPSX). Chad leads the Tampa Powershell User Group and is a frequent speaker at users groups, SQL Saturdays and Code Camps.


Posted by sem.johareez on 1 August 2011

Simply such an amazing blog got to know the views on various topics from the professionals.

<a href="http://www.johareez.com">Online Shopping</a>

Posted by sem.johareez on 1 August 2011

Wow! I am so glad to find so many admirable such in this blog and I really like it. From now I’ll be a permanent one to visit this blog.


Leave a Comment

Please register or log in to leave a comment.