Solved

Access 2003 append query

Posted on 2013-01-28
16
291 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 42

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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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 42

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
 

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 42

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 42

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
Many companies are making the switch from Microsoft to Google Apps (https://www.google.com/work/apps/business/). Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
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 use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

724 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