Solved

Access 2003 append query

Posted on 2013-01-28
16
279 Views
Last Modified: 2013-02-06
I need to use username in a microsoft access 2003 query to determine which table is used as the append table.
0
Comment
Question by:raythomas
  • 7
  • 4
  • 3
16 Comments
 
LVL 18

Expert Comment

by:Jerry Miller
ID: 38827610
I am not exactly sure of your needs. Can't you open the query in design mode or is it in a different database that you cannot access?
0
 

Author Comment

by:raythomas
ID: 38827752
Yes.  I can open the query in design mode.  What I'm trying to do is have the query append data to one of several tables based on the user running the form or query.
0
 
LVL 41

Expert Comment

by:pcelba
ID: 38827763
You'll need more query definitions and some macro or VBA code to determine which query to execute.
0
 

Author Comment

by:raythomas
ID: 38827793
I have several tables already created.  They're identical except the environ(username) precedes the rest of the table name.  ie.  John Doe Table, Jane Doe Table.  I need for the append query to be able to add environ(username) to "Table" and append the data to that table name.  Is there no way to use environ to do this?  I could also do it in the form itself if necessary.  This is a temporary table which data from other tables is written to when the form is opened.  All data is used to update the permanent tables and the temporary table is cleared when the form is closed.  I would really like to set up a temporary (empty) table for each user of this form and have the data appended to that table based on user name.  Is this possible?
0
 

Author Comment

by:raythomas
ID: 38827806
There are several tables, forms and queries involved in this process and I'm trying to avoid having to replicate each of these for each possible user of this particular form.
0
 
LVL 41

Expert Comment

by:pcelba
ID: 38827848
A standard way is to create ONE table with UserName column. And the append query must populate this column with the current user name obtained from environment.

Another way is a VBA function which allows to build and execute the INSERT INTO statement based on parameters passed.
0
 
LVL 18

Expert Comment

by:Jerry Miller
ID: 38828037
I agree with pcelba. I would not use multiple tables with the username appended to the front. One main table with a user name column would be a more standard approach. Plus when you add a new user you don't have to create new tables, queries, etc.
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:raythomas
ID: 38828111
My problem is that when more than one user has this temporary table open and one closes the form, the table gets cleared out.  The second user then does not get his data appended to the permanent table.  Thus the reason for several temporary tables.
0
 
LVL 41

Expert Comment

by:pcelba
ID: 38828187
Does the "table gets cleared out" mean some delete query?

If yes then you have to delete just records belonging to the current user. It means one additional parameter or condition in your delete query.

If not then please tell how do you clear the table out.
0
 

Author Comment

by:raythomas
ID: 38828212
Thanks for your suggestions.  I figured out another way to resolve my problem.  Consider this problem resolved.
0
 

Author Comment

by:raythomas
ID: 38829596
I've requested that this question be deleted for the following reason:

Solved by adopting a different approach altogether.  Thanks to all.
0
 
LVL 41

Expert Comment

by:pcelba
ID: 38828281
Sorry to disturb you again but you could tell more about your different approach. I would like to know whether or not our answers were so bad to force the question deletion...
0
 
LVL 18

Expert Comment

by:Jerry Miller
ID: 38829597
I would also like to hear more about his 'different approach'.
0
 

Accepted Solution

by:
raythomas earned 0 total points
ID: 38830916
I simply did away completely with the temporary tables I was using for the particular operation I was trying to perform.  I changed all queries to select queries instead, revised the forms to use those queries.  It turned out that I didn't really need to use those temporary tables, but could allow the users to directly modify the data in the permanent tables, rather than appending data to a temporary table, then updating the data in the permanent tables, then clearing the temporary tables.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Edit header 19 102
Is there a 32-bit & 64-bit version of Mac Office 2016? 8 64
How to export photos individually from a OneNote 2016 39 110
Using MS Code on my Mac 6 66
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now