4

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

StayOnTarget
  • 11,743
  • 10
  • 52
  • 81
jonny
  • 1,326
  • 9
  • 44
  • 62

3 Answers3

5

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 :(

onedaywhen
  • 55,269
  • 12
  • 100
  • 138
4

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
Oorang
  • 6,630
  • 1
  • 35
  • 52
  • Where is the TableExists() function? I have written my own (indeed, I've written 4 different versions of it that use different approaches to solving the problem), but so far as I know there is no such function in Access. – David-W-Fenton May 27 '09 at 03:15
  • 1
    Yah, guess it would help if I posted the whole example:) Fixed. As a rule I try to avoid test-by-error functions, but as it happens this runs faster than iterating the collection and checking each name. – Oorang May 27 '09 at 08:35
  • Interesting comment. I am also against test-by-error, so I coded up three versions of a TableExists function. One uses the error, the second walks through the TableDefs collection and the third does a lookup in MSysObjects. For a single use when you're initializing a database variable, the MSysObjects version is fastest. When called in a loop with a pre-existing db variable, the error-raising version is fastest. The TableDefs version's speed is proportional to the number of tables. Since testing, I settled on the test-by-error version, though mine is substantially more complicated. – David-W-Fenton Jun 13 '09 at 04:10
-2

Why would you want to create a table? If it's for temporary data storage then that's fine otherwise that's usuaally not required.

See the TempTables.MDB page at my website which illustrates how to use a temporary MDB in your app. http://www.granite.ab.ca/access/temptables.htm

Tony Toews
  • 7,850
  • 1
  • 22
  • 27
  • No need for that, you can just use ACE/Jet SQL DDL "CREATE TEMPORARY TABLE". See Access2007 help for CREATE TABLE Statement (http://office.microsoft.com/en-gb/access/HA012314411033.aspx): "When a TEMPORARY table is created it is visible only within the session in which it was created. It is automatically deleted when the session is terminated. Temporary tables can be accessed by more than one user." – onedaywhen Jun 15 '09 at 07:48
  • ...sorry, my little joke, couldn't resist. Of course it's merely yet another example of the terrible documentation for the ACE/Jet engine :( – onedaywhen Jun 15 '09 at 07:50
  • 1
    ...aside from omissions (who no DEFAULT?) and IMO poor phrasing, I've spotted at least two other material misstatements on that one page alone! – onedaywhen Jun 15 '09 at 07:55