[resolved] CREATE TABLE blues...

[rickford66]

Hello,
I'm trying to create a table using the CREATE TABLE command (in Access 2000) and I'm having great difficulty. First off, I need to say that any solution must be ADO, not DAO. Here's the statement I'm using...

CREATE TABLE tblFoundItems (ModelNumber TEXT, FxCap TEXT, FyCap TEXT, _
        FzCap TEXT, MxCap TEXT, MyCap TEXT, MxCap TEXT);

This is straight out of the Access 2000 Developer's Handbook, yet it gives a compile error. I don't see what is wrong with the statement, but I'm new to Access... (so go easy on me).
Thanks

[dannymking]

Neither ADO or DAO can accomplish this one..

CurrentDb.Execute "CREATE TABLE tblFoundItems (ModelNumber TEXT, FxCap TEXT, " & _
"FyCap TEXT, FzCap TEXT, MxCap TEXT, MyCap TEXT)"

There was a duplication in your query string on MxCap

[rickford66]

Yes, that worked. I'm able to put data in the table no problem. The duplication was just an error do to copy/paste that I missed. Thanks for pointing it out. Now, I just have to ask one more question (I hope). When I'm finished using the table, I need to blow it away. How do I do that? Not just the connection to the table, but the table as well. I guess this is a 2 parter... before I create the table, I need to find out if it already exists... like, if it was created then the computer crapped out, it would leave the table in the DB and the program wouldn't work anymore. How can I check to see if it's there first?
Thanks.

[rickford66]

I found that...

CurrentDb.Execute "DROP TABLE tblFoundItems"

seems to destroy the table.... so I just need to know how to check for its' existance.
Thanks.

[techgnome]

Neither ADO or DAO can accomplish this one..

CurrentDb.Execute "CREATE TABLE tblFoundItems (ModelNumber TEXT, FxCap TEXT, " & _
"FyCap TEXT, FzCap TEXT, MxCap TEXT, MyCap TEXT)"

There was a duplication in your query string on MxCap

BTW: ADO Can run such a query, through the command.execute method.

Tg

[rickford66]

Ok, got it... execute the following before I create the table

If CheckTableExists("tblFoundItems") = True Then
        CurrentDb.Execute "DROP TABLE tblFoundItems"  'destroy the table
    End If

Then put this in a module...

Public Function CheckTableExists(strTableName) As Boolean
On Error GoTo ProcessError
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    rst.Open strTableName, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    CheckTableExists = True
    rst.Close
EndCheckTableExists:
    Set rst = Nothing
    Exit Function
 
ProcessError:
    CheckTableExists = False
    GoTo EndCheckTableExists
End Function

Thanks for all your help. Now I can get some work done.