Code to copy .mdb table?

[rudo]

Hi there,

I made a table through Access. It contains data about employee. I am looking for code to copy and paste the structure of that table to a new table for each new employee. I cannot find it. Has anyone a solution for this problem?

Rudo

 

[dhulbert]

Rudo

Whay do you wan tot copy the table for each employee the idea is all of the employees go in to the same table

 

[alvechurchdata]

Why do you need a new table for each employee? Information about different employees should be stored in records of the employee table.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[rudo]

Thank you Dhulbert and Geoff Franklin,

Those tables do not have any relationships in between them. The number of records can vary a lot from one employee to another. That is one of the reasons to keep them separate.

In Access it is possible to copy and paste tables, with option "only structure". Would you happen to know code to get the same result?

I am not very experienced in Access or Access-code. So if you can help me further, that would be nice.

Rudo

 

[cliffhanger9]

rudo - as you say it is possible. I dont know how to do it in access VBA but I think what everyone here is suggesting is that you consider redesigning your database. Do a serch for database normalization to learn more. Basically from what you have described it sounds like a poor database structure - inefficient.

If it were my database, I would keep the one main table that you have but instead of copying the structure only, add another field where you can store a foreign key - something like an employee ID or username. Then make another table and put al lteh employees names and IDs in this table. Proceed to link the ID field in the new table to the Employee ID field of the original table.

This will benefit you in several ways. The main one being that you eliminate any and all redundancies or duplicates. If you were to go ahead and do it the way you originally wanted to do it with a table per each employee, what would you do when you modified the table structure? you would have to go in and edit every single table whose structure you copied. Very inefficient. not to mention a pain in the arse

This is the strongly recommended path to take. Best of luck..

 

[rudo]

Thanks Cliffhanger9,

Normally you would be right. I know the structure is poor. It might even look inefficient. On the other hand, besides my question about how to copy tables, the management remains very simple. This simple solution keeps data that are treated and maneged separately in simple individual two-dimensional tables. If I would use the classical solution of one table and adding fields all the time, I would end up with 90 extra fields per employee each month (or a sophisticated table with many dimensions). As the number of records needed varies a lot from one employee to another (and depends on the number of separate activities per employee/per day) a lot of space would remain empty.

This said, if there is no code for copy and paste, I will find a solution with following code:

Dim tdfTempl as TableDef
Dim tdfNew as Tabledef

Set oMyBD = Workspaces(0).OpenDatabase("DBname.mdb")

Set tdfTempl=oMyDB.TableDefs("TemplateTablename")
Set tdfNew =oMyDB.CreateTableDef("NewTable")

With tdfNew
For i = 0 to tdfTempl.Fields.Count-1
fldname = tdfTempl.Fields(i).Name
fldtype = tdfTempl.Fields(i).Type
fldsize = tdfTempl.Fields(i).Size
.Fields.Append .CreateField _
(fldname,fldtype,fldsize)
Next i
End with

oMyDB.TableDefs.Append tdfNew

I still have to include the right properties per field and am still looking for this code.

Thanks again,

Rudo