Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2008
    Posts
    10

    Unanswered: updating MS Access DB with VBScript

    Hi you all.
    I'm trying to modify an mdb file using VBScript on a Windows Server 2008 platform.

    I was trying to run the following script, taken from Microsoft Technet:

    ================================================== =====
    On Error Resume Next

    Const adOpenStatic = 3
    Const adLockOptimistic = 3

    Set objConnection = CreateObject("ADODB.Connection")
    Set objRecordSet = CreateObject("ADODB.Recordset")

    objConnection.Open _
    "Provider=Microsoft.Jet.OLEDB.4.0; " & _
    "Data Source=c:\scripts\inventory.mdb"

    objRecordSet.Open "SELECT * FROM GeneralProperties" , _
    objConnection, adOpenStatic, adLockOptimistic

    objRecordSet.MoveFirst

    Do Until objRecordSet.EOF
    Wscript.Echo objRecordSet.Fields.Item("ComputerName")
    objRecordSet.MoveNext
    Loop
    ================================================== ===

    The problem is that it doesn't run at all.. No error message no nothing.

    I tried the same thing on my local computer, and it worked just fine..

    What is the diffenrence? what am I missing using the server??
    Is there a dll I need to register on the server?

    Thank you in advance, Ishay.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Is this the correct location of the mdb?

    objConnection.Open _
    "Provider=Microsoft.Jet.OLEDB.4.0; " & _
    "Data Source=c:\scripts\inventory.mdb"

    Some network guys disable the WScript or limit it in some way on some work computers so check if that's an issue also (which might prevent the script from even running.) - ie. make a new script and just have it produce a msgbox "Hello".
    Last edited by pkstormy; 11-23-08 at 08:48.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Here's the code I've used to update an mdb table via a vb script (grabbing the current loginID to update the table for that loginID with a status of "loggedIn"):

    Set WSNet = CreateObject("WScript.Network")

    varUserName = WSNet.UserName
    'varUserdomain = WSNet.UserDomain
    'varNBName = WSNet.ComputerName

    Set WSNet = Nothing

    Set MyConn = CreateObject("ADODB.Connection")
    Set RS = CreateObject("ADODB.Recordset")

    MyConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\AccessExamples\CustomersXPSample.mdb"

    Set RS = MyConn.Execute("UPDATE Users SET LoginStatus ='" & "LoggedIn" & "' WHERE LoginID ='" & varUserName & "'")

    MyConn.Close

    Set RS = Nothing
    Set MyConn = Nothing
    Last edited by pkstormy; 11-23-08 at 08:41.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    And just an FYI, here's one to update a SQL Server table:

    Set WSNet = CreateObject("WScript.Network")

    varUserName = WSNet.UserName
    'varUserdomain = WSNet.UserDomain
    'varNBName = WSNet.ComputerName

    Set WSNet = Nothing

    Set MyConn = CreateObject("ADODB.Connection")
    Set RS = CreateObject("ADODB.Recordset")

    MyConn.open = "DRIVER={SQL Server};"_
    & "SERVER=SQLSERVER;"_
    & "DATABASE=PKTime;"_
    & "OPTION=35;"

    sql_query = "UPDATE dbo.Users SET LoginStatus ='" & "LoggedIn" & "' WHERE LoginID ='" & varUserName & "'"

    'ex: sql_query = "UPDATE dbo.Users SET LoginStatus ='" & "LoggedIn" & "' WHERE LoginID ='" & "PaulK" & "'"
    'ex for logout: sql_query = "UPDATE dbo.Users SET LoginStatus ='" & "LoggedOut" & "' WHERE LoginID ='" & varUserName & "'"
    'ex: sql_query = "UPDATE dbo.Users SET LoginStatus ='" & "LoggedOut" & "' WHERE LoginID ='" & "PaulK" & "'"

    MyConn.Execute sql_query
    MyConn.Close

    Set RS = Nothing
    Set MyConn = Nothing
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    You can also add in a line of code such as:

    msgbox "I'm here"

    to check where the code might be stopping.

    Or perhaps this...

    msgbox objRecordSet.Fields.Item("ComputerName")

    versus this....

    Wscript.Echo objRecordSet.Fields.Item("ComputerName")
    Last edited by pkstormy; 11-23-08 at 08:48.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Oct 2008
    Posts
    10

    Didn't help

    Hi. Thanks for your help.
    I was trying to use your code instead of mine, and it didn;t work as well.
    It gave out the attached message.

    I guess this is the same problem why the other method didn't work..
    Attached Thumbnails Attached Thumbnails Noname.jpg  

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Are you sure MDAC is installed on that server computer? That error looks like a provider/driver issue which is usually related to the installation of MDAC. If it is installed, does it have the same providers as yours?
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Oct 2008
    Posts
    10
    I'm not sure, and I guess that is my problem.
    Can you refer me to where can I check if it's installed, and where can I download it?

    I remind you that it's a w2k8 server..
    Thanks a lot, Ishay.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •