Friday, July 10, 2009

VBScript: ADOX Create Access Database, Table and Insert Row

Adapted from portions of code on MSDN at http://msdn.microsoft.com/en-us/library/ms681497(VS.85).aspx

'----------------------------------------------------------------
' first we create the database file itself
'----------------------------------------------------------------

Const dbFile = "c:\database.mdb"
strDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbFile

Const adVarWChar = 202
Const adSingle = 4
Const adLockOptimistic = 3
Const adOpenDynamic = 2
Const adCmdTable = &H0002

On Error Resume Next
wscript.echo "info: creating database..."
Dim objCatalog
Set objCatalog = CreateObject("ADOX.Catalog")
objCatalog.Create strDSN
If err.Number = 0 Then
wscript.echo "info: database created successfully"
Else
wscript.echo "fail: error = " & err.Number & " (" & err.Description & ")"
wscript.Quit(1)
End If

'----------------------------------------------------------------
' next we create a table in the new database
'----------------------------------------------------------------

wscript.echo "info: creating table..."

Set objTable = CreateObject("ADOX.Table")
objCatalog.ActiveConnection = strDSN

With objTable
.Name = "tbl_Employees"
.Columns.Append "empID", adVarWChar, 12
.Columns.Append "fname", adVarWChar, 50
.Columns.Append "lname", adVarWChar, 50
.Columns.Append "dept", adVarWChar, 50
.Columns.Append "phone", adVarWChar, 50
.Columns.Append "email", adVarWChar, 50
.Columns.Append "jobID", adSingle
.Columns.Append "birthDay", adVarWChar, 20
End With

objCatalog.Tables.Append objTable

If Err.Number <> 0 Then
wscript.echo "fail: " & err.Number & ": " & err.Description
Set objTable = Nothing
Set objCatalog = Nothing
wscript.Quit(1)
Else
wscript.echo "info: table created successfully"
End If

Set objTable = Nothing
Set objCatalog = Nothing

'----------------------------------------------------------------
' comment: SQL INSERT
'----------------------------------------------------------------
wscript.echo "info: entering a new row..."

On Error Resume Next
Set rs = CreateObject("ADODB.Recordset")
rs.Open "tbl_Employees", strDSN, adOpenDynamic, adLockOptimistic, adCmdTable

If err.Number <> 0 Then
rs.Close
Set rs = Nothing
wscript.echo "fail: error = " & err.Number & " (" & err.Description & ")"
wscript.Quit(1)
End If

rs.AddNew
rs("empID").value = "100400"
rs("fname").value = "JOHN"
rs("lname").value = "DOE"
rs("dept").value = "SALES"
rs("phone").value = "800-555-1212"
rs("email").value = "john_doe@dumbass.local"
rs("jobID").value = 4002
rs("birthDay").value = "#03/01/1966#"
rs.Update
If err.Number <> 0 Then
rs.Close
Set rs = Nothing
wscript.echo "fail: error = " & err.Number & " (" & err.Description & ")"
wscript.Quit(1)
End If

rs.Close
Set rs = Nothing

No comments:

Post a Comment