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

    Unanswered: Updating Access from a script

    I need to write a script to be run on Windows that will ftp an Access database up to a web server but also update a table in that database with the date and time at the same time it is ftp'd. The ftp itself is obviously simple but how do I connect to and update the database first?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What scripting language are you using?
    George
    Home | Blog

  3. #3
    Join Date
    Oct 2008
    Posts
    2
    Can this be done from a simple DOS batch file?

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I'm fairly certain the answer is "no you cannot do this using DOS commands"...

    ...you can however call, for instance, a VBS file from the command line...
    George
    Home | Blog

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Code to update an Access table via a vbs script:

    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;"

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

    Set RS = MyConn.Execute("UPDATE Users SET LoginStatus ='" & "LoggedOut" & "' 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)

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    PK, you rock! Never thought to paste that in.

    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    wouldn't a simple dos batch file work called from the windows scheduler?

    agree DOS Batch is frowned on, and being phased out but AFAIK it stil works.. admittdly it doesn't have the flexibility and error handling of VBS but its simple
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Sure, but how is a DOS batch file meant to modify an Access database?

    I still use .BAT files for some things. Familiarity is surely the enemy of the winds of change.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by StarTrekker
    Sure, but how is a DOS batch file meant to modify an Access database?

    I still use .BAT files for some things. Familiarity is surely the enemy of the winds of change.
    good point, and well made
    attempting a recovery
    Ihaving copied the file I'd then open an instance of Access from within the batch file and run a specific macro (yuk) of procedure by use of the appropriate command line switches to set the audit event.
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    OMFG!












    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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