Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Create table with date in name (Access 2000)

    I use the code below in a module. It's not the entire code. Just a sample. My users want the created table Centerchanges to have the date in it. Something like 04/05/2004CenterChanges. Is this possible using the date function? Thanks for the help..

    db.TableDefs.Delete "CenterChanges"
    db.Execute ("CREATE TABLE CenterChanges (RecordNumber TEXT(255), FieldName TEXT(255), OldText TEXT(255), NewText TEXT(255));")

    rstBase.MoveFirst
    rstVarying.MoveFirst

    Do Until rstBase.EOF
    If rstVarying.EOF = True Then
    ErrorMessage = "**** record " & rstBase(PrimaryKeyField) & _
    " deleted ****"
    db.Execute ("INSERT INTO CenterChanges (RecordNumber) " _
    & "VALUES ( '" & ErrorMessage & "');")

    For Each fld In tdf.Fields
    db.Execute ("INSERT INTO CenterChanges (RecordNumber, FieldName, OldText)" _
    & " VALUES ( '" & rstBase(PrimaryKeyField) & "','" & fld.Name & "','" & rstBase(fld.Name) & "');")
    FieldChanged = True
    Next fld

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

    Re: Create table with date in name (Access 2000)

    Although it is possible to include slashes / in a table name, I recommend avoiding them (just like spaces and other special characters) because they force you to enclose the table name in square brackets wherever you use it. If you ever forget that, Access gets confused because / is also the division operator. You could use 04052004 or something like that. I prefer using the date in yyyymmdd format, because that makes it easy to sort the name by date.

    Dim strTableName As String
    strTableName = "CenterChanges" & Format(Date, "yyyymmdd")

    db.TableDefs.Delete strTableName
    db.Execute ("CREATE TABLE " & strTableName & " (RecordNumber TEXT(255), FieldName TEXT(255), OldText TEXT(255), NewText TEXT(255));")

    rstBase.MoveFirst
    rstVarying.MoveFirst

    Do Until rstBase.EOF
    If rstVarying.EOF = True Then
    ErrorMessage = "**** record " & rstBase(PrimaryKeyField) & " deleted ****"
    db.Execute ("INSERT INTO " & strTableName & " (RecordNumber) VALUES ( '" & ErrorMessage & "');")

    For Each fld In tdf.Fields
    db.Execute ("INSERT INTO " & strTableName & " (RecordNumber, FieldName, OldText)" _
    & " VALUES ( '" & rstBase(PrimaryKeyField) & "','" & fld.Name & "','" & rstBase(fld.Name) & "');")
    FieldChanged = True
    Next fld

Posting Permissions

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