SCRIPT: Update SQL Database From an Access Saved Query

Here we will go through a VB script that reades the result of a saved query created on a Microsoft Access Database

VDM_VacationApproval: The saved query in Access DB.

SQL 2005 case: (You may need to install SQL 2005 Native Client (32 bit  64 bit) to be able to use ODBC Driver for SQL 2005)

'On Error Resume next '################ '#Access Portion# '################ Dim connection_string1 : connection_string1 = _ "provider=microsoft.jet.oledb.4.0;" _ & "data source=\\fileserver\Departments\HR\HR\HR.mdb" Dim conn1 : Set conn1 = createobject("adodb.connection") conn1.open connection_string1

strACCQuery = "SELECT * FROM VDM_VacationApproval" Set rs1 = createobject("adodb.recordset") rs1.Open strACCQuery, conn1, 3, 3

'############# '#SQL Portion# '############# '''Delete the table content Dim conn2 : Set conn2 = createobject("adodb.connection") Dim connection_string2 : connection_string2 = ("Driver={SQL Native Client};Server=SQL01;Database=HRDB;Uid=hruser;Pwd=hrpassword;")

conn2.open connection_string2

strSQLQuery = "DELETE FROM HRtable" Set rs2 = createobject("adodb.recordset") rs2.Open strSQLQuery, conn2, 3, 3

'''Add the data collected from the access DB

Dim col1, col2, col3, col4, col5, col6, col7, col8

While Not RS1.EOF col1 = rs1("EmployeeName") col2 = rs1("department") col3 = rs1("Date") col4 = rs1("vacation") col5 = rs1("manager") col6 = rs1("ImmediateManagerApproval") col7 = rs1("employeeID") col8 = rs1("ImmediateManagerDisapproval")

'''To handle special case when a value in col5 is null If col5 <> "" Then col5 = REPLACE(col5,"'","''")

'''To handle mapping of access TRUE/FALSE to SQL 1/0 If col6 = "TRUE" Then col6 = 1 Else col6 = 0 If col8 = "TRUE" Then col8 = 1 Else col8 = 0

'WScript.Echo col1 & col2 & col3 & col4 & col5 &col6 & col7 & col8 strSQLQuery2 = "INSERT INTO HRtable VALUES ('" & _ col1 & "', '" & col2 & "', '"& col3 &"', '" & col4 & "', '" & col5 & "', '" & col6 & "', '" & col7 & "', '" & col8 & "')"

Set rs3 = CreateObject("ADODB.Recordset") rs3.Open strSQLQuery2, conn2, 3, 3

RS1.MoveNext Wend

conn1.close Set conn1 = Nothing conn2.close Set conn2 = Nothing

SQL 2008 case: (You may need to install SQL 2008 Native Client (32 bit 64 bit) to be able to use ODBC Driver for SQL 2008)

'On Error Resume next '################ '#Access Portion# '################ Dim connection_string1 : connection_string1 = _ "provider=microsoft.jet.oledb.4.0;" _ & "data source=\\fileserver\Departments\HR\HR\HR.mdb" Dim conn1 : Set conn1 = createobject("adodb.connection") conn1.open connection_string1

strACCQuery = "SELECT * FROM VDM_VacationApproval" Set rs1 = createobject("adodb.recordset") rs1.Open strACCQuery, conn1, 3, 3

'############# '#SQL Portion# '############# '''Delete the table content Dim conn2 : Set conn2 = createobject("adodb.connection") Dim connection_string2 : connection_string2 = ("Provider=SQLNCLI10;Data Source=tcp:GB-PSC-HYPERV\PSCSQL;User ID=HRuser;Password=hrpassword;Initial Catalog=HRDB;")

conn2.open connection_string2

strSQLQuery = "DELETE FROM HRtable" Set rs2 = createobject("adodb.recordset") rs2.Open strSQLQuery, conn2, 3, 3

'''Add the data collected from the access DB

Dim col1, col2, col3, col4, col5, col6, col7, col8

While Not RS1.EOF col1 = rs1("EmployeeName") col2 = rs1("department") col3 = rs1("Date") col4 = rs1("vacation") col5 = rs1("manager") col6 = rs1("ImmediateManagerApproval") col7 = rs1("employeeID") col8 = rs1("ImmediateManagerDisapproval")

'''To handle special case when a value in col5 is null If col5 <> "" Then col5 = REPLACE(col5,"'","''")

'''To handle mapping of access TRUE/FALSE to SQL 1/0 If col6 = "TRUE" Then col6 = 1 Else col6 = 0 If col8 = "TRUE" Then col8 = 1 Else col8 = 0

'WScript.Echo col1 & col2 & col3 & col4 & col5 &col6 & col7 & col8 strSQLQuery2 = "INSERT INTO HRtable VALUES ('" & _ col1 & "', '" & col2 & "', '"& col3 &"', '" & col4 & "', '" & col5 & "', '" & col6 & "', '" & col7 & "', '" & col8 & "')"

Set rs3 = CreateObject("ADODB.Recordset") rs3.Open strSQLQuery2, conn2, 3, 3

RS1.MoveNext Wend

conn1.close Set conn1 = Nothing conn2.close Set conn2 = Nothing