Here's a solution you can use provided that:
1. MS Access is installed on the SQL Server machine.
2. You can create a text file and write into it from your SQL Server.
3. You can start MS Access from your SQL Server.
Step 1:
a) Create an Access database and name it ImportFromSQLServer.mdb or ImportFromSQLServer.accdb
b) In this database, create a Standard Module and paste this code into it:
Private m_strConnection As String
Private m_strDbName As String
Private Sub CreateDatabase()
If Len(Dir(m_strDbName)) > 0 Then Kill m_strDbName
Application.DBEngine.CreateDatabase m_strDbName, dbLangGeneral
End Sub
Private Function GetTableList() As Variant
Const c_SQL As String = "SELECT name FROM sys.objects WHERE type = 'U';"
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim var As Variant
Set qdf = CurrentDb.CreateQueryDef("")
With qdf
.Connect = m_strConnection
.SQL = c_SQL
Set rst = .OpenRecordset
With rst
If Not .EOF Then
.MoveLast
ReDim var(0 To .RecordCount - 1)
.MoveFirst
Do While Not .EOF
var(.AbsolutePosition) = .Fields(0).Value
.MoveNext
Loop
End If
.Close
End With
.Close
End With
Set rst = Nothing
Set qdf = Nothing
GetTableList = var
End Function
Private Sub ImportTable(ByVal TableName As String)
Const c_SQL1 As String = "SELECT * FROM @T;"
Const c_SQL2 As String = "SELECT * INTO @T IN '@D' FROM qryImport;"
Dim qry As DAO.QueryDef
If DCount("*", "MSysObjects", "name='qryImport'") > 0 Then DoCmd.DeleteObject acQuery, "qryImport"
Set qry = CurrentDb.CreateQueryDef("qryImport")
With qry
.Connect = m_strConnection
.SQL = Replace(c_SQL1, "@T", TableName)
End With
CurrentDb.Execute Replace(Replace(c_SQL2, "@T", TableName), "@D", m_strDbName), dbFailOnError
DoCmd.DeleteObject acQuery, "qryImport"
End Sub
Public Function StartUp()
Dim var As Variant
Dim str As String
Dim intHandle As Integer
Dim i As Long
var = Split(Command$)
For i = 0 To UBound(var)
If Left(var(i), 5) = "/INI:" Then
str = Trim(Mid(var(i), 6))
Exit For
End If
Next i
If Len(Dir(str)) = 0 Or Len(str) = 0 Then str = Replace(CurrentDb.Name, ".mdb", ".ini")
intHandle = FreeFile
Open str For Input As #intHandle
Do Until EOF(intHandle)
Line Input #intHandle, str
Select Case Left(str, 5)
Case "/DBN:": m_strDbName = Trim(Mid(str, 6))
Case "/CNN:": m_strConnection = Trim(Mid(str, 6))
End Select
Loop
Close #intHandle
var = GetTableList
CreateDatabase
For i = 0 To UBound(var)
ImportTable var(i)
Next i
Application.Quit
End Function
c) Create a Macro:
- Action: RunCode
- Function Name: StartUp()
- Name: AutoExec
d) Compile, save and close the Access database.
Step 2:
Create a stored procedure that can create a text file:
a) By default, this text file will be named ImportFromSQLServer.ini and will be located in the same folder as the database you created in step 1a.
b) Write two lines in the file ImportFromSQLServer.ini:
- One line begins with /CNN: followed by the connection string (e.g. /CNN:ODBC;DRIVER={SQL Server};SERVER=ISKENDER;DATABASE=Sales;Trusted_Connection=Yes;).
- A second line begins with /DBN: followed by the full path to the Access database where you want to export the tables (e.g. /DBN:U:\Access\Sales.mdb).
Step 3:
Start Access from a stored procedure, passing the full path to the Access database you created in step 1a.
Note: You can also create a batch file to start Access and have this batch file execute from the SQL Server.
Example (form Access 11 (MS Office 2003):
- File name: StartAccess.cmd
- File contents: "C:\Program Files\Microsoft Office\OFFICE11\msaccess.exe" "U:\Access\ImportFromSQLServer\ImportFromSQLServer.mdb"
Notes:
1) If you want to specify another .ini file than the default one, add the parameter /INI: followed by the full path to the .ini file on the command line starting Access.
2) You'll need to refine the process if you want to import the indexes defined on the SQL Server tables.