472,374 Members | 1,336 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,374 software developers and data experts.

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 7625
8,832 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,832 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,832 Expert 8TB
You are quite welcome.
Jul 9 '09 #7

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

Similar topics

by: Pat Patterson | last post by:
I'm having serious issues with a page I'm developing. I just need some simple help, and was hoping someone might be able to help me out in here. I have a form, that consists of 3 pages of...
by: Tim.D | last post by:
People, I've ventured into the wonderful world of Stored Procedures. My first experience has been relatively successful however I am stuck on using host variables to specifiy actualy table or...
by: Nathan Bloomfield | last post by:
Does anyone know if there is any documentation which relates to Access2k + ? or can anyone help adjust the code? I am having trouble converting the DAO references. TITLE :INF: How to...
by: serge | last post by:
How can I run a single SP by asking multiple sales question either by using the logical operator AND for all the questions; or using the logical operator OR for all the questions. So it's always...
by: pukivruki | last post by:
hi, I wish to create a temporary table who's name is dynamic based on the argument. ALTER PROCEDURE . @PID1 VARCHAR(50), @PID2 VARCHAR(50), @TICKET VARCHAR(20)
by: Chris | last post by:
Can't seem to figure out how to do this and have been reading for some time now...... I want to select a row count from a table name in SYSTABLES. This statement does not return what I needed,...
by: Bongard | last post by:
I have a dynamic range that I would like to use as a linked table into Access. The problem is that Access doesn't seem to want to to recognize the dynamic range when you click on "show named...
by: creative1 | last post by:
Here is how you create a complex data report that involves parent and child commands and you can update information at runtime. Its pretty straight forward to work with simple queries; however,...
by: azegurb | last post by:
hi I have just took from internet dinamic table. this table is dynamic and its rows dynamically can be increased. but i would like how create SUM function that automatically sums each added row...
by: yomadhu | last post by:
I created a dynamic form in javascript. Am unable to get those values in to php to display. I need all details. If i add 10 rows the i need to display those all values. Can any one help me for that...
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge required to effectively administer and manage Oracle...
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was proposed, which integrated multiple engines and...
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific technical details, Gmail likely implements measures...
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the synthesis of my design into a bitstream, not the C++...
by: Rahul1995seven | last post by:
Introduction: In the realm of programming languages, Python has emerged as a powerhouse. With its simplicity, versatility, and robustness, Python has gained popularity among beginners and experts...
by: Ricardo de Mila | last post by:
Dear people, good afternoon... I have a form in msAccess with lots of controls and a specific routine must be triggered if the mouse_down event happens in any control. Than I need to discover what...
by: Johno34 | last post by:
I have this click event on my form. It speaks to a Datasheet Subform Private Sub Command260_Click() Dim r As DAO.Recordset Set r = Form_frmABCD.Form.RecordsetClone r.MoveFirst Do If...
by: jack2019x | last post by:
hello, Is there code or static lib for hook swapchain present? I wanna hook dxgi swapchain present for dx11 and dx9.
by: DizelArs | last post by:
Hi all) Faced with a problem, element.click() event doesn't work in Safari browser. Tried various tricks like emulating touch event through a function: let clickEvent = new Event('click', {...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.