Results 1 to 6 of 6

Thread: archive table

  1. #1
    Join Date
    Jul 2004
    Posts
    214

    Unanswered: archive table

    All, I am using Access 2010. I have a split database (FE and BE) My main tables reside in the BE and linked to the FE. My main table is tblMstr. This table is updated maybe twice a month. But the user has requested that I keep a snapshot of the table as it was previously. So I created an archive process: I use the code below to rename the current table by adding the current date to make it the current archive table ex: tblMstr20140923. I have a temp table that I import the new data into. I then export a copy of the temp table renamed tblMstr to the backend. The problem is if and when the user makes an error importing data and have to reimport that same day and the code attempts to append the current date to tblMstr ; they get an error that a table with the same name i.e. tblMstr20140923 already exist. So they are not able to go forward with the import. I need something that says if the current name exists to overwrite the table like it does when we rename items in the database. I hope this helps you help me. Thanks
    Code:
    Dim dbBackend As DAO.Database
    	Dim strBackendDatabase As String
    	  strBackendDatabase = "C:\TestFolder\Sampledb.accdb"
    	  Set dbBackend = OpenDatabase(strBackendDatabase)
    	dbBackend.TableDefs("tblMstr").Name = "tblMstr" & Format(Date, "yyyymmdd")
    	  dbBackend.Close
      Set dbBackend = Nothing

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can test for the existence of the archived table:
    Code:
    Dim dbBackend As DAO.Database
    Dim tdf As DAO.TableDef
    Dim strBackendDatabase As String
    
    strBackendDatabase = "C:\TestFolder\Sampledb.accdb"
    Set dbBackend = OpenDatabase(strBackendDatabase) 
    For Each tdf In dbBackend.Tabledefs
        If tdf.Name =  "tblMstr" & Format(Date, "yyyymmdd") Then Exit For
    Next tdf
    If tdf Is Nothing Then   
        dbBackend.TableDefs("tblMstr").Name = "tblMstr" & Format(Date, "yyyymmdd")
    Else
        ' The archive table already exists: Take action (Rename, Delete, Warns...)
    End If
    dbBackend.Close
    Set dbBackend = Nothing
    Have a nice day!

  3. #3
    Join Date
    Jul 2004
    Posts
    214
    Thanks so much for your reply. It works that it does not error out but do nothing if file has the current date already. Thanks for your help.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  5. #5
    Join Date
    Sep 2014
    Posts
    37
    In a similar situation I added hours minutes to the archive name. The likelihood of a duplicate becomes very rare.

    dbBackend.TableDefs("tblMstr").Name = "tblMstr" & Format(Date, "yyyymmddhhmm")

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If you want to be absolutely suere of the uniqueness of the name, you can use a GUID as the suffix of the table name:
    Code:
    Option Compare Database
    Option Explicit
    
    Public Type GUID
        Data1(16) As Byte
    End Type
    
        Private Declare Function CoCreateGuid Lib "ole32.dll" (pGuid As GUID) As Long
        Private Declare Function StringFromGUID2 Lib "ole32.dll" (rguid As Any, ByVal lpstrClsId As Long, ByVal cbMax As Long) As Long
        Private Declare Function IIDFromString Lib "ole32.dll" (ByVal lpsz As String, ByVal lpiid As Long) As Long
    
    Public Function CreateGUID() As String
        
        Dim strGUID   As String
        Dim typGUID   As GUID
        Dim bytGUID() As Byte
        Dim lngRetVal    As Long
        
        lngRetVal = CoCreateGuid(typGUID)
        If lngRetVal = 0 Then
            bytGUID = String(50, 0)
            lngRetVal = StringFromGUID2(typGUID, VarPtr(bytGUID(0)), 50)
            If lngRetVal > 0 Then CreateGUID = Mid$(bytGUID, 1, lngRetVal - 1)
        Else
            Err.Raise 99, "CreateGUID", "CoCreateGuid() returned with the Error #" & lngRetVal
        End If
        
    End Function
    Code:
    dbBackend.TableDefs("tblMstr").Name = "tblMstr" & CreateGUID
    Have a nice day!

Posting Permissions

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