tags:

views:

1300

answers:

4

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

Update

Something like this

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

would be suitable as well

A: 
if not exists (select * from sysobjects where id = object_id(N'[TABLENAME]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
James L
-1 This is SQL Server, not MS Access. And the question just has *one* line of text.
Tomalak
Which would work in an An Access ADP. However the OP did not specify. So a clarification would have been in order;)
Oorang
I don't see anywhere in any of this whole thread that the OP has specified that it's a SQL Server back end.
David-W-Fenton
+3  A: 

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
This is why I argue that they aren't procedures for any meaningful definition of the term.
David-W-Fenton
They are fine for what they are: SQL code belongs in the 'backend' only, IMO. But let's not argue :)
onedaywhen
A: 

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

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
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
...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
...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