myself(Lowell)

---

a couple of prerequisites:

install the AccessDatabaseEngine_x64.exe from microsoft:

http://www.microsoft.com/en-us/download/details.aspx?id=13255

make sure you open an Administrative command prompt window, and run it with the c:\Downloads\AccessDatabaseEngine_x64.exe /passive

command line flag;

this will force the install of the drivers, even if you have 32 bit office installed;

otherwise you get some error about 32 bit Office preventing the install.

After that is isntalled:

--Required settings for the provider to work correctly as a linked server

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1

GO

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1

and then the code for the linked server:

--#################################################################################################

--Linked server Syntax for Excel with ACE 64 driver

--#################################################################################################

DECLARE

@srvproduct nvarchar(256),

@provider nvarchar(256),

@location nvarchar(100),

@provstr nvarchar(100),

@catalog sysname,

@sql varchar(1000)

--add an excel spreadsheet as a linked server.

SET @server = 'MyExcelACE'

SET @srvproduct = ''

SET @provider = 'Microsoft.ACE.OLEDB.12.0'

set @provstr = 'Excel 12.0'

SET @datasrc ='C:\Data\BlockGroups_2010\AKblockgroup.xls'

EXEC sp_addlinkedserver @server,@srvproduct,@provider,@datasrc,NULL,@provstr

EXEC dbo.sp_AddLinkedSrvLogin @server, FALSE, NULL, Admin, NULL

--what spreadsheets (table equivilents are available?

EXEC sp_tables_ex 'MyExcelACE'

--you MUST know the name of the spreadsheet;

--spreadsheet name has a dollar sign at the end of it!

--I've personally never gor a spreadsheet that has a space in it

--for example "Activity Data" =ActivityData$ never got [Activity Data$] or [Activity Data]$ to work

--to work, so I end up editing the spreadsheet to remove spaces if that happens.

select * from MyExcelACE...ActivityData$;

select * from MyExcelACE...Sheet1$;