Search code examples
sql-serverms-access-2007linked-tables

Customizing the table linked to SQL Server in MS Access

Could you advise if there is a way to customize the data types and formats in linked table in MS Access connected to MS SQL database? Now I have the number looking like .00000 and dates shown as text.

Thank you a lot!

Solution

  • Data Types can only be changed by altering the source table in SQL Server. You can use Data Definition Language (DDL) SQL or an administration tool like SQL Server Management Studio to do this.

    Here is an example of a DDL command:

    ALTER TABLE yourTable ALTER COLUMN yourColumn nvarchar(100) NOT NULL;

    If there is data in the column already, it must be convertible to the new data type.

    Formatting is not done on table level but in the user interface (e.g. form, report). You can however define a default format for your data in the column properties of the table design view within Access. This format will be used as default if this fields is displayed in the UI.

    Format column property in table design view

    This is done the same way for linked tables as it is for local Access tables. And it works the same way, even though there is an info displayed, that you can't change this property. The format is stored locally in the Access database. So keep in mind that you will lose all format settings in linked tables if you delete and relink the table.