By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,397 Members | 1,425 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,397 IT Pros & Developers. It's quick & easy.

Function for creating/copying table?

P: 36
Hi all,

I'm trying to create a new table or copy a new table from a template table
but clicking a button in a form.

Sounds complicated? let me explain more

I will have a form were you can login as a user or create a new user
I want in either case you either create a new table for that new user based on a template table that I already made or restrict the user access to his tables only

any way to do that? I can't find a function in access to create or dublicate tables
Nov 26 '06 #1
Share this Question
Share on Google+
6 Replies

Expert 100+
P: 1,418
You can create a make table query that is run from the respective bouton
the make table query can be like it :

Expand|Select|Wrap|Line Numbers
  1. SELECT "" AS Expr1, "" AS Expr2, "" AS Expr3 INTO test;
This query inserts an empty row in the table that you can delete it with delete query and your table test should be empty!
Nov 26 '06 #2

Expert Mod 15k+
P: 31,661
Please consider carefully whether you want to create a new table for every user.
There certainly are ways of doing it (in SQL as PEB suggests or even using DoCmd.CopyObject()) but it is not an advisable way to go forward in your circumstances.
Consider, instead, adding the new user to a general purpose user table with an identifying key code.

CopyObject Method
The CopyObject method carries out the CopyObject action in Visual Basic.

expression.CopyObject(DestinationDatabase, NewName, SourceObjectType, SourceObjectName)
expression Required. An expression that returns one of the objects in the Applies To list.

DestinationDatabase Optional Variant. A string expression that's the valid path and file name for the database you want to copy the object into. To select the current database, leave this argument blank. Note: In a Microsoft Access project (.adp) you must leave the destinationdatabase argument blank. If you execute Visual Basic code containing the CopyObject method in a library database and leave this argument blank, Microsoft Access copies the object into the library database.

NewName Optional Variant. A string expression that's the new name for the object you want to copy. To use the same name if you are copying into another database, leave this argument blank.

SourceObjectType Optional AcObjectType.

AcObjectType can be one of these AcObjectType constants.
acDefault default
Note When using the CopyObject method with a data access page, a copy of the HTML file for the data access page is created in the Default database folder and a link to it is created in the destination database.

SourceObjectName Optional Variant. A string expression that's the valid name of an object of the type selected by the sourceobjecttype argument. If you run Visual Basic code containing the CopyObject method in a library database, Microsoft Access looks for the object with this name first in the library database, then in the current database.
Nov 26 '06 #3

Expert 100+
P: 1,418
Sure a column like UserID can be sutable to insert the information about all users instaed to create a table for each user...

And one table for all i better more easy way to process the data... Else proceed each table.. very heavy algorithme...

The creation of a new rtable is good when you have a very heavy queries... and to facilitate the query work you need to save the information into temporary table.
Nov 26 '06 #4

P: 36
Thank all for your input.

I'm trying to create table for every user because it will act as a storage area for other information that are specific for each user. Users will be able this up to add more records
Nov 26 '06 #5

Expert 100+
P: 1,418
Yeah man...

I understand.. something like a table for each user...

But you can do also this work adding user_ID and show the information for only the user that put this information Is it possible?

And also u can update his ID automatically without need that he inputs his ID...

So you will have all information from all users at one place... and they will see only introduction from them!
Nov 26 '06 #6

Expert Mod 15k+
P: 31,661
PEB is absolutely right.
Have a look in here - it's taken from the sticky thread in the Access forum.
How to structure your tables

This site was written by Microsoft for Access 2000. However, the rules basically hold true for any database structure and should be read by anyone building a database who doesn't fully understand the importance of normalisation and how to do it.

So many of the problems our posters encounter can be traced back to the incorrect structure of their tables.

As with all these sites you can ask any questions on the forum if there is anything you don't fully understand.
Nov 26 '06 #7

Post your reply

Sign in to post your reply or Sign up for a free account.