Function for creating/copying table?

[omozali]

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

[PEB]

Hi
You can create a make table query that is run from the respective bouton
the make table query can be like it :

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!

[NeoPa]

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.
acDataAccessPage
acDefault default
acDiagram
acForm
acFunction
acMacro
acModule
acQuery
acReport
acServerView
acStoredProcedure
acTable
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.

[PEB]

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.

[omozali]

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

[PEB]

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!

[NeoPa]

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.

http://support.microsoft.com/kb/209534/