Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Create a trail (2003)

    Hi,

    Is it possible that when a document is opened via a macro it can record some data to either a database table, or to a text file that I can later import to a database. I want to timestamp when the file was last modified.

    Many thanks

    Kevin
    Regards
    Gerbil (AKA Kevin)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create a trail (2003)

    You could, but just opening a document doesn't mean that it will be modified. the user could close it without having changed anything, or without saving changes.
    Windows itself keeps track of when a document was last modified, you don't need code or a database.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create a trail (2003)

    Thanks for the reply

    I need to know within my database because I will use the data to flag up incomplete clients that I need to update there profiles, Currently we look manually looking at the same records time and time again.

    The type of situation were in would mean that it is highly unlikely for a letter to be opened by the script in word unless it was to be edited, from the validation side we open the letters within the database and audit that we looked at it.

    Simply put if they used the macro in word they would have edited it, so if using the modified date is not possible then on open is good enough.

    Thanks for your help, I am amazed by how much information you know!

    Kevin
    Regards
    Gerbil (AKA Kevin)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create a trail (2003)

    OK. You can execute an SQL statement that inserts a record into a table in a database. For example:

    - Set a reference to the Microsoft DAO 3.6 Object Library in Tools | References in the Visual Basic Editor.
    - Add code like this to what you already have. You will obviously have to modify it to suit your needs:
    <code>
    Dim dbs As DAO.Database
    Dim strSQL As String
    Set dbs = DBEngine.OpenDatabase("MyServerMyShareMyDatabasesL ogfile.mdb")
    strSQL = "INSERT INTO tblLog (FileName, TimeStamp) VALUES (" & Chr(34) & _
    ActiveDocument.FullName & Chr(34) & ", Now())"
    dbs.Execute strSQL, dbFailOnError
    dbs.Close
    Set dbs = Nothing
    </code>
    This assumes that you have
    - A database MyServerMyShareMyDatabasesLogfile.mdb
    - A table tblLog in the database
    - Fields FileName (text) and TimeStamp (date/time) in the table.

  5. #5
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create a trail (2003)

    That is superb thank you very much I will try it out later.

    Thanks

    Kevin
    Regards
    Gerbil (AKA Kevin)

  6. #6
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create a trail (2003)

    Hi,

    Hans thanks for the code, I am having some difficulty getting it to work. dbs.Execute strSQL, dbFailOnError keeps halting the code. Error message

    Run time error '3134'
    Syntax error in INSERT INTO statement

    I've tried alsorts of things but can't get it to work.

    Thanks again

    Kevin
    Regards
    Gerbil (AKA Kevin)

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create a trail (2003)

    Could you do the following:

    - Comment out the following line by inserting an apostrophe in front of it:

    dbs.Execute strSQL, dbFailOnError

    - Insert the following line above it:

    Debug.Print strSQL

    When you run the code, you should see the SQL statement in the Immediate window. Please copy it into a reply.

  8. #8
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create a trail (2003)

    Thanks Hans

    INSERT INTO tblLog (FileName, TimeStamp) VALUES ("C:ProfileskemorrDesktopDoc1.doc", Now())

    Kevin
    Regards
    Gerbil (AKA Kevin)

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create a trail (2003)

    Is your table really named tblLog?
    Are the relevant fields really named FileName and TimeStamp?

  10. #10
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create a trail (2003)

    100% yes

    I really check them carfully, I made a test database with just that one table in. I added the database location to my safe list because macros with stopping it fully open

    Would creating a text file be simpler that can be imported to access later.

    Thanks
    Regards
    Gerbil (AKA Kevin)

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create a trail (2003)

    The problem is that TimeStamp is a reserved word in Jet SQL, so you must put square brackets around it in the SQL string: [TimeStamp]

  12. #12
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create a trail (2003)

    Hans

    If I remove the TimeStamp part and only use the FileName, but set it to TEST instead of ActiveDocument.FullName it works, if I ask it to put Now() in FileName it works, if I rename FileName in the table to TimeStamp it fails. Also if I have more than one field (FileName, Filename2) it still fails

    Thanks

    Kevin
    Regards
    Gerbil (AKA Kevin)

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create a trail (2003)

    What I meant is that you should use
    <code>
    strSQL = "INSERT INTO tblLog (FileName, [TimeStamp]) VALUES (" & Chr(34) & _
    ActiveDocument.FullName & Chr(34) & ", Now())"
    </code>
    The square brackets around TimeStamp are needed because TimeStamp is a reserved word.

  14. #14
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create a trail (2003)

    Thanks, Hans it works well.

    Just Out of curiosity, I you wanted to store data into a text file how would you go about it. I'm wondering about the speed it will take to append to the table once it gets bigger because it will get about 500 records added per day.

    I could import and clear a text file.

    Just a thought.

    Many thanks

    Kevin
    Regards
    Gerbil (AKA Kevin)

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create a trail (2003)

    You'd use something like

    Const strLog = "CataLog.txt"
    Dim f As Integer
    f = FreeFile
    Open strLog For Append As #f
    Print #f, ActiveDocument.FullName, Now()
    Close #f

Page 1 of 3 123 LastLast

Posting Permissions

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