Create Table using ADO?

[Navarone]

I found a page at the MSDN Library witch list all the ADO properties but I couldn't find anything there about Creating a Table. I found some code which creates a table but I need to find the properties, what they mean and what they are. Are these SQL properties?

'create new table TestTable
    Call CreateConnection(objConn)
        objConn.Execute "CREATE TABLE TestTable(my_id  TEXT(50)  NOT NULL)"
    Call CloseConnection(objConn)

[Mark Gambo]

Take a look at this link and this one.

[Navarone]

Thanks those links were pretty helpful. Creating a table or Altering a table looks fairyly simple. How would I check to see if a table already exists?

[Mark Gambo]

Thanks those links were pretty helpful. Creating a table or Altering a table looks fairyly simple. How would I check to see if a table already exists?

I would think you could either try creating the table and then catch the "Table Exists Error" or try SELECT * FROM YourTableName" and then again trap for the error.

[Navarone]

Table Exists Error

Where can I find out more info on this or how to use it?

[Mark Gambo]

Where can I find out more info on this or how to use it?

Check this out

[si_the_geek]

or try SELECT * FROM YourTableName" and then again trap for the error.

This is the method I use, with a minor twist. I use "SELECT * FROM YourTableName WHERE 1=2"

As 1 does not equal 2, no data will be returned if the table exists - and no error will occur (if it exists) either.

[Navarone]

I am not sure I understand, if the table doesn't exist to start with and then lets say when the form loads the table is created, all is well. The next time the form loads, it will try and create the table and thus generate an error.

But how can you do a SELECT statement to a non existing table when the form loads for the first time?

[Mark Gambo]

I am not sure I understand, if the table doesn't exist to start with and then lets say when the form loads the table is created, all is well. The next time the form loads, it will try and create the table and thus generate an error.

But how can you do a SELECT statement to a non existing table when the form loads for the first time?

Are you going to be Creating and then Dropping a table each time your app runs ? Can you explain exactly what you are trying to do and we probably can help you out.

[si_the_geek]

But how can you do a SELECT statement to a non existing table when the form loads for the first time?

You can try to run any Select statement you like, if the table doesn't exist you will get an error (which you can trap with an error handler).

[kaffenils]

Thanks those links were pretty helpful. Creating a table or Altering a table looks fairyly simple. How would I check to see if a table already exists?

Personally I wouldn't use the methods that has been suggested. Most database systems has schema tables that gives you information about tables, views/queries, columns etc. It's usually better to query these to check if an object already exist than to trap an error.

The way to check if a table exists depends on the database system. In SQL Server you can call the sp_tables 'tablename' sproc or quyery the sysobjects table in the database. If sp_tables returns a row, then the table exists and you don't have to execute the CREATE TABLE. If it is Access you can a table called MSysObjects where the column Name contains the name of the table/query/Form/Report etc.

[si_the_geek]

That's fair enough.. you could also do the same thing with ADOX, so that you don't need to use specific methods for each DBMS.

[kaffenils]

That's fair enough.. you could also do the same thing with ADOX, so that you don't need to use specific methods for each DBMS.

Wow!! Now I feel stupid. For some strange reason I have never tried ADOX, and I'm curious by nature.

THANKS!!!!

[si_the_geek]

Lol... last week somebody let sz' know something new to him about debugging SQL Server SP's, and now I've found something new for you about ADO.

I guess all of us can learn new things in areas we excel at!

[Navarone]

Wow! So much info at once. Ok here is what I need to do. I need to add a table to my existing MS Access database called TempDocAudio with a text field called "n_id" and add two fields to an existing table called Documents and the new text fields are "n_mp3" and "n_wavBol" all of the same database.

So maybe you guys can help me script this out. In my module I set up a sub like this to access my database then when ever I need to make a call to the db I do a Call CreateConnection(objConn) and then Call CloseConnection(objConn).

Sub CreateConnection(objConn As Connection)
 
' CONNECTION STRING FOR THE DATABASE
    AccessConnect = "DRIVER={Microsoft Access Driver (*.mdb)};" & _
                    "DBQ=Westfield.MDB;" & _
                    "DefaultDir=" & App.Path & ";"
 
    'MsgBox AccessConnect
 
    ' Create Connection Object and open it
    Set objConn = New ADODB.Connection
 
    objConn.CursorLocation = adUseClient
 
    ' Trap any error/exception
    On Error GoTo AdoError
 
    objConn.ConnectionString = AccessConnect
    objConn.Open
 
    Exit Sub
 
    ' ADO Error/Exception Handler
AdoError:
    ErrNumber = Err.Number
    ErrSource = Err.Source
    ErrDescription = Err.Description
 
    'AdoErrorEx List1, objConn
 
End Sub
 
Sub CloseConnection(objConn As Connection)
 
    objConn.Close
    Set objConn = Nothing
 
End Sub

So on my Sub Form Load I have this, how do I correctly scritp out what I want to do.

Private Sub Form_Load()
 
    On Error GoTo FormLoadError
 
    
    frmIntro.Caption = "1:1 card " & Chr(147) & "Because every customer is different" & Chr(153) & Chr(46) & Chr(148)
    frmIntro.Label1.Caption = "© Copyright 2005 Westfield Insurance. All rights reserved."
 
    
    'create directories if they do not already exist
    If Dir(App.Path & "\" & "CDTypes", vbDirectory) = "" Then
        'MsgBox "Directory doesn't exist"
        MkDir (App.Path & "\" & "CDTypes")    ' make new directory
        MkDir (App.Path & "\" & "WavFiles")    'make wavFile directory
    Else
        'MsgBox "Directory Exist"
    End If
    
 
    
    'create new table TestTable
    Call CreateConnection(objConn)
        objConn.Execute "CREATE TABLE TestTable(my_id  TEXT(50)  NOT NULL)"
    Call CloseConnection(objConn)
    
    Call CreateConnection(objConn)
    objConn.Execute "ALTER TABLE TestTable (my_name  TEXT(50)  NOT NULL)"
    Call CloseConnection(objConn)
    
' Error handler
FormLoadError:
  If Err.Number <> 0 Then
        errLogger Err.Number, Err.Description, "Open File"
        Err.Clear
        Resume Next
        'Exit Sub
    End If
    
 
End Sub

[Navarone]

I guess what I have will work, but very clumsly. How do I specify the Default Value for my field. I need the fields Default Value to be False.

[Navarone]

Ok I have been searching thru threads and tried this but I get an error in the CONSTRAINT clause

-2147217900,"[Microsoft][ODBC Microsoft Access Driver] Syntax error in CONSTRAINT clause.

Call CreateConnection(objConn)
        objConn.Execute "ALTER TABLE Documents ADD n_wavBol TEXT(50) CONSTRAINT n_wavBol_default DEFAULT False"
    Call CloseConnection(objConn)

[Navarone]

I also tried this

objConn.Execute "ALTER TABLE Documents ADD n_wavBol TEXT(50)CONSTRAINT n_wavBol_default DEFAULT 'FALSE'"

But this doesn't work either.