I knew it was too simple! Create with a DEFAULT

[mud2]

The solution to my current problem, updating without duplicating seemed simple...but as usual, ACCESS tripped me up! Now I can't even CREATE a table with a default value!

Create table TableA (Name Char(20), Count Integer); Works fine
BUT
Create Table TableA (Name Char(20), Count Integer DEFAULT 0); Does Not!
What simple thing am I missing?

Doesn't work With a Not NULL either!

[Demosthine]

Good Afternoon.

Don't feel too baffled by this issue. It's actually really common and occurs because Access operates using an older subset of SQL Commands called ANSI 89 by default. There is an option to enable the extended command set, called ANSI 92. There are two ways to do this, depending on how you are running your Query.

If you are running your Query from inside Access, have your database open and go to Tools | Options... and select the Tables/Queries tab. In the bottom right corner of this window, there is a small frame for SQL Server Compatible Syntax (ANSI 92). Select one or both of the choices: This Database or Default for New Databases*.

* Note: If Default for New Databases is disabled (grayed out), go to
the Advanced tab. In the top right corner, you will find an option
for Default File Format. Make sure this is set to Access 2002-2003.
Go back to the Tables/Queries tab to set your ANSI 92 setting
above.

The second method is for using the MS Access Database driver through your User DSN. This may be the method you are using to access the data through external scripts, such as a webpage. To change this method, go to your Control Panel and open the ODBC Manager applet. From the User DSN tab, select the MS Access Database entry and click Configure... In the new dialog that appears, click the Advanced... button. Again, a dialog appears with a list of options. Find the entry in the list that reads ExtendedAnsiSQL and change it's value to a 1. Click OK repeatedly until you exit entirely out of the ODBC Manager.

This should solve your problems.
Scott