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
    I'm about to be a PowerPoster! 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'

  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,398

    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,450

    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
    I'm about to be a PowerPoster! 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?

  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,450

    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,450

    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