SQL statement - create table

Posted on 2003-03-04
Medium Priority
Last Modified: 2010-05-01
Hi there,

How to write a SQL statement to check whether NEW created table name exist in existing database(Access)?  I allow users to create table when they want to do backup, copy from existing table to new table in another database.  

Question by:laysee
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +1
LVL 53

Expert Comment

by:Ryan Chong
ID: 8063202
I think Access cannot handle this in SQL, so you will need to do coding (in DAO or ADO) to check if a table is already exist or not.
LVL 43

Accepted Solution

TimCottee earned 480 total points
ID: 8063389
You can do it with this statement:

SELECT Count(MSysObjects.Name) As ExistingTable
FROM MSysObjects
WHERE (((MSysObjects.Type)=1))
And MSysObjects.Name = 'MyNewTable'

If you have a value of 1 in ExistingTable returned then the table name exists in the database.
LVL 53

Expert Comment

by:Ryan Chong
ID: 8063611
Nice one, Tim..
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Expert Comment

ID: 8063630
You can use a modification of the 1.x example in http://support.microsoft.com/default.aspx?scid=kb;en-us;90989

SELECT COUNT(Name) FROM MSysObjects WHERE Type=1 And Name = 'NewTable'

You can use this in Transact-SQL as:

IF (SELECT COUNT(Name) FROM MSysObjects WHERE Type=1 And Name = 'NewTable') > 0
     PRINT 'The table already exists'
     PRINT 'The table does not exist'

Expert Comment

ID: 8063633
oh, i guess tim beat me to it :-)

Expert Comment

ID: 8070935
This is how I once solved the issue in a school project (a project that never should have happened as the app development was not part of the specifications, only modification of an existing app to impliment error handling and to access a network database instead of a local database, what does the instructor say??? Take this project, which is already a two person group project, do it from scratch, and hand it in in three days. I say. Here you go mr. instructor, one month later. he says. Where's the user manual? Like he didnt pile enough extra on top. Meanwhile, finish user manual and hand in project on same day as the following project some time last September or October, (This is a community college, CDI College Winnipeg, to be precise) they do not implement a multi-course schedule. You finish the one you are working on before starting the next. At this time, I was working on three courses at the time. One a business skills course, two; the one this code originates from, and three; a THREE person group project on object-oriented design, no small feat, little time for completion, no extensions. I went and picked up the project from school just this week 'cause they disenrolled me for atteendance (like I wanted to go anyway, and they knew I couldnt go anyway cause I had to stay at home to look after our year-old child while my common-law went back to work. and besides, I was beyond schedule because of all these group projects (these were not the only ones) and they failed to reduce my work-load or hold me back for a while until someone caught up to where I was at in the diploma program (she was one course behind, only about 2 to 3 weeks))(they failed to get me to sign an extension contract, the mr. instructor was present at that meeting for oh, 2 minutes before he walked out), as they say...my attendance was the issue, i say, they neglected to follow up on their student's activities. The project was not marked, and when I asked them to sign a written statement stating they had given me the project in an unmarked state, they vehemently refused, called security and had me escorted out. Hows that for a highly-reputable educational institute? )
(this is not a complete list of greivences, by far, not even close)

(Sorry 'bout the rant, but I figured you might be interested in a little history of a chuck of code that never should have been written)

(the CreateInfoTable function will return true if it created the table and return false if something went wrong(that could be no connection to a database or the table was already there))
(function getConn returns nothing or a valid ADODB.Connection object)
<Access does not support stored procedures, shame)

(Well, you get the idea)

Public Function CreateInfoTable() As Boolean
'This table will be inserted into the database upon first execution of
'the application after installation. A special form will allow the manager
'to change these values, but not the property names as these names are hard-coded
On Error GoTo theEnd
    Dim cmd As ADODB.Command
    Dim arry(0 To 1, 0 To 9) As String
    Dim x As Integer, y As Integer
    For x = 0 To 1
        If Not cDemo Then
            gstrResponse = "0"
            Exit For
        End If
        gstrResponse = InputBox("Enter yearly interest rate that will be " & _
                "used for calculating interest charges." & vbCrLf & vbCrLf & _
                "Example: enter 10.5 for a 10.5% annual interest rate.")
        If gstrResponse = "" Then EndAll: Exit Function
        If IsNumeric(gstrResponse) Then x = 1
    Next x
    arry(0, 0) = "Yearly Interest Rate": arry(1, 0) = gstrResponse
    arry(0, 1) = "Update Frequency": arry(1, 1) = "Daily"
    arry(0, 2) = "Interest Last Update": arry(1, 2) = ""
    arry(0, 3) = "Next Tran_Num": arry(1, 3) = GenerateFirst("Tran_Num")
    arry(0, 4) = "Next Merchant_Key": arry(1, 4) = GenerateFirst("Merchant_Key")
    arry(0, 5) = "Next Account_Num": arry(1, 5) = GenerateFirst("Account_Num")
    arry(0, 6) = "Applying Interest": arry(1, 6) = "False"
    arry(0, 7) = "Current DatabaseDate": arry(1, 7) = ""
    arry(0, 8) = "Current DatabaseTime": arry(1, 8) = ""
    arry(0, 9) = "DueDate Period": arry(1, 9) = "28"
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = getConn(conAcc_Connect)
    cmd.CommandText = "CREATE TABLE Information " & _
               "(Property VARCHAR (20) NOT NULL, " & _
               "PropValue VARCHAR (100) NOT NULL, " & _
               "CONSTRAINT Pk_Property PRIMARY KEY (Property))"
    Dim theTime As Long
    theTime = Timer
    While Timer < theTime + 2
    For x = 0 To 9
        cmd.CommandText = "INSERT INTO Information " & _
               "VALUES('" & arry(0, x) & "','" & _
               arry(1, x) & "')"
    Next x
    cmd.CommandText = "UPDATE Information " & _
               "SET PropValue = CStr(Date() - " & _
               IIf(cDemo, 1, 0) & ") " & _
               "WHERE Property ='Interest Last Update'"
    cmd.CommandText = "UPDATE Information " & _
                   "SET PropValue = CStr(Time()) " & _
                   "WHERE Property ='Current DatabaseTime'"
'    cmd.CommandText = "CREATE PROCEDURE getDatabaseDate " & _
'                        "(IN defnct CHAR(2), OUT strDate CHAR(10)) " & _
'                         "BEGIN " & _
'                        "SELECT CStr(Date) AS crntDate INTO strDate " & _
'                         "END"
'    cmd.Execute
'    If False Then
        cmd.CommandText = "UPDATE Information " & _
                   "SET PropValue = CStr(Date()) " & _
                   "WHERE Property ='Current DatabaseDate'"
'    End If

    Set cmd = Nothing
    CreateInfoTable = True
    Exit Function
'    If Err.Number = -2147217900 Then
'        'Error is for CREATE TABLE SYNTAX but
'        'applies to CREATE PROCEDURE command questionably
'        'guess Access doesn't support stored procedures
'        'it is a shame cause it would be
'        'more efficient in this case
'        Err.Clear
'        Resume ProcNotWork
'    End If
    CreateInfoTable = False
End Function
LVL 43

Expert Comment

ID: 8070986
Of course a try it first then react if it fails approach will work it isn't really the "right" way to do it of course. Sometimes though there is no alternative. When as in this case there are perhaps 10 different alternatives that you could use that would be better than this approach I wouldn't recommend it.

That is not intended as an attack on your proposal, merely a comment that in my opinion there are better ways.

Expert Comment

ID: 8071080
I agree. it should not require the need to turn to error handling for program control.

Would it not generate an error though, to try to access a table, for even a count of records, if the table doesnt exist?

Expert Comment

ID: 8071132
I recall seeing somewhere, not exactly, and I dont know how it is implemented; or maybe I can find it again, but, can you not get the database contents, ie: table names in database, I guess that is like the type thing above, but isnt there an object that returns values as properties?
LVL 43

Expert Comment

ID: 8071168
It would, the solution I suggested though examines the built-in table MSYSObjects which cannot not exist so no error would be generated. If the row doesn't exist in the table then the count returns 0.

What you are perhaps thinking about is the ADO .OpenSchema method or the TableDefs collection in DAO, or again the Tables collection in ADOX. All provide different ways of accessing objects within a database structure.

Expert Comment

ID: 8071481
yup, .OpenSchema
i found the app that did it, that's the one
it navigates just like a recordset, pretty cool.
in the app though, they opt to go about it this way:

    Do Until rsSchema.EOF
         ' Since MS current schema returns tables named "MSys...."
         ' as well as their TABLE_TYPE is also "TABLE", we exclude them.
        If UCase(Left(rsSchema!Table_name, 4)) <> "MSYS" Then
           If UCase(Left(rsSchema!Table_name, 11)) <> "SWITCHBOARD" Then
               mtblName = rsSchema!Table_name ' this is what i was thinking(qwasted)
               Set mNodeTables = tvwDB.Nodes.Add("RootDB", tvwChild, "X" & mtblName, mtblName, "imgClosed")
               mNodeTables.Tag = "Tables"
           End If
        End If

the app displays contents(tables,fields,field properties,table data, etc.) of database that you select in common dialog...

your way is faster and less 'round-about. i'll have to remember that.

Expert Comment

ID: 8071537
   Set gAcnn = New adodb.Connection
    gAcnn.CursorLocation = adUseClient

    'gAcnn.Open "PROVIDER=Microsoft.Jet.OLEDB.3.51;Data Source=" & gFileSpec & ";"
       ' If you use 4.0
     gAcnn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & gFileSpec & ";"
'   Dim rsSchema As adodb.Recordset (module level variable (qwasted))
    Set rsSchema = gAcnn.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "TABLE"))

guess this is important too

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question