Data type Conversion problems using DTS

[mike Franklin]

I am trying to execute a custom DTS package i made using VB. The SQL datatypes that I am using are short, VarChar and Single in SQL. Would anybody happen to know the equivalent data types in MSAccess 97?

[Jun]

Hi Mike,

Here is the information I retrieved from BOL.

****************
Data Types in Access and SQL Server
The available data types are different in Microsoft® Access and Microsoft SQL Server™. SQL Server bases its data types on the C programming language; Access bases its data types on Microsoft Visual Basic®. The data types are converted as described in the following table.

Access SQL Server
Text Varchar
Memo Text
Byte Smallint
Integer Smallint
Long Integer Int
Single Real
Double Float
Date/Time Datetime
Currency Money
Autonumber Int (Identity)
Yes/No Bit
OLE Object Image


Be careful when selecting which data types to use in SQL Server. You can change a field’s data type in Access; however, it is not possible to change a data type in SQL Server. A new table must be created before the data can be transferred to that table.

Timestamps
The SQL Server timestamp data type has no counterpart in Access. In spite of its name, it is neither a time nor a date, nor is it some encoded representation of a time or date. A timestamp is a binary number column that is updated automatically every time a row is inserted or updated. This permits the client program to confirm whether values have changed since it last checked the record. A timestamp column enables BROWSE-mode updates and makes cursor update operations more efficient.

The timestamp field indicates only that a record was changed, not when it was changed. You cannot set the timestamp column to any specific value. To record the times data modifications take place in a table, use either a datetime or smalldatetime data type to record the events and triggers to update the values automatically when any modification takes place.

AutoNumber and Identity Fields
SQL Server has an automatically incrementing field, called an Identity field, similar to the Access AutoNumber field. When migrating data from an AutoNumber field to an Identity field, turn off the functionality of the field with the IDENTITY_INSERT option, copy the existing AutoNumber values into the table, and then turn the IDENTITY_INSERT option back on. This preserves the original AutoNumber values from your Access table and begins future records with the highest number in your table, incrementing by one.

Changing True from -1 to +1
Access stores either a zero (0) or a negative one (-1) in the Yes/No data type. Access interprets 0 as 0, No, or False, and interprets any nonzero value as –1, Yes, or True.

A bit field stored in SQL Server that is selected in ISQL/w appears as either a zero (0) or a positive one (1), where 0 represents false and 1 represents true.

****************

Hope this helps!

Jun
------------
mike Franklin at 3/13/01 4:39:26 PM

I am trying to execute a custom DTS package i made using VB. The SQL datatypes that I am using are short, VarChar and Single in SQL. Would anybody happen to know the equivalent data types in MSAccess 97?