Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Wikltshire UK
    Posts
    152
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Make Table Code + Check not already created. (2000/2003)

    I'm in the doodoo.....

    I have this table which is created from a template (Daily File Template) (IE copied and then renamed (="File " & Format (Date(), "dd-mm-yyy"), we then enter the data and the file is closed and exported. We just had a disaster where an operator pushed the button at the end of the day and deleted the File [date], and recreated a blank then exported it and obviously we lost all the data. Hurumph.....

    I do all this using the standard Macro Commands:

    Copy Object (Then Renaming as Above)
    Open Table.

    Whats the best way to either do this with a RunCode Command (Ie create table and also check its not already created using VB) or to check using the macro resources that its not already been created and then not over write.

    Help I've got to get this sorted because if it happens again, I'll never forgive myself.

    Praying for an Easy solution. (if you just tell me how to Copy a table in VB and then rename it and how to check it dosn't already exist that'd be perfect I can do the rest.)

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

    Re: Make Table Code + Check not already created. (2000/2003)

    You can put the following function in a standard module:

    Public Function TableExists(TableName As String) As Boolean
    Dim obj As AccessObject, dbs As Object
    Set dbs = Application.CurrentData
    For Each obj In dbs.AllTables
    If obj.Name = TableName Then
    TableExists = True
    Exit For
    End If
    Next obj
    End Function

    and use it like this in a procedure:

    Dim strName As String
    strName = "File " & Format(Date, "dd-mm-yyy")
    If TableExists(strName) Then
    MsgBox "A table with the name " & strName & " already exists!", vbExclamation
    Else
    ' do stuff
    End If

  3. #3
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Wikltshire UK
    Posts
    152
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Make Table Code + Check not already created. (2000/2003)

    Hans "Your my Own Personal Jesus Christ". Just don't follow the white rabbit, I still need you.

    Tx.

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

    Re: Make Table Code + Check not already created. (2000/2003)

    Thanks for the compliment, but don't overdo it... <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

Posting Permissions

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