Solved

Access 2003 append query

Posted on 2013-01-28
16
276 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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

This very simple solution applies to a narrow cross-section of the "needs to close" variety. In this case, the full message in Event Viewer was in applog, Event ID 1000: Faulting application iexplore.exe, version 8.0.6001.18702, faulting module …
The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
Viewers will learn the different options available in the Backstage view in Excel 2013.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

708 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

16 Experts available now in Live!

Get 1:1 Help Now