在运行时在现有数据库中创建表 [英] Create table in existing database at runtime

问题描述

我想在运行时在现有数据库中创建表，该表将表单文本框的值作为表名.我正在使用ADODB Connection和MS Access2007.

我在创建表语句时遇到错误.

请帮助我成功摆脱此错误.

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

推荐答案

您错过了括号(表名周围的[]也不会受到损害):

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) )")

请查看以下链接:

http://www .c-sharpcorner.com/uploadfile/manishkdwivedi/create-table-at-run-time-in-database-by-using-Asp-Net/ [ http://www.developerfusion.com/code/3039/create-table-in -a-database/ [ ^ ]

http://www.dotnetcurry.com/ShowArticle.aspx?ID=135 [ ^ ]

check out below link:

http://www.c-sharpcorner.com/uploadfile/manishkdwivedi/create-table-at-run-time-in-database-by-using-Asp-Net/[^]

http://www.developerfusion.com/code/3039/create-table-in-a-database/[^]

http://www.dotnetcurry.com/ShowArticle.aspx?ID=135[^]

检查此
http://www.programmersheaven .com/mb/VBNET/351660/351660/creating-table-during-run-time-in-vbnet --- urgent/ [

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) )")

这篇关于在运行时在现有数据库中创建表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！