Datatype – SQL Server to Access (Access 97)

Topic

New Reply

Hi,

I’ve created “linked tables” from SQL Server to Access. The datatypes for ‘decimal’ and ‘numeric’ in SQL Server will changed to ‘text’ in Access. Why is it so? Any way to correct it? Thanks…

Regards,
88

Reply | Quote

Replies

I believe that it has to do with the Size, Precicision, and Scale of the data fields.

In one of my Access 97 apps SQL numeric fields with 5(5,0) are shown as Number, Long Integers in Access.
Numeric fields with 13(22,6) and 13(20,0) are shown as Text in Access.

I can think of three ways to solve the problem:
(1) Modify the SQL tables to a lesser scale and precision
(2) Use the conversion functions (CDbl, CInt, CLng, etc.) to change the data type in queries, reports, etc.
(3) Updgrade to Access 2000, which doesn’t seem to have this problem.

Reply | Quote

The only data type in Access that is even similar to the decimal or numeric types in SQL Server is the currency field. Those data types in SQL Server are essentially BCD (Binary Coded Decimal) encoded types which eliminate the round-off problems you get with floating point numbers like you have in Access. You really don’t want to use them in a database where Access is used as the front-end, or at least certainly not where you need to do calculations.

Reply | Quote

Which is precisely why you create views and use those instead of trying to work directly with SQL tables. There are a number of field types in SQL Server that have to be converted to something else so Access can work with them.

Reply | Quote

Hi Charlotte,

Do you have detailed instructions or code that attaches to SQL Server Views from Access 97?

Thanks

Reply | Quote

Views are attached to in the same fashion that you refer to tables – whether using DAO or ADO. They can be linked using ODBC, and appear to Access as a SQL Server table. However there is an issue of updatability as it pertains to indexes – in older versions of SQL Server you couldn’t have an index on a view. SQL Server 2000 does support that, though not all SQL types will be familiar with it. If the view doesn’t have an index, Access will ask you to identify the primary key of the table. If you don’t, it won’t let you do updating with any query that uses the view, and in later versions, may not even let you update the table.

Reply | Quote