Click here to Skip to main content
14,362,565 members
Rate this:
Please Sign up or sign in to vote.
See more:
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
Posted
Updated 23-Sep-12 23:12pm
v4
Rate this:
Please Sign up or sign in to vote.

Solution 1

You missed the parenthesis (also [] around table names wouldn't hurt):
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) )")
   
Comments
shivam20292 24-Sep-12 3:40am
   
Thanx Mehdi for your solution.
But still I am having an error in create table syntax.

I am using ADODB for the database creation. Also I want to create table in existing database.
Santhosh Kumar Jayaraman 24-Sep-12 3:47am
   
what was the error? which line?
shivam20292 24-Sep-12 4:17am
   
Runtime error in Create table syntax.

Please have a look at my whole form code in question.
Santhosh Kumar Jayaraman 24-Sep-12 4:32am
   
try my solution below
Rate this:
Please Sign up or sign in to vote.

Solution 3

Check this
http://www.programmersheaven.com/mb/VBNET/351660/351660/creating-table-during-run-time-in-vbnet---urgent/[^]

try this
db.Execute ("CREATE TABLE  [" & TabName & "] (" _
                & "Invoice_No VarChar(20)," _
                & " Supplier_Name VarChar(20)," _
                & " Purchase_Date DateTime," _
                & " Delivery_Date DateTime," _
                & " Material_Type VarChar(20)," _
                & " Material_Name VarChar(20)," _
                & "Order_Qty Number(DOUBLE)," _
                & "Price_Per_Unit Number(DOUBLE)," _
                & "Total_Amount Number(DOUBLE)," _
                & "Received_Qty Number(DOUBLE)," _
                & "Qty_Instock Number(DOUBLE) )")
   
Comments
shivam20292 24-Sep-12 5:02am
   
Hey Santosh,

I have tried this one too but still the error continues.

Have you looked my whole code?
I want to create in existing database on click event of update button..
Santhosh Kumar Jayaraman 24-Sep-12 5:03am
   
WHen you get error in create table syntax, then we no need to look into whole code.. You have to give us exact error message code. It says incorrect table syntax, but it will say near something or line something..Give us that.
shivam20292 24-Sep-12 5:26am
   
When I fill my form & click on update button, it gives runtime error '-2147217900 (80040e14)'
Also when i debug it, the whole create statement syntax is displayed highlighted.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100