Search code examples
vbatimestampms-access-2010unique

ms access vba unique table naming method datestamp

I want to make the created table name unique, possibly by using hh:mm:ss in the table name so that if the macro is played time after time, it won't be telling me "table name already exists".

There are two parts to the query. One for creating the table, and one for refreshing access data objects so that the new table becomes visible.

  Sub SelectIntoX()

    Dim dbs As Database
       Set dbs = CurrentDb

    ' Part 1 Select all records in the scheme table
    ' and copy them into a new table
    dbs.Execute "SELECT * INTO " _
        & Format(Date, "yymmdd") & "_Scheme" & " FROM dbo_scheme;"

         'Part 2 refresh Access data objects to see new table appear
     DBEngine(0)(0).TableDefs.Refresh
     DoCmd.SelectObject acTable, Format(Date, "yymmdd") & "_Scheme", True

End Sub

The problem I have is that yymmdd is not unique and I am running it a lot each day.

I have also tried this hhmmss, but it only adds on zeroes.

enter image description here

Solution

  • This should be a good alternative:

    Format(Now(), "yyyymmddhhmmss")