Enable errors display on the production server

They following lines in the web.config are the key to finding errors on the production servers.

<!-- Web.Config Configuration File --><configuration> <system.web> <customErrors mode="Off"/> </system.web></configuration>

Caution:
Don't forget to return the mode into "RemoteOnly" immediately after finishing, that can be a real threat to your application if left behind.

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
GO
RECONFIGURE
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE


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)".

http://connect.microsoft.com/SQLServer/feedback/details/284113/import-excel-data-with-openrowset-on-production-server


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

Detecting Access version which created the file

Although it might be so trivial as the title implies; however, it took me really hours deceived that this can be achieved through a straight forward method like looking up the file properties dialog or finding it somewhere on opening this file in the access application.

The outcome for this research was the following windows VB Script for opening an Access file and displaying the original Access version utilized based on a property called FileFormat.


AccessFieVersionDetector.vbs
--------------------------------

' Initialization
ScriptName = "** Access File Version Detector **"
MsgBox "Please select the Access file...", 0, ScriptName

' Input Access file path
Set ObjFSO = CreateObject("UserAccounts.CommonDialog")
ObjFSO.InitialDir = "c:\"
InitFSO = ObjFSO.ShowOpen
If InitFSO = False Then
    MsgBox "Script Error: Please select a file!", 0, ScriptName
    Wscript.Quit
Else
    MsgBox "You selected the file: " & ObjFSO.FileName, 0, ScriptName
End If

' Open Access file specified
Set objAccess = CreateObject("Access.Application")
objAccess.OpenCurrentDatabase(ObjFSO.FileName)
CurrentFileFormat = objAccess.CurrentProject.FileFormat

' Display the equivalent Access version utilized to create this file
Select Case CurrentFileFormat
    Case 2
MsgBox "It is Microsoft Access 2", 0, ScriptName
    Case 7
MsgBox "It is Microsoft Access 95", 0, ScriptName
    Case 8
MsgBox "It is Microsoft Access 97" , 0, ScriptName
    Case 9
MsgBox "It is Microsoft Access 2000", 0, ScriptName
    Case 10
MsgBox "It is Microsoft Access 2003", 0, ScriptName
    Case 11
MsgBox "It is Microsoft Access 2007", 0, ScriptName
    Case 12
MsgBox "It is Microsoft Access 2010", 0, ScriptName
    Case 13
MsgBox "Not yet! ;)" + Chr(13) & Chr(10) + "It is Unknown file type!", 0, ScriptName
Case Else
MsgBox "It is unknown file type!"
End Select