Access: create table if it does not exist

Question

Can you give an MS Access equivalent to MySQL CREATE TABLE IF NOT EXISTS ...?

Something like this:

IF <no such table>
CREATE TABLE history(<fields>)

would be suitable as well

Answer 1

For SQL DDL code the answer is no. ACE/Jet SQL does not have any control-of-flow syntax and a ACE/Jet PROCEDURE can only execute one SQL statement. Yes, that's right: an ACE/Jet PROCEDURE does not support procedural code :(

Answer 2

Here is how to do it via VBA:

Sub ViaVBA()
    Const strSQLCreateFoo_c As String = _
          "CREATE TABLE Foo" & _
          "(" & _
          "MyField1 INTEGER," & _
          "MyField2 Text(10)" & _
          ");"
    Const strSQLAppendBs_c As String = _
          "INSERT INTO Foo (MyField1, MyField2) " & _
          "SELECT Bar.MyField1, Bar.MyField2 " & _
          "FROM Bar " & _
          "WHERE Bar.MyField2 Like 'B*';"

    If Not TableExists("foo") Then
        CurrentDb.Execute strSQLCreateFoo_c
    End If
    CurrentDb.Execute strSQLAppendBs_c
End Sub

Private Function TableExists(ByVal name As String) As Boolean
    On Error Resume Next
    TableExists = LenB(CurrentDb.TableDefs(name).name)
End Function