Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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
  • 5
  • 3
  • 2
  • +1
LVL 55

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 55

Expert Comment

by:Ryan Chong
ID: 8063611
Nice one, Tim..
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.


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

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month10 days, 12 hours left to enroll

571 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