CODEWITHSUNDEEP
sqlms-accessvba
sion_corn
sion_corn

Reputation: 3151

how to separate SQL statements in Access VBA

How can I structure a SQL VBA block so I can execute multiple SQL statements without having to open the DB for each separate SQL statement?

For example, I tried the both of the following code blocks, but got an error stating there was something wrong with the FROM clause.

   Dim dbs As Database

   Set dbs = OpenDatabase("path\database.mdb")

   dbs.Execute " INSERT INTO table1 (col1,col2,col3) " _
                & "SELECT F1,F2,Format$(Now(),'Short Date') " _
                & "FROM table2" _
                & "DROP table2;"
    dbs.Close

and 

        Dim dbs As Database

        Set dbs = OpenDatabase("path\database.mdb")

        dbs.Execute " INSERT INTO table1 (col1,col2,col3) " _
                    & "SELECT F1,F2,Format$(Now(),'Short Date') " _
                    & "FROM table2 " _
                    & "GO " _
                    & "DROP TABLE table2        
        dbs.Close

Upvotes: 2

Views: 603

Answers (2)

Linger
Linger

Reputation: 15068

If your tables are linked to the access database, then you can just use the DoCmd.RunSQL & DoCmd.DeleteObject commands like so:

DoCmd.RunSQL "INSERT INTO table1 (col1, col2, col3) " & _
             "SELECT F1, F2, Format(Date(), 'mm/dd/yyyy') " & _
             "FROM table2"

DoCmd.DeleteObject acTable, "table2"

Less code is needed and no opening and closing of a connection is required.

Upvotes: 3

Blackhawk
Blackhawk

Reputation: 6140

Why not just use a second call to .Execute?

Dim dbs As Database

Set dbs = OpenDatabase("path\database.mdb")

dbs.Execute "INSERT INTO table1 (col1,col2,col3) " _
            & "SELECT F1,F2,Format$(Now(),'Short Date') " _
            & "FROM table2"

dbs.Execute "DROP TABLE table2"

dbs.Close

Upvotes: 4

Related Questions