please,please,please.......Im going insane with this question!

[Smie]

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!!

[LG]

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.

[Smie]

I need it to save settings for each user

[Smie]

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).]

[MartinLiss]

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.

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

[Smie]

THANK YOU SO MUCH!!! IT WORKS!!!