Help with VB database table creation

[sri20]

[code=vb]
Set tblNew_Cycle = db.CreateTableD ef(New_Cycle_Na me)


'Adding Fields to the Table
tblNew_Cycle.Fi elds.Append tblNew_Cycle.Cr eateField("Test _Cycle", dbText)
tblNew_Cycle.Fi elds.Append tblNew_Cycle.Cr eateField("Sno" , dbInteger)
tblNew_Cycle.Fi elds.Append tblNew_Cycle.Cr eateField("Grou p_Name", dbText)
tblNew_Cycle.Fi elds.Append tblNew_Cycle.Cr eateField("Test _Case", dbText)
tblNew_Cycle.Fi elds.Append tblNew_Cycle.Cr eateField("Resu lt", dbText)
tblNew_Cycle.Fi elds.Append tblNew_Cycle.Cr eateField("Test er", dbText)
tblNew_Cycle.Fi elds.Append tblNew_Cycle.Cr eateField("Date _Tested", dbDate)
tblNew_Cycle.Fi elds.Append tblNew_Cycle.Cr eateField("CR", dbText)
tblNew_Cycle.Fi elds.Append tblNew_Cycle.Cr eateField("CL", dbText)
tblNew_Cycle.Fi elds.Append tblNew_Cycle.Cr eateField("Star _Team_Ref", dbText)
tblNew_Cycle.Fi elds.Append tblNew_Cycle.Cr eateField("Dumm y_Notes", dbText)
tblNew_Cycle.Fi elds.Append tblNew_Cycle.Cr eateField("Tota l_Steps", dbText)
tblNew_Cycle.Fi elds.Append tblNew_Cycle.Cr eateField("Fail ed_Step", dbText)
tblNew_Cycle.Fi elds.Append tblNew_Cycle.Cr eateField("Orig inal_Tester", dbText)
tblNew_Cycle.Fi elds.Append tblNew_Cycle.Cr eateField("Revi ew_Comments", dbText)
tblNew_Cycle.Fi elds.Append tblNew_Cycle.Cr eateField("Test er_Comments", dbText)
tblNew_Cycle.Fi elds.Append tblNew_Cycle.Cr eateField("Acti on_Taken", dbText)
tblNew_Cycle.Fi elds.Append tblNew_Cycle.Cr eateField("Bug_ Status", dbText)
tblNew_Cycle.Fi elds.Append tblNew_Cycle.Cr eateField("Dupl icate", dbText)
tblNew_Cycle.Fi elds.Append tblNew_Cycle.Cr eateField("Step _Needing_Clarif ication", dbText)
[/code]

I created a database table in the above manner. Here New_Cycle_Name is a variable which contains the actual name which the table shld be given.
This code is placed in a loop so every time a new table shld be created with a new name.

Now my problem is
1. How do i insert values into the table using SQL Insert Into command??
I tried it in the following way:


db.Execute ("Insert into tbl_New_Cycle (Test_Cycle,Sno ,Group_Name,Tes t_Case,Tester) values('" & New_Cycle_Name & "','" & no & "', """ & Group_Name & """, """ & Test_Case & """,'" & Tester & "')")

But while running the program i got an error saying that output table "tbl_New_Cy cle" not found, This is because the table that is created in the database has a different name ex TC99..

2.Similarly I am not able to query the table for any kind of results because of the table name being different.



Any help will be appreciated. I searched the net a ot for some solution but couldn`t get any.
Thanks

[debasisdas]

tbl_New_Cycle is not the table name that is a variable , so how can you hardcode that one.

try this
[code=vb]
db.Execute ("Insert into" & tbl_New_Cycle & "(Test_Cycle,Sn o,Group_Name,Te st_Case,Tester) values ('" & New_Cycle_Name & "','" & no & "', """ & Group_Name & """, """ & Test_Case & """,'" & Tester & "')")
[/code]

[QVeen72]

Hi,

One more correction over here : Field "SNo" is an Integer, so you should not wrap it in single Quotes..:

Check this :
[code=vb]
db.Execute ("Insert into" & tbl_New_Cycle & "(Test_Cycle,Sn o,Group_Name,Te st_Case,Tester) values ('" & New_Cycle_Name & "'," & no & ", '" & Group_Name & '", '" & Test_Case & "','" & Tester & "')")
[/code]

Regards
Veena