Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Access 2003 append query

Posted on 2013-01-28
16
Medium Priority
?
306 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
14 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 43

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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

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 43

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 43

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 43

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

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…
User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

581 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