FANDOM


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


Ad blocker interference detected!


Wikia is a free-to-use site that makes money from advertising. We have a modified experience for viewers using ad blockers

Wikia is not accessible if you’ve made further modifications. Remove the custom ad blocker rule(s) and the page will load as expected.