Get your SQL data types right

7 July, 2023 PeCo Microsoft Access, SQL Server, 0

Think about your database as the foundation of your application, and don’t be afraid to correct bad design when you revisit your database. Build your Tables as if your clients have direct access to them and apply referential integrity where possible.

1. If the column is required, then don’t allow nulls.
2. Use the correct SQL Server data type for the MS-Access client.
3. Don’t be lazy and set zero defaults for all your numeric columns.
4. Add column restraints where applicable.
5. Make sure that all your Tables have an Identity column.

One of the Access/SQL Table design traps is failure to apply a zero default to Bit data type columns in SQL. You will get an erroneous error message if you fail to set a default value for Bit data type columns.

A well designed database will reduce data entry errors and your application support effort.

More information about SQL/Access data types can be found here.

Using VBA Internal Subroutines