Results 1 to 7 of 7

Thread: odbc sql commands

  1. #1

    Thread Starter
    Fanatic Member Navarone's Avatar
    Join Date
    Jun 2003
    Location
    Akron, Ohio USA
    Posts
    740

    odbc sql commands

    Can someone point me to a list of SQL commands supported by ODBC and MS Access 2000 database?
    He who never made a mistake never made a discovery?

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: odbc sql commands

    In the DB FAQ (link below) are links to SQL tutorials, however these are generic SQL rather than specific to Access.

    Most will work for any DBMS, and there aren't many changes required for Access (dates should be enclosed in hashes like this: #1/4/2005# , and joins need brackets in particular places).

    If you are using a DAO connection then wildcards for Like will be * and ? , while for ADO they will be % and _ .

  3. #3

    Thread Starter
    Fanatic Member Navarone's Avatar
    Join Date
    Jun 2003
    Location
    Akron, Ohio USA
    Posts
    740

    Re: odbc sql commands

    Thanks. I have been trying to use the Alter Table parameter to add a field to my table, but I need the default value to be 'false'. I can add the field well enough but the default parameter does not seem to be supported or else I am doing it wrong. That was my interest in finding the supported commands specific to ODBC and Access 2000 database .
    He who never made a mistake never made a discovery?

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: odbc sql commands

    Ah yes, I remember that one from a few days ago... I haven't found an SQL solution I'm afraid

    An alternative tho would be to use ADOX to make the changes, I haven't found any working solutions (for Access defaults) on the forum, but this thread might be useful to you.

    Otherwise, there is DAO, which I think can change tables using the TableDefs collection.

  5. #5
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: odbc sql commands

    Seems like you need to do it in two steps - add the column in one ALTER statement and then set the "default".

    Check this link:

    http://msdn.microsoft.com/library/de...l/acintsql.asp

    Altering Tables
    After you have created and populated a table, you may need to modify the table's design. To do so, use the ALTER TABLE statement. But be forewarned, altering an existing table's structure may cause you to loose some of the data. For example, changing a field's data type can result in data loss or rounding errors, depending on the data types you are using. It can also break other parts of your application that may refer to the changed field. You should always use extra caution before modifying an existing table's structure.

    With the ALTER TABLE statement, you can add, remove, or change a column (or field), and you can add or remove a constraint. You can also declare a default value for a field; however, you can alter only one field at a time. Let's suppose that we have an invoicing database, and we want to add a field to the Customers table. To add a field with the ALTER TABLE statement, use the ADD COLUMN clause with the name of the field, its data type, and the size of the data type, if it is required.

    ALTER TABLE tblCustomers
    ADD COLUMN Address TEXT(30)

    To change the data type or size of a field, use the ALTER COLUMN clause with the name of the field, the desired data type, and the desired size of the data type, if it is required.

    ALTER TABLE tblCustomers
    ALTER COLUMN Address TEXT(40)

    If you want to change the name of a field, you will have to remove the field and then recreate it. To remove a field, use the DROP COLUMN clause with the field name only.

    ALTER TABLE tblCustomers
    DROP COLUMN Address

    Note that using this method will eliminate the existing data for the field. If you want to preserve the existing data, you should change the field's name with the table design mode of the Access user interface, or write code to preserve the current data in a temporary table and append it back to the renamed table.

    A default value is the value that is entered in a field any time a new record is added to a table and no value is specified for that particular column. To set a default value for a field, use the DEFAULT keyword after declaring the field type in either an ADD COLUMN or ALTER COLUMN clause.

    ALTER TABLE tblCustomers
    ALTER COLUMN Address TEXT(40) DEFAULT Unknown

    Notice that the default value is not enclosed in single quotes. If it were, the quotes would also be inserted into the record. The DEFAULT keyword can also be used in a CREATE TABLE statement.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  6. #6

    Thread Starter
    Fanatic Member Navarone's Avatar
    Join Date
    Jun 2003
    Location
    Akron, Ohio USA
    Posts
    740

    Re: odbc sql commands

    Thanks this is helpful, it would appear that the ODBC driver does not support the default option.

    Switching over to the Jet driver means reconfiguring all my database connections, not something I was looking forward too.
    He who never made a mistake never made a discovery?

  7. #7
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: odbc sql commands

    ODBC is old now - you might not want to switch - but it could have other payoffs as well. I'm not an ACCESS or ODBC expert though...

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

Posting Permissions

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



Click Here to Expand Forum to Full Width