automatically creating a table within a database

[SirLars]

i have an access database created with front page..
in this database is a simple table (the "main" table) where records are added with an online form...

now... the tough part...

i wish to create a new table within this database for each record in the "main" table...

i want the new table to be created and named for the ID # of the corresponding record in the "main" database...

does anyone know a way to do this with front page?
or simple asp commands?

Lars

 

[Alcar]

in ASP it would be something like this:

assuming you have the number of records contained in the main table of the first DB,

For i = 1 to intNumOfRecords

strSql = "CREATE DATABASE " & rsMainTable.Fields("ID&quot & _
"(firstField varchar(10) NOT NULL, " & _
"secondField varchar(10) NOT NULL, " & _
"thirdField varchar(10) NOT NULL, " & _
"fourthField varchar(10) NOT NULL, " & _
"fithField varchar(10) NOT NULL) ON [default]"

myConnObj.Execute strSql

if not rsMainTable.EOF then
rsMainTable.MoveNext
end if

Next


HTH,
Alcar

 

[Alcar]

make that CREATE TABLE =)

 

[MichaelRed]

On the other hand, creating a TABLE for each record suggests that the db either is or soon will be hoplessly de-normalized. You really need to look at the structure of the problem you are addressing in the terms of relational databases and design an eddicient and normalized design for the application/database.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[SirLars]

firstly.. thank you for your responses...

hmmm

perhaps i should better explain.
what i wish to do is use this database for online tournament games and their results..

so the MAIN database would be the type of tournament and name...
i.e. (fields)
ID, TournamentName, TypeofTournament, Date, Time, TournamentManager, MaxPlayers, KeepScores (boolean)

sooooo
when a record is created here it would be creating a new tournament.. when that tournament is created, i will need a table for players to sign up to.. inotherwords...

when a record is created, i would like to automatically create a new table
i.e. (fields)
name of table= MAINID
playername, wins, losses, pointsearned, timejoined

i'm not quite sure what you mean by "de-normalized"
this seems like the simplest solution...

so.. when someone wants to find a tournament i would simply list out the records in the MAIN table.. then a link on the table ID field would open a form for them to "sign in" their player name to the table associated to that tournament...

if you have a better idea of how to design this database, i'd love to hear it....

thanks again

Lars

 

[Alcar]

I see,

then why not just create those Tables empty??

tblTournament
-------------
lTournament_ID
szTournamentName
szTournamentType
dtDate
dtTime
szTournamentManager
lMaxPlayers
bKeepScore

tblAssignedPlayers
------------------
lAssignment_ID
lTournament_ID
lPlayer_ID
szParamenter1
szParamenter2
szParamenter3

tblPlayers
----------
lPlayer_ID
szPlayersHandle
szPlayersFirstName
szPlayersLastName
szPlayersPassword
dtJoinDate
lWins
lLosses
lPoints


When they create a new tournament jsut put it in the tournament table, then the players that want to join will see the list of tournaments available (included the new one) and once they will pick the one they want, the code will populate the tblAssignedPlayers table.

hth
Alcar