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!
1
-
In linked tables the only way to edit the schema is to do it from the source table's db / server. The other option is to create a query based on that table that manipulates the data using functions like cvdate(fieldname) for a date field that is showing as text and Round(fieldname,2) for an integer showing too many decimal places.The Gambill– The Gambill2015-09-11 05:25:33 +00:00Commented Sep 11, 2015 at 5:25
Add a comment
|
1 Answer
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.
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.
