Create table in existing database at runtime

See more:

VB

I want to create table in the existing database at runtime that takes the value of the text box of my form as a table name.I am using ADODB Connection and MS Access 2007.

I am getting an error in create table statement.

Please help me to get out of this error successfully.

Private Sub cmd_update_Click()

Dim t As Integer
t = MsgBox("Are you sure you want to save purchase Bill", vbQuestion Or vbYesNo, "Want to save Purchase bill")
If t = 7 Then
    Exit Sub
End If

Dim TabName As String
    
    TabName = txt_invoiceno.Text
    
    db.Execute ("CREATE TABLE  [" & TabName & "] (" _
                & "Invoice_No Text(20)," _
                & " Supplier_Name Text(20)," _
                & " Purchase_Date Date," _
                & " Delivery_Date Date," _
                & " Material_Type Text(20)," _
                & " Material_Name Text(20)," _
                & "Order_Qty Number(DOUBLE)," _
                & "Price_Per_Unit Number(DOUBLE)," _
                & "Total_Amount Number(DOUBLE)," _
                & "Received_Qty Number(DOUBLE)," _
                & "Qty_Instock Number(DOUBLE) )")
                
    
                
Dim rs_Available_Purchased_Material As New ADODB.Recordset

rs_Available_Purchased_Material.Open "SELECT * FROM Available_Purchased_Material", db, adOpenDynamic, adLockOptimistic

    rs_cur_invoice_mat.Requery
    rs_cur_record_count.Requery
    
    If rs_cur_record_count.Fields(0).Value > 0 Then
    
   ' If Len(txt_invoiceno.Text) > 0 And Len(cmb_suppliername.Text) > 0 Then
   ' TOTAL_TRAN_AMT = TOTAL_AMT("PURCHASE")
    
    While rs_cur_invoice_mat.EOF <> True
                 
        rs_purchase.AddNew
        rs_purchase.Fields(0).Value = txt_invoiceno.Text
        rs_purchase.Fields(1).Value = cmb_suppliername.Text
        rs_purchase.Fields(2).Value = DT_purchase.Value
        rs_purchase.Fields(3).Value = DT_delivery.Value
        rs_purchase.Fields(4).Value = rs_cur_invoice_mat.Fields(0).Value
        rs_purchase.Fields(5).Value = rs_cur_invoice_mat.Fields(1).Value
        rs_purchase.Fields(6).Value = rs_cur_invoice_mat.Fields(2).Value
        rs_purchase.Fields(7).Value = rs_cur_invoice_mat.Fields(3).Value
        
        If Len(rs_cur_invoice_mat.Fields(4).Value) > 0 Then
        
                rs_purchase.Fields(8).Value = rs_cur_invoice_mat.Fields(4).Value
        
        End If
        
        rs_purchase.Fields(9).Value = rs_purchase.Fields(9).Value
        rs_purchase.Fields(10).Value = rs_cur_invoice_mat.Fields(2).Value
       
        
        On Error GoTo OH_ER
        rs_purchase.Update
        GoTo A1:
OH_ER:
        MsgBox "Duplicate Entry Found ...", vbCritical, "Duplicate Entry Found..."
        rs_purchase.CancelUpdate
        Exit Sub
A1:
        rs_Available_Purchased_Material.AddNew
        rs_Available_Purchased_Material.Fields(0).Value = txt_invoiceno.Text
        rs_Available_Purchased_Material.Fields(1).Value = cmb_suppliername.Text
        rs_Available_Purchased_Material.Fields(2).Value = DT_purchase.Value
        rs_Available_Purchased_Material.Fields(3).Value = DT_delivery.Value
        rs_Available_Purchased_Material.Fields(4).Value = rs_cur_invoice_mat.Fields(0).Value
        rs_Available_Purchased_Material.Fields(5).Value = rs_cur_invoice_mat.Fields(1).Value
        rs_Available_Purchased_Material.Fields(6).Value = rs_cur_invoice_mat.Fields(2).Value
        rs_Available_Purchased_Material.Fields(7).Value = rs_cur_invoice_mat.Fields(3).Value
        rs_Available_Purchased_Material.Fields(8).Value = rs_cur_invoice_mat.Fields(4).Value
        
        rs_Available_Purchased_Material.Fields(10).Value = rs_cur_invoice_mat.Fields(2).Value
        
        rs_Available_Purchased_Material.Update
    
        rs_material.Close
        rs_material.Open "select * from [Material_Master] where Material_Type='" & rs_cur_invoice_mat.Fields(0).Value & "' and Material_Name='" & rs_cur_invoice_mat.Fields(1).Value & "'", db, adOpenDynamic, adLockOptimistic
        rs_material.Update
        
        rs_cur_invoice_mat.MoveNext
        
    Wend
    
    rs_cur_invoice_mat.MoveFirst
    
    While rs_cur_invoice_mat.EOF <> True
    
        rs_cur_invoice_mat.Delete
        rs_cur_invoice_mat.MoveNext
    Wend
    
    PADD = False
    
    frm_material_received_or_not.lbl_fill_invoiceno.Caption = txt_invoiceno.Text
    frm_material_received_or_not.lbl_fill_suppliername.Caption = cmb_suppliername.Text
    frm_material_received_or_not.dt = Format(DT_purchase.Value, "dd-MMM-yyyy")
    frm_material_received_or_not.lbl_fill_outof.Caption = txt_order_qty.Text
    frm_material_received_or_not.lbl_fill_mat_name.Caption = cmb_mat_name.Text
    frm_material_received_or_not.lbl_fill_mat_type.Caption = cmb_mat_type.Text
    
    Unload Me
    
        'Dim f As New FileSystemObject
        'f.CopyFile App.Path & "\Master_Database.mdb", App.Path & "\data\" & cur_company_name & "\Master_Database.mdb", True
       
    frm_material_received_or_not.Show vbModal
    
    Else
        
        MsgBox "Enter Proper Data" & vbCrLf & "Some Important Data Are Missing", vbCritical, "Enter Proper Data ..."
    
    End If
    
   ' Else
   '     MsgBox "There is no item in this Purchase bill , You can not save it ...", vbInformation, "No item Found.."
    
' End If
    rs_Available_Purchased_Material.Close

End Sub