Want to change some data in a mdb database via a VBscript? Great when you’ve left a client with the lights switched on, or even a boolean set to true when it should be false.
All you need is the path to the database, the table name and the field you want to edit. You may need to build up a little query rather than the simple example of “SELECT * FROM tblTest”
Private FileXists
Const AppPath ="C:Pathtomydatabase.mdb"
Private Sub FileX()
Set objFSO = CreateObject("Scripting.FileSystemObject")
If objFSO.FileExists(AppPath) Then
FileXists=1
Else
FileXists=0
End If
End Sub
Private Sub UpdateDB()
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & AppPath & ";Mode=Read|Write"
objRecordset.CursorLocation = adUseClient
objRecordset.Open "SELECT * FROM tblTest" , objConnection, _
adOpenStatic, adLockOptimistic
objRecordset("BlnTest") = True
objRecordset.Update
objRecordset.Close
objConnection.Close
End Sub
Call FileX
if FileXists = 1 then
Call UpdateDB
MsgBox "The BlnTest field has been updated"
else
MsgBox "Something went wrong"
end if
Leave a Reply
Want to join the discussion?Feel free to contribute!