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_070 709
Can this be done?
6 7865 ADezii 8,834
Recognized Expert Expert @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_MMD DYY 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
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 8,834
Recognized Expert Expert @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.
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?
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.
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 fields. I'd like to create
a page in which all of this is stored as you move along as hidden
variables, until the end, when the user submits. I can't figure out
one thing: I have dynamic form elements (dropdowns), that I'd like to
use instead 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 column names in a FROM
clause. After many hours or reading all manner of manuals I've
discovered it appears this is not possible and that in order to so I
need to further venture into dynamic SQL.
My present procedure is based on all static SQL...
|
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 Create a Dynamic Crosstab Report
PRODUCT :Microsoft Access
PROD/VER:1.00 1.10
OPER/SYS:WINDOWS
|
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
either AND or OR but never mixed together.
We can use Northwind database for my question, it is very similar
to the structure of the problem on the database I am working on.
IF(SELECT OBJECT_ID('REPORT')) IS NOT NULL
DROP TABLE REPORT_SELECTION
|
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, but can help explain what
I'm looking for. I want the results to be the Row Count from a Table
Name out of the Sub-Select.
Select Count(*) from
|
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 ranges" in
the Link Spreadsheet wizard. The reason I want to use a dynamic range
is because when the data in my excel spreadsheet changes the linked
table will import blank rows that were previously used from the bottom
of the spreadsheet. I can't...
|
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, working with complex reports is tricky
Assumption: Reader of this article have basic knowledge of creating data reports.
Creating a Parent-Child Command and create a DataReport
Suppose we have a database called company with two tables
...
|
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 value (text value)
here is the code
if possible please help me
Thanks beforehand
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
|
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 code.
<HTML>
<HEAD>
<TITLE> Add/Remove dynamic rows in HTML table </TITLE>
<script type="text/javascript" src="script.js">
// JavaScript Document
var c=0;
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
| |
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |