Addding Fields into an already created Access table

Ok i am having another problem. As many of you know I am a teacher and I am creating a program that will allow me to make virtual tests. I have a table in Access called Student that will hold the records of the test that are taken. This table will hold the login name, fullname, checkboxes for the test so I can tell who took what test, and a fields that will hold the percent scored on each test. Right now I only have the login field and fullname fields in Access. I want the program to add two fields to the Student table each time I create a test. I don't care if the fields names are the same or different as long as one is a checkbox and the other is a number. This my code


Dim OpenTable As Recordset
Public TableName As String
Dim Table As Recordset

Private Sub cmdOk_Click()
Set DataB = OpenDatabase("Z:\TestPro\Login.mdb")
Dim tdef As TableDef
Dim tdef2 As TableDef

' Create a new TableDef object.
Set tdef = DataB.CreateTableDef(txtTestName.Text)
TableName = txtTestName.Text


tdef.Fields.Append tdef.CreateField("Question#", dbMemo, 2)
tdef.Fields.Append tdef.CreateField("Question", dbMemo, 75)
tdef.Fields.Append tdef.CreateField("AAnswer", dbMemo, 25)
tdef.Fields.Append tdef.CreateField("BAnswer", dbMemo, 25)
tdef.Fields.Append tdef.CreateField("CAnswer", dbMemo, 25)
tdef.Fields.Append tdef.CreateField("DAnswer", dbMemo, 25)
tdef.Fields.Append tdef.CreateField("CorrectAns", dbMemo, 1)
tdef.Fields.Append tdef.CreateField("PointValue", dbLong, 2)
DataB.TableDefs.Append tdef

Set tdef2 = DataB.CreateTableDef("Student")

tdef2.Fields.Append tdef2.CreateField("TableName", dbBoolean)
tdef2.Fields.Append tdef2.CreateField("TableName", dbLong, 2)


Set Test = DataB.OpenRecordset("TestNames")

Test.AddNew

Test.Fields("TestName").Value = TableName
MsgBox "Ok"

Test.Update
frmQuestion2.Show


As you can probably tell, I have a lot going on in this form. I am having the test table created and I am adding the testname to the testnames table. I am getting no errors with this code but it isn't writing to the Student table. I want it to be though. Can someone please help with how to add two fields in that table. I am using VB 6.0 with the DAO code and I am connecting it to an Access 2002. Thanks ahead of time.

Ok I changed my code but I don't know what is wrong it gives me an Invalid Operation error on the line that set the allowzerolength attribute

Dim OpenTable As Recordset
Public TableName As String
Dim Table As Recordset
Dim NewField As Field
Dim NewField2 As Field

Private Sub cmdOk_Click()
Set DataB = OpenDatabase("Z:\TestPro\Login.mdb")
Dim tdef As TableDef
Dim tdef2 As TableDef

' Create a new TableDef object.
Set tdef = DataB.CreateTableDef(txtTestName.Text)
TableName = txtTestName.Text


tdef.Fields.Append tdef.CreateField("Question#", dbMemo, 2)
tdef.Fields.Append tdef.CreateField("Question", dbMemo, 75)
tdef.Fields.Append tdef.CreateField("AAnswer", dbMemo, 25)
tdef.Fields.Append tdef.CreateField("BAnswer", dbMemo, 25)
tdef.Fields.Append tdef.CreateField("CAnswer", dbMemo, 25)
tdef.Fields.Append tdef.CreateField("DAnswer", dbMemo, 25)
tdef.Fields.Append tdef.CreateField("CorrectAns", dbMemo, 1)
tdef.Fields.Append tdef.CreateField("PointValue", dbLong, 2)
DataB.TableDefs.Append tdef

Set NewField = DataB.TableDefs("Student").CreateField(TableName)
NewField.Type = dbBoolean
NewField.Size = 1
NewField.DefaultValue = "No"
NewField.AllowZeroLength = False

DataB.TableDefs("Student").Fields.Append NewField

Set NewField = DataB.TableDefs("Student").CreateField("'TableName' Percent")
NewField.Attributes = dbMemo
NewField.Size = 50
NewField.DefaultValue = " "

DataB.TableDefs("Student").Fields.Append NewField


Set Test = DataB.OpenRecordset("TestNames")

Test.AddNew

Test.Fields("TestName").Value = TableName
MsgBox "Ok"

Test.Update
frmQuestion2.Show
End Sub

Your best bet is to use ADOX to modify the structure of an already existing table:

http://www.xtremevbtalk.com/showthread.php?t=183689&highlight=adox

Link fixed

The link doesn't work and why can't I use DAO?

OK I am getting a syntax error on the line the is trying to alter the table. This is the code and I hope you can help:

Dim OpenTable As Recordset
Public TableName As String
Dim Table As Recordset
Dim NewField As Field
Dim NewField2 As Field

Private Sub cmdOk_Click()
Set DataB = OpenDatabase("Z:\TestPro\Login.mdb")
Dim tdef As TableDef
Dim tdef2 As TableDef

' Create a new TableDef object.
Set tdef = DataB.CreateTableDef(txtTestName.Text)
TableName = txtTestName.Text


tdef.Fields.Append tdef.CreateField("Question#", dbMemo, 2)
tdef.Fields.Append tdef.CreateField("Question", dbMemo, 75)
tdef.Fields.Append tdef.CreateField("AAnswer", dbMemo, 25)
tdef.Fields.Append tdef.CreateField("BAnswer", dbMemo, 25)
tdef.Fields.Append tdef.CreateField("CAnswer", dbMemo, 25)
tdef.Fields.Append tdef.CreateField("DAnswer", dbMemo, 25)
tdef.Fields.Append tdef.CreateField("CorrectAns", dbMemo, 1)
tdef.Fields.Append tdef.CreateField("PointValue", dbLong, 2)
DataB.TableDefs.Append tdef

ALTER TABLE Student ADD COLUMN TableName YesNo



Set Test = DataB.OpenRecordset("TestNames")

Test.AddNew

Test.Fields("TestName").Value = TableName
MsgBox "Ok"

Test.Update
frmQuestion2.Show
End Sub

By the way I tried this too:

ALTER TABLE Student ADD COLUMN bolField BIT


But it didn't work either. Is it because of a setup problem or because I am using DAO?

That statement alone in VB isn't anything. It is SQL that needs to be passed to the database, usually by a Connection object. I don't use DAO as it is an obsolete technology, but is there some kind of DataB.Connection.Execute object? If so, you can try DataB.Connection.Execute "ALTER TABLE Student ADD COLUMN TableName YesNo"