469,887 Members | 1,222 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,887 developers. It's quick & easy.

Dynamic table name

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?
Jul 7 '09 #1
6 7239
8,800 Expert 8TB
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.
Expand|Select|Wrap|Line Numbers
  1. Dim tdf As DAO.TableDef
  2. Dim MyDB As DAO.Database
  3. Dim fld1 As DAO.Field
  4. Dim fld2 As DAO.Field
  5. Dim fld3 As DAO.Field
  6. Dim strSQL As String
  7. Const conBASE_TABLE_NAME As String = "tbl_EXTRACT_"
  9. strSQL = "INSERT INTO " & conBASE_TABLE_NAME & Format$(Date, "mmddyy") & _
  10.          "([LONG INTEGER FIELD], [TEXT FIELD], [CURRENCY FIELD]) VALUES (99999 , 'Some Text', 2345.98);"
  12. Set MyDB = CurrentDb
  14. For Each tdf In CurrentDb.TableDefs
  15.   If tdf.Name = conBASE_TABLE_NAME & Format$(Date, "mmddyy") Then
  16.     MsgBox conBASE_TABLE_NAME & Format$(Date, "mmddyy") & " already exists!", _
  17.            vbExclamation, "Table Already Exists"
  18.              Exit Sub
  19.   End If
  20. Next
  22. 'If you get to this point, the Table doesn't exist
  23. Set tdf = MyDB.CreateTableDef(conBASE_TABLE_NAME & Format$(Date, "mmddyy"))
  24. Set fld1 = tdf.CreateField("LONG INTEGER FIELD", dbLong)
  25.     fld1.Required = True
  27. Set fld2 = tdf.CreateField
  28. With fld2
  29.   .Name = "TEXT FIELD"
  30.   .Required = True
  31.   .Type = dbText
  32.   .Size = 30
  33. End With
  35. Set fld3 = tdf.CreateField("CURRENCY FIELD", dbCurrency)
  37. With tdf.Fields
  38.   .Append fld1
  39.   .Append fld2
  40.   .Append fld3
  41. End With
  43. MyDB.TableDefs.Append tdf
  45. CurrentDb.Execute strSQL, dbFailOnError
  47. RefreshDatabaseWindow
Jul 7 '09 #2
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?
Jul 8 '09 #3
8,800 Expert 8TB
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.
Jul 8 '09 #4
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?
Jul 8 '09 #5
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.
Jul 8 '09 #6
8,800 Expert 8TB
You are quite welcome.
Jul 9 '09 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Nathan Bloomfield | last post: by
1 post views Thread by Waqarahmed | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.