-
Say I have a database loaded. The database has one column(table). I have my textbox(name.txt) and a command button. After selecting the name in the textbox, and press the command button, another form is loaded and the name that was selected in the textbox, is now a variable. This new form is the user data. Now my question is how in the world, can I get vb to CREATE. another table within my database, that is named after the user(name is now set as variable)? I have spent 3 days straight looking for help on this, If I cant get it here, I will have to scrap the project....so i beg you please help!!
-
Why in the world do you want to create a table for each user? This is not DB is created for, as soon as you using DB.
-
I need it to save settings for each user
-
Would It have anything to do with
data1.RecordSource New ?
-
If you're using DAO, there's a method called CreateTableDef. Look up the help files on that, it will create a table like you want HOWEVER, I agree with LG that that's a bad bad bad database design.
Here's a suggestion: when you add user, give the user some unique ID. Now have one table that has the following columns:
Column1: UserID
Column2: UserPropertyName
Column3: UserPropertyValue
...now let's say a user is added. Their user id is '123XYZ'. You want to save their email which might be '[email protected]' and you want to save their user name which happens to be 'someusername'. All you have to do now is add one row to the table for the email and one row for the username. The following(hopefully it looks ok, i used commas to show the separate columns) is what the table layout might look like:
TableName=UserPropertyTable
UserID, UserPropertyName, UserPropertyValue
'123XYZ','EMAIL','[email protected]'
'123XYZ','USERNAME','someusername'
..now if you want to look up a user's properties you could use
SELECT * FROM UserPropertyTable WHERE UserID = '123XYZ'
this is very simplified, but does eliminate the need for creating a separate table for every user.
[This message has been edited by pvb (edited 01-08-2000).]
-
Here is a routine that you can use to create a table named anything you want. It's purpose is to add a field to a table that already exists, but if you call it with a table name that doesn't exist, it will create the table. So to create a table with 10 fields you would need to the routine 10 times. If that's not acceptable, then you can modify it to add all the fields at once.
Code:
Public Sub AddField(sTableName As String, sFieldName As String, nFieldType As Integer, nFieldSize As Integer, bRequired As Boolean, bAllowZeroLength As Boolean)
'***************************************************************************
'Purpose: Add a new field to a table
'Inputs: sTableName - The target table name. If it doesn't exist it
' will be created
' sFieldName - The name of the new field
' nFieldType - The number representing the type of the new field
' 1 = Yes/No 7 = Double
' 2 = Byte 8 = Date/Time
' 3 = Integer 9 = (not valid)
' 4 = Long 10 = Text
' 5 = Currency 11 = Long Binary (OLE Object)
' 6 = Single 12 = Memo
' nFieldSize - The size of the new field (ignored if not appropriate
' to the field type)
' bRequired - Require field if True
' bAllowZeroLength - Allow field to be zero length if True
'Outputs: None
'***************************************************************************
Dim tdTableDef As TableDef
Dim fldFieldToAdd As Field
Dim bFound As Boolean
Dim nCtr As Integer
On Error GoTo ErrorRoutine
'Search to see if table exists
For nCtr = 0 To gdbTargetDB.TableDefs.Count - 1
If UCase(gdbTargetDB.TableDefs(nCtr).Name) = UCase(sTableName) Then
bFound = True
Exit For
End If
Next
If bFound Then
Set tdTableDef = gdbTargetDB.TableDefs(sTableName)
Set fldFieldToAdd = tdTableDef.CreateField(sFieldName, nFieldType, nFieldSize)
If nFieldType = 10 Then fldFieldToAdd.AllowZeroLength = bAllowZeroLength
fldFieldToAdd.Required = bRequired
gdbTargetDB.TableDefs(sTableName).Fields.Append fldFieldToAdd
Else
'Table doesn't exist
Set tdTableDef = gdbTargetDB.CreateTableDef(sTableName)
Set fldFieldToAdd = tdTableDef.CreateField(sFieldName, nFieldType, nFieldSize)
If nFieldType = 10 Then fldFieldToAdd.AllowZeroLength = bAllowZeroLength
fldFieldToAdd.Required = bRequired
tdTableDef.Fields.Append fldFieldToAdd
gdbTargetDB.TableDefs.Append tdTableDef
End If
ErrorRoutine:
If Err.Number <> 0 Then
' Your error processing
End If
End Sub
------------------
Marty
-
THANK YOU SO MUCH!!! IT WORKS!!!
