Script to Import MS-Access 2007 data into SQL Server 2005 table

Before you start throwing your hands in the air and exclaiming “see? I told you!  This guy is a dumbass!  Anyone with any experience using SQL 2005 knows you use SSIS to do this!”, I know.  And I agree: it is the way to go.  But (yes, there is a but) you may be forced to deal with this situation without having SSIS at your disposal.  I won’t get into reasons why this could be, but suffice it to say it does happen.  So there are options a-plenty for you to pull this off.  One of them is using a script.  Any script.  It doesn’t matter what language really, as long as you can connect the pieces together and move data across.

This is just ONE example, using VBScript and ADO to connect to a Microsoft Access 2007 database file, fetch the results of a query, connect to a SQL 2005 instance, truncate a temp table, copy over the query record data, then roll that over into a final table.

Why the temp table?  Because it adds a buffer layer of safety and performance in the middle.  On the safety side, it helps to ensure you get valid results from the source query before doing anything more.  If that fails, you exit and notify that the source shit the bed for some reason.  If it succeeds, you pull the data over into a temp table first, then copy it from there to the final table.  This last step provides a boost in performance.  If you have applications or other SQL views relying on the final table data, you want to keep the window of time during the import as short as possible to avoid interrupting those other services.  It’s much faster to copy from one SQL table to another, especially within the same database schema, than it is to bring them over from a remote Access query.  So, in short, you bring it over into a temporary place, making sure you got it all, then you swap it into the final location.

There are DBA’s that will disagree with this, and those that will agree.  I side with the latter.  So, I’ll shut up and drop the code below.  Remember: as always, this is for example only.  No garantees are given of any kind.  Be sure to modify to suit your needs and test test test test before using in any production environment.

'****************************************************************
' Filename..: access_2_sql.vbs
' Author....: David Stein
' Date......: 08/18/2009
' Purpose...: import ms-access 2007 data into a sql 2005 table
' SQL.......: (source) MS Access 2007 .accdb file
' SQL.......: (target) SERVER\INSTANCE:DatabaseName
'****************************************************************
Option Explicit

Const sourceDB    = "\\server\sharename\folder\ms_acces_data.accdb"
Const sourceTable = "qry_MyQuery"
Const targetSrv   = "SERVER2\SQL_INSTANCE"
Const targetDB    = "SQL_Database_Name"
Const tempTable   = "dbo.tbl_TempTable"
Const finalTable  = "dbo.tbl_FinalTable"

' required if using SQL security (if you use AD/Trusted, modify the dsn below)
Const dbuser = "SQL_USERNAME"
Const dbpwd  = "SQL_PASSWORD"

Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3
Const adLockReadOnly = 1
Const adLockPessimistic = 2
Const adLockOptimistic = 3
Const adLockBatchOptimistic = 4
Const adUseServer = 2
Const adUseClient = 3
Const adCmdText = &H0001
Const adCmdTable = &H0002
Const adCmdFile = &H0100
Const adStateClosed = &H00000000
Const adStateOpen = &H00000001

Dim sourceDSN, targetDSN, conn1, cmd1, rs1, rs2, query1, conn2, icount, query

sourceDSN = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sourceDB
targetDSN = "DRIVER=SQL Server;SERVER="&targetSrv&";database="&targetDB&";UID="&dbuser&";PWD="&dbpwd&";"

On Error Resume Next

'----------------------------------------------------------------

wscript.echo "info: processing initiated at " & Now

'----------------------------------------------------------------
' truncate destination (target) table
'----------------------------------------------------------------

wscript.echo "info: truncating table..."
Set conn2 = CreateObject("ADODB.Connection")
conn2.Open targetDSN
conn2.Execute "TRUNCATE TABLE " & tempTable & ""
conn2.Close
Set conn2 = Nothing
wscript.echo "info: table has been truncated"

'----------------------------------------------------------------
' open connection to source database
'----------------------------------------------------------------

Set conn1 = CreateObject("ADODB.Connection")
Set cmd1  = CreateObject("ADODB.Command")
Set rs1   = CreateObject("ADODB.Recordset")

query1 = "SELECT * FROM [" & sourceTable & "]"

wscript.echo "info: opening connection to source..."
wscript.echo "info: source database is " & sourceDB
wscript.echo "info: source table is " & sourceTable

conn1.Open sourceDSN

rs1.CursorLocation = adUseClient
rs1.CursorType = adOpenStatic
rs1.LockType = adLockReadOnly

Set cmd1.ActiveConnection = conn1

cmd1.CommandType = adCmdText
cmd1.CommandText = query1
rs1.Open cmd1

wscript.echo "info: sending query to source..."

If rs1.BOF And rs1.EOF Then
    rs1.Close
    conn1.Close
    Set rs1 = Nothing
    Set cmd1 = Nothing
    Set conn1 = Nothing
    wscript.echo "fail: no records found"
    wscript.quit(1)
Else
    cols = rs1.Fields.Count
    rows = rs1.RecordCount
    wscript.echo "info: " & rows & " records were returned"
End If

'----------------------------------------------------------------
' open connection to target database
'----------------------------------------------------------------
wscript.echo "info: opening connection to destination..."

Set rs2 = CreateObject("ADODB.Recordset")
On Error Resume Next

rs2.Open tempTable, targetDSN, adOpenDynamic, adLockOptimistic, adCmdTable

If err.Number <> 0 Then
    rs2.Close
    Set rs2 = Nothing
    wscript.echo "fail: unable to connect to destination..."
    wscript.echo "fail: rs2 (" & err.Number & "): " & err.Description
    err.Clear
    wscript.quit(2)
End If

'----------------------------------------------------------------
' SOURCE column names
'----------------------------------------------------------------
' EmpID
' FirstName
' LastName
' Department
' ManagerID
' Email
' WorkPhone
' HireDate
'----------------------------------------------------------------

'----------------------------------------------------------------
' DESTINATION and FINAL column names
'----------------------------------------------------------------
' emp_id
' f_name
' l_name
' dept
' mgr_id
' email
' work_phone
' hire_date
'----------------------------------------------------------------

wscript.echo "info: importing source records..."

icount = 0

Do Until rs1.EOF
    rs2.AddNew
    rs2("emp_id").value = rs1("EmpID").value
    rs2("f_name").value = rs1("FirstName").value
    rs2("l_name").value = rs1("LastName").value
    rs2("dept").value   = rs1("Department").value
    rs2("mgr_id").value = rs1("ManagerID").value
    rs2("email").value  = rs1("Email").value
    rs2("work_phone").value = rs1("WorkPhone").value
    rs2("hire_date").value  = rs1("HireDate").value
    rs2("date_added").value = Now
    rs2("comment").value = ""
    rs2.Update
    rs1.MoveNext
    icount = icount + 1
Loop

wscript.echo "info: " & icount & " records were imported"

'----------------------------------------------------------------
' close transfer connections and release objects
'----------------------------------------------------------------

rs1.Close
conn1.Close
Set rs1 = Nothing
Set cmd1 = Nothing
Set conn1 = Nothing

rs2.Close
Set rs2 = Nothing

'----------------------------------------------------------------
' truncate final table and import from temp table
'----------------------------------------------------------------

If icount > 0 Then
    wscript.echo "info: truncating final destination table for rollover..."
    Set conn2 = CreateObject("ADODB.Connection")
    conn2.Open targetDSN
    conn2.Execute "TRUNCATE TABLE " & finalTable & ""

    wscript.echo "info: table [" & finalTable & "] has been truncated"
	
    query = "INSERT INTO " & finalTable & " (" & _
    "emp_id, f_name, l_name, dept, mgr_id, email, work_phone, hire_date," & _
    "date_added, comment) " & _
    "(SELECT emp_id, f_name, l_name, dept, mgr_id, email, work_phone, " & _
    "hire_date, date_added, comment FROM " & tempTable & ")"
	
    wscript.echo "info: rolling data from temp to final..."
    conn2.Execute query, , adCmdText

    conn2.Close
    Set conn2 = Nothing
    wscript.echo "info: rollover completed"

Else
    wscript.echo "warn: no data was imported so temp was not rolled into final"
End If

'----------------------------------------------------------------

wscript.echo "info: processing completed at " & Now