CODEWITHSUNDEEP
vbscript
addcom
addcom

Reputation: 1

vbscript ADO Recordset Update

I am trying to update paths in access mdb database using ADO and vb6 without success.

The scripts are below. The line Rs1(columnName) = Replace( Rs1(columnName),oldPath,newPath) causes vbscript runtime err invalid use of Null.

Put simply, I want to update all tables that have strings like \\server2 to \\DBSE-46\. I am running the script on win7 64bit as 

c:\windows\syswow64\cscript.exe C:\SQLTest\HarishScripts\DatabaseAccessProg6.vbs >> C:\SQLTest\HarishScripts\DatabaseAccessProg6.txt

Option Explicit
WScript.Echo "START of ADO access program...."
Dim DBpath
Dim tableName
Dim columnName
Dim oldPath
Dim newPath
'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
' Set all external variables here...
DBpath = "C:\DBTest;"
tableName = "Test"
columnName = "Path"
oldPath = "\\SERVER2\"
newPath = "\\DBSE-46\"
'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
Dim Rs1
Set Rs1 = CreateObject("ADODB.Recordset")

Dim i 
Dim AccessConnect 
AccessConnect = "Driver={Microsoft Access Driver (*.mdb)};" & _
                 "Dbq=MedDataSource.mdb;" & _
                 "DefaultDir=" &  DBpath & _
                 "Uid=Admin;Pwd=;"


'--------------------------
' Recordset Object Method
'--------------------------

' Recordset Open Method #4:  Open w/o Connection & w/Connect String

Dim sqlStmt
sqlStmt = "SELECT * FROM " & tableName 

' use LockTypeEnum.adLockOptimistic = 3. This allows update of the recordset.
Rs1.LockType = 3
Rs1.Open sqlStmt, AccessConnect


Do Until Rs1.EOF 
'WScript.Echo Rs1("Path")
if (Rs1(columnName) = NULL) Then
  End If
  Rs1(columnName) = Replace( Rs1(columnName),oldPath,newPath)
Rs1.MoveNext 
Loop 

' Close the recordset...
Rs1.Close
Set Rs1 = Nothing


WScript.Echo "..."
WScript.Echo "..."
WScript.Echo "DONE!"

Upvotes: 0

Views: 1645

Answers (1)

Ekkehard.Horner
Ekkehard.Horner

Reputation: 38745

Use the IsNull function to check for Null values. In my older local .chm there is even a paragraph

Use the IsNull function to determine whether an expression contains a Null value. Expressions that you might expect to evaluate to True under some circumstances, such as If Var = Null and If Var <> Null, are always False. This is because any expression containing a Null is itself Null, and therefore, False.

Work on your understanding of the If clause. In

if (Rs1(columnName) = NULL) Then
End If
Rs1(columnName) = Replace( Rs1(columnName),oldPath,newPath)

the last statement will be executed regardless of the content of Rs1(columnName). So do

if Not IsNull(Rs1(columnName)) Then
   Rs1(columnName) = Replace( Rs1(columnName),oldPath,newPath)
End If

Upvotes: 1

Related Questions