Can someone point me to a list of SQL commands supported by ODBC and MS Access 2000 database? :)
Printable View
Can someone point me to a list of SQL commands supported by ODBC and MS Access 2000 database? :)
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 _ .
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 . :)
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.
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
Quote:
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.
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. :cry:
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...