insert into Statement...Whats wrong?

[danielgraham]

create table MoP(MoP long, Description text)
insert into MoP(MoP, Description) Values (0,Cash)
insert into MoP(MoP, Description) Values (1,Cheque)
insert into MoP(MoP, Description) Values (2,Credit Card)
insert into MoP(MoP, Description) Values (3,XMAS Club)
insert into MoP(MoP, Description) Values (4,Cash)

Comes up with

Syntax error in create table statement


Please help

 

[Mile-O]

Although the SQL you have posted sort is used to append records to a table, it looks as if you are actually wanting to update a table. Is this the case?

 

[danielgraham]

What i want to do is first create the table then insert this data into it

insert into MoP(MoP, Description) Values (0,Cash)
insert into MoP(MoP, Description) Values (1,Cheque)
insert into MoP(MoP, Description) Values (2,Credit Card)
insert into MoP(MoP, Description) Values (3,XMAS Club)
insert into MoP(MoP, Description) Values (4,Cash)

But it comes up with the same error each time.


Thanks
Dan

 

[danielgraham]

Do i have to tell it thats its end a new statement each line. if so How.

Im doing this in a query in sql view. itll create the table and the contents but only if i pass one line at a time...Not bulk

Thanks
Dan

 

[Mile-O]

This function can be used to create your table and will let you do something else should an error occur. To call it:

If MakeTable = True Then
    ' table is created
Else
    ' an error has occurred; do contingency
End If

Public Function MakeTable() As Boolean

    On Error GoTo Err_MakeTable
    
    Dim strSQL As String
    
    strSQL = "CREATE TABLE MOP([Description] Text, " & _
        "CONSTRAINT [Index1] PRIMARY KEY ([Description]));"
    DoCmd.RunSQL strSQL
    
    MakeTable = True
    
Exit_MakeTable:
    strSQL = vbNullString
    Exit Function
    
Err_MakeTable:
    MakeTable = False
    Resume Exit_MakeTable
    
End Function

Why though, don't you just create the table manually if you are only going to have five records in - what I assume - is a lookup?

 

[Mile-O]

Do i have to tell it thats its end a new statement each line. if so How.

SQL can only do one action at a time. You'd need six queries to do all that. I don't see, however, why you can't just create the table and put the five records into it.

 

[danielgraham]

well. i have 22 database's to update. and i wanted to learn about sql

 

[danielgraham]

Done

       Dim strSQL0 As String
       Dim strSQL1 As String
       Dim strSQL2 As String
       Dim strSQL3 As String
       Dim StrSQL4 As String
       Dim strSQL5 As String
       Dim strSQL6 As String
    
strSQL0 = "CREATE TABLE MoP([MoP] long, [Description] Text, " & _
        "CONSTRAINT [Index1] PRIMARY KEY ([MoP]));"
strSQL6 = "Delete From MoP"
strSQL1 = "insert into MoP(MoP, Description) Values (0,'Cash')"
strSQL2 = "insert into MoP(MoP, Description) Values (1,'Cheque')"
strSQL3 = "insert into MoP(MoP, Description) Values (2,'Credit Card')"
StrSQL4 = "insert into MoP(MoP, Description) Values (3,'XMAS Club')"
strSQL5 = "insert into MoP(MoP, Description) Values (255,'Cash')"

    
        
    DoCmd.RunSQL strSQL0
    DoCmd.RunSQL strSQL6
    DoCmd.RunSQL strSQL1
    DoCmd.RunSQL strSQL2
    DoCmd.RunSQL strSQL3
    DoCmd.RunSQL StrSQL4
    DoCmd.RunSQL strSQL5

Works Fine.

Thanks

 

[Mile-O]

If you wrap it like this you can run any contingency should it ever fail.

Public Function MakeTable() As Boolean

    On Error Goto Err_MakeTable

       Dim strSQL0 As String
       Dim strSQL1 As String
       Dim strSQL2 As String
       Dim strSQL3 As String
       Dim StrSQL4 As String
       Dim strSQL5 As String
       Dim strSQL6 As String
    
    strSQL0 = "CREATE TABLE MoP([MoP] long, [Description] Text, " & _
        "CONSTRAINT [Index1] PRIMARY KEY ([MoP]));"
    strSQL6 = "Delete From MoP"
    strSQL1 = "insert into MoP(MoP, Description) Values (0,'Cash')"
    strSQL2 = "insert into MoP(MoP, Description) Values (1,'Cheque')"
    strSQL3 = "insert into MoP(MoP, Description) Values (2,'Credit Card')"
    strSQL4 = "insert into MoP(MoP, Description) Values (3,'XMAS Club')"
    strSQL5 = "insert into MoP(MoP, Description) Values (255,'Cash')"

    
        
    DoCmd.RunSQL strSQL0
    DoCmd.RunSQL strSQL6
    DoCmd.RunSQL strSQL1
    DoCmd.RunSQL strSQL2
    DoCmd.RunSQL strSQL3
    DoCmd.RunSQL StrSQL4
    DoCmd.RunSQL strSQL5 

    MakeTable = True

Exit_MakeTable:
    strSQL0 = vbNullString
    strSQL1 = vbNullString
    strSQL2 = vbNullString
    strSQL3 = vbNullString
    strSQL4 = vbNullString
    strSQL5 = vbNullString
    strSQL6 = vbNullString
    Exit Function

Err_MakeTable:
    MakeTable = False
    Resume Exit_MakeTable

End Function

 

[danielgraham]

What i would like is a textbox on a form with a button called run.

in the textbox i can add multiple sql commands and run them in order.


please help