dcsimg
Results 1 to 10 of 10

Thread: New Table or not?

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    May 2006
    Posts
    612

    New Table or not?

    Using Access how do I check to see if a table exists in a database to which I have a connection?

    If it does not exist how do I create it ?

    If I need to create it can I create and name the fields I need in the same code?

  2. #2
    Super Moderator Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,335

    Re: New Table or not?

    Select count(*) from sysobjects where name = 'Yourtablename'
    Please use [Code]your code goes in here[/Code] tags when posting code.
    When you have received an answer to your question, please mark it as resolved using the Thread Tools menu.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    I dont answer coding questions via PM or EMail. Please post a thread in the appropriate forum section.

    Creating A Wizard In VB.NET
    Paging A Recordset
    What is wrong with using On Error Resume Next
    Good Article: Language Enhancements In Visual Basic 2010
    Upgrading VB6 Code To VB.NET
    Microsoft MVP 2005/2006/2007/2008/2009/2010/2011/2012/Defrocked

  3. #3

    Thread Starter
    Fanatic Member
    Join Date
    May 2006
    Posts
    612

    Re: New Table or not?

    Select count(*) from sysobjects where name = 'Yourtablename'

    Can you recommend a good text book

  4. #4
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,354

    Re: New Table or not?

    The code Hack posted will return 1 if the table exsists. It will return 0 if the table does not exsist. If return is 0 then you use the Create Table SQL statement to create the table with the field specifications you want.
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  5. #5
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    4,229

    Re: New Table or not?

    If you are using DAO then this will work,

    Code:
    Dim td As DAO.TableDef
        'Create table object
        Set td = dbDB.CreateTableDef(TableName)
        'add fields
        Set f = td.CreateField("ID", dbText, 12)
        'Append field to table
        td.Fields.Append f
        Set f = td.CreateField("User", dbText, 12)
    
        'Append Table to Database
        dbDB.TableDefs.Append td

  6. #6
    Super Moderator Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,335

    Re: New Table or not?

    Quote Originally Posted by JohnSavage
    Can you recommend a good text book
    A book on what?
    Please use [Code]your code goes in here[/Code] tags when posting code.
    When you have received an answer to your question, please mark it as resolved using the Thread Tools menu.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    I dont answer coding questions via PM or EMail. Please post a thread in the appropriate forum section.

    Creating A Wizard In VB.NET
    Paging A Recordset
    What is wrong with using On Error Resume Next
    Good Article: Language Enhancements In Visual Basic 2010
    Upgrading VB6 Code To VB.NET
    Microsoft MVP 2005/2006/2007/2008/2009/2010/2011/2012/Defrocked

  7. #7

    Thread Starter
    Fanatic Member
    Join Date
    May 2006
    Posts
    612

    Re: New Table or not?

    Sorry wes4dbt I am using ADO and have never used DAO

    Hack "A book on what?" SQL, or a good source.

    It seems to me I need to do a lot of learning as I have absolutly no idea of what "sysobjects" is, nor have I ever seen it used in any examples or any of the books I own.

    At the moment I use a database with a recordset to simply store and retrive variable values, having first created the blank database and tables in Access with the correct number and data type for the fields.

  8. #8
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    4,229

    Re: New Table or not?

    I thought you might be working on an old program. This code should work with ADO, it worked for me.

    conCnn is a string
    conCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & < your database >

    Code:
    Dim adoCon As New ADODB.Connection, cmd As New ADODB.Command
    
    adoCon.Open conCnn
    Set cmd.ActiveConnection = adoCon
    cmd.CommandText = "Create Table wes ( nameid char(3), age INTEGER )"
    cmd.CommandType = adCmdText
    cmd.Execute
    I've had good luck getting books from Amazon ( and cheap ). I would suggest reading the reviews to see if the book seems to meet your needs. Everyone seems to have their own favorites, a guess everyone learns in different ways. I would suggest getting an ADO book and a SQL book.

    Good Luck

  9. #9
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    4,229

    Re: New Table or not?

    Select count(*) from sysobjects where name = 'Yourtablename'
    I would also like to know more about this statement.

    It doesn't seem to work with an ACCESS database.

  10. #10
    Banned randem's Avatar
    Join Date
    Oct 2002
    Location
    Maui, Hawaii
    Posts
    11,385

    Re: New Table or not?

    Look in my signature for DBASEIV To Access Conversion. The project uses a method that will work for all databases to find if a table exist.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width