Help with VB database table creation

[sri20]

Set tblNew_Cycle = db.CreateTableDef(New_Cycle_Name)
 
 
'Adding Fields to the Table
tblNew_Cycle.Fields.Append tblNew_Cycle.CreateField("Test_Cycle", dbText)
tblNew_Cycle.Fields.Append tblNew_Cycle.CreateField("Sno", dbInteger)
tblNew_Cycle.Fields.Append tblNew_Cycle.CreateField("Group_Name", dbText)
tblNew_Cycle.Fields.Append tblNew_Cycle.CreateField("Test_Case", dbText)
tblNew_Cycle.Fields.Append tblNew_Cycle.CreateField("Result", dbText)
tblNew_Cycle.Fields.Append tblNew_Cycle.CreateField("Tester", dbText)
tblNew_Cycle.Fields.Append tblNew_Cycle.CreateField("Date_Tested", dbDate)
tblNew_Cycle.Fields.Append tblNew_Cycle.CreateField("CR", dbText)
tblNew_Cycle.Fields.Append tblNew_Cycle.CreateField("CL", dbText)
tblNew_Cycle.Fields.Append tblNew_Cycle.CreateField("Star_Team_Ref", dbText)
tblNew_Cycle.Fields.Append tblNew_Cycle.CreateField("Dummy_Notes", dbText)
tblNew_Cycle.Fields.Append tblNew_Cycle.CreateField("Total_Steps", dbText)
tblNew_Cycle.Fields.Append tblNew_Cycle.CreateField("Failed_Step", dbText)
tblNew_Cycle.Fields.Append tblNew_Cycle.CreateField("Original_Tester", dbText)
tblNew_Cycle.Fields.Append tblNew_Cycle.CreateField("Review_Comments", dbText)
tblNew_Cycle.Fields.Append tblNew_Cycle.CreateField("Tester_Comments", dbText)
tblNew_Cycle.Fields.Append tblNew_Cycle.CreateField("Action_Taken", dbText)
tblNew_Cycle.Fields.Append tblNew_Cycle.CreateField("Bug_Status", dbText)
tblNew_Cycle.Fields.Append tblNew_Cycle.CreateField("Duplicate", dbText)
tblNew_Cycle.Fields.Append tblNew_Cycle.CreateField("Step_Needing_Clarification", dbText)
 

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,Test_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_Cycle" 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

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

[QVeen72]

Hi,

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

Check this :

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

Regards
Veena