sql - 如何在Access VBA中分离SQL语句

如何构造一个SQL VBA块，以便执行多个SQL语句而不必为每个单独的SQL语句打开数据库？

例如，我尝试了以下两个代码块，但收到错误消息，指出FROM子句有问题。

   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

和

        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

最佳答案

为什么不只是再次调用.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