By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,490 Members | 1,796 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,490 IT Pros & Developers. It's quick & easy.

Dynamic table name

SBCUser666
P: 7
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
Share this Question
Share on Google+
6 Replies


ADezii
Expert 5K+
P: 8,679
@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.
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_"
  8.  
  9. strSQL = "INSERT INTO " & conBASE_TABLE_NAME & Format$(Date, "mmddyy") & _
  10.          "([LONG INTEGER FIELD], [TEXT FIELD], [CURRENCY FIELD]) VALUES (99999 , 'Some Text', 2345.98);"
  11.  
  12. Set MyDB = CurrentDb
  13.  
  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
  21.  
  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
  26.  
  27. Set fld2 = tdf.CreateField
  28. With fld2
  29.   .Name = "TEXT FIELD"
  30.   .Required = True
  31.   .Type = dbText
  32.   .Size = 30
  33. End With
  34.  
  35. Set fld3 = tdf.CreateField("CURRENCY FIELD", dbCurrency)
  36.  
  37. With tdf.Fields
  38.   .Append fld1
  39.   .Append fld2
  40.   .Append fld3
  41. End With
  42.  
  43. MyDB.TableDefs.Append tdf
  44.  
  45. CurrentDb.Execute strSQL, dbFailOnError
  46.  
  47. RefreshDatabaseWindow
Jul 7 '09 #2

SBCUser666
P: 7
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

ADezii
Expert 5K+
P: 8,679
@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.
Jul 8 '09 #4

SBCUser666
P: 7
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

SBCUser666
P: 7
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

ADezii
Expert 5K+
P: 8,679
@SBCUser666
You are quite welcome.
Jul 9 '09 #7

Post your reply

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