Dynamic table name

[SBCUser666]

I have an Access 2003 query that does a SELECT.. INTO table_name.

I would like the table_name to be a fixed value plus todays date.

Example: tbl_EXTRACT_070709

Can this be done?

[ADezii]

@SBCUser666
I'm not sure if I am misreading this Thread, but I'll give it a shot anyway. The following code will dynamically create tbl_EXTRACT_MMDDYY if it does not already exist. It will next create 3 Fields of Type LONG, TEXT, and CURRENCY. Last but not least, it will execute a SQL Statement appending Values into these Fields. Any questions, please feel free to ask.

Dim tdf As DAO.TableDef
Dim MyDB As DAO.Database
Dim fld1 As DAO.Field
Dim fld2 As DAO.Field
Dim fld3 As DAO.Field
Dim strSQL As String
Const conBASE_TABLE_NAME As String = "tbl_EXTRACT_"
 
strSQL = "INSERT INTO " & conBASE_TABLE_NAME & Format$(Date, "mmddyy") & _
         "([LONG INTEGER FIELD], [TEXT FIELD], [CURRENCY FIELD]) VALUES (99999 , 'Some Text', 2345.98);"
 
Set MyDB = CurrentDb
 
For Each tdf In CurrentDb.TableDefs
  If tdf.Name = conBASE_TABLE_NAME & Format$(Date, "mmddyy") Then
    MsgBox conBASE_TABLE_NAME & Format$(Date, "mmddyy") & " already exists!", _
           vbExclamation, "Table Already Exists"
             Exit Sub
  End If
Next
 
'If you get to this point, the Table doesn't exist
Set tdf = MyDB.CreateTableDef(conBASE_TABLE_NAME & Format$(Date, "mmddyy"))
Set fld1 = tdf.CreateField("LONG INTEGER FIELD", dbLong)
    fld1.Required = True
 
Set fld2 = tdf.CreateField
With fld2
  .Name = "TEXT FIELD"
  .Required = True
  .Type = dbText
  .Size = 30
End With
 
Set fld3 = tdf.CreateField("CURRENCY FIELD", dbCurrency)
 
With tdf.Fields
  .Append fld1
  .Append fld2
  .Append fld3
End With
 
MyDB.TableDefs.Append tdf
 
CurrentDb.Execute strSQL, dbFailOnError
 
RefreshDatabaseWindow

[SBCUser666]

I appreciate the answer. Will what you supplied work in Access? If so where do I put it? Or how do I implement it in Access?

[ADezii]

@SBCUser666

Will what you supplied work in Access?

Most definately

If so where do I put it?

Almost anywhere where you can execute code

Or how do I implement it in Access?

I need to know 'exactly' what you are trying to accomplish first, before I answer this question.

[SBCUser666]

Right now I just double left clik on the query in the Queries panel. I assume I would put your code into a Module. But then how do I 'run' the module?

[SBCUser666]

OK, got it.

Create a Form with a Command button

Command button has On Click sub that does Call MCFSQuery

Module MCFSMismatch has a Public Function called MCFSQuery that contains code to build filename, store the full SQL statement in a string then do a DoCmd.RunSQL.

Since the query code is long it took awhile to get the line continuation all set up. And since the filename I wanted had spaces in it I had to figure out where to put the [ and ] around it.

Thanks for your ideas.

[ADezii]

@SBCUser666
You are quite welcome.