Create Table If Not Exists

[jp2access]

The following code segment created the Users table:

CREATE TABLE [Users2]
([UserID] int primary key, [UserName] text(255), [Hash1] text(255),


[Firstname] text(255), [Lastname] text(255), [Email] text(255),
[Hint] text(255), [Hash2] text(255), [Active] YesNo,
[StartDate] DateTime, [LastUse] DateTime, [Level] integer)


However, I need this piece of code to stay in my application until I'm sure all of the database users have the table added.

Every time I attempt to call the SQL using:

• CREATE TABLE IF NOT EXISTS [Users] or
• IF NOT EXISTS [Users] CREATE TABLE,

I get an OleDbException with the unhelpful message, "Syntax error in CREATE TABLE statement" or "Invalid SQL statement," respectively.

What am I doing wrong?

[RuralGuy]

Is this all done in Access? How is your database distributed or structured for the users?

[jp2access]

Is this all done in Access? How is your database distributed or structured for the users?

It is hidden and they don't have access to it. The Windows Application uses it only.

Again, how do I write in a check that adds a table if it does not exist?

I found this piece of code, but I don't understand it:

 IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Users2]') AND type in (N'U')) 
CREATE TABLE [Users2] ...blah, blah, blah

What do the _ID, N, and type do? Could I use this?

[RuralGuy]

Try using this function:

Public Function IsTable(InName As String) As Boolean
   Dim db As DAO.Database
   Dim I As Integer
   Set db = CurrentDb()
   IsTable = False
   For I = 0 To db.TableDefs.COUNT - 1
      If db.TableDefs(I).Name = InName Then
         IsTable = True
      End If
   Next
   Set db = Nothing
End Function

[jp2access]

That's basically what I'm doing now in C#, but I have ADO.NET instead of DAO:

bool ok = true;
string sqlText = "SELECT Count(*) FROM [Users2]";
using (OleDbCommand cmd = new OleDbCommand(sqlText, myConn)) {
  try {
    cmd.Connection.Open();
    cmd.ExecuteNonQuery();
  } catch (OleDbException) {
    ok = false;
  } finally {
    cmd.Connection.Close();
  }
}
if (ok == false) {
  // now, create my table
}

Relying on the "catch" just seems sloppy, though.

[RuralGuy]

You know more about your project and C# than I do. I'm not certain I can provide any additional assistance in this thread.

[jp2access]

So, basically, there isn't a way to create any SQL that can check for the existence of the table? That's what I was looking for.

[RuralGuy]

Not as far as I know but I'm a long way from being an SQL expert.