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
Solved

Access 2003 append query

Posted on 2013-01-28
16
285 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 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
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 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 …
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…

860 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