Tuesday, 28 February 2012

Using SSMS with MS Access

The following is the steps to run a SQL statement against MS Access database using SSMS (SQL Server Management Studio)

1) Enable "Ad Hoc Distributed Queries

EXEC sp_configure 'show advanced options', 1
EXEC sp_configure 'Ad Hoc Distributed Queries', 1

2)Use" OpenDataSource"

Select   * 
FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0', 'Data Source=\\ServerName\FolderName\DBFileName.mdb')...[TableName] AS T

Unfortunately, finally I got:  :'(

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Cannot open database ''.  It may not be a database that your application recognizes, or the file may be corrupt.".
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".


Looks like the file should not be open by someone else!!!

No comments:

Post a Comment