|
Home > Archive > MS Access data conversion > November 2005 > Conversion from Access to SQL Server
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Conversion from Access to SQL Server
|
|
| fniles 2005-11-29, 8:25 pm |
| If the Access field is a "Yes/No" field, in Sql Server to what Data Type
should I convert it to ? Thanks.
| |
|
| Means flag field ..i think you can take 'char' or 'varchar'
You can use cast or convert to do this or you can put the trigger to diplay
automatically after every insert or update
Kumar
"fniles" wrote:
> If the Access field is a "Yes/No" field, in Sql Server to what Data Type
> should I convert it to ? Thanks.
>
>
>
| |
| Danny J. Lesandrini 2005-11-29, 8:25 pm |
| I convert them to smallint, though some use tinyint. The problem
with tinyint is that it limits the value to positive numbers, 0-255.
In Access, True = -1, which won't fit in that data type ... so, I
use smallint in SQL Server for Yes/No Access types. I wouldn't
use Char or Varchar. It will slow any comparisons in queries.
--
Danny J. Lesandrini
dlesandrini@hotmail.com
http://amazecreations.com/datafast
"fniles" <fniles@pfmail.com> wrote in ...
> If the Access field is a "Yes/No" field, in Sql Server to what Data Type should I convert it to ? Thanks.
>
| |
|
| bit
"fniles" <fniles@pfmail.com> wrote in message
news:%239bsUKS9FHA.1020@TK2MSFTNGP15.phx.gbl...
> If the Access field is a "Yes/No" field, in Sql Server to what Data Type
> should I convert it to ? Thanks.
>
| |
| Ron Hinds 2005-11-29, 8:25 pm |
| The equivalent data type in SQL Server is the bit data type. It can have
only two values 1 (True, Yes) or 0 (False, No).
"fniles" <fniles@pfmail.com> wrote in message
news:%239bsUKS9FHA.1020@TK2MSFTNGP15.phx.gbl...
> If the Access field is a "Yes/No" field, in Sql Server to what Data Type
> should I convert it to ? Thanks.
>
>
| |
| Danny J. Lesandrini 2005-11-29, 8:25 pm |
| .... or, bit type will work.
For some reason, I had problems with this in some applications. Can't remember
what they were, but I do remember that I swore off ever using the Bit type.
Maybe it was something I was doing, but there's a caveat emptor associated with
this data type, and I can't quite remember what it is.
--
Danny J. Lesandrini
dlesandrini@hotmail.com
http://amazecreations.com/datafast
"Ron Hinds" < __ron__dontspamme@we
dontlikespam_garagei
q.com> wrote ...
> The equivalent data type in SQL Server is the bit data type. It can have
> only two values 1 (True, Yes) or 0 (False, No).
>
| |
|
| The SQL Server Bit type can have 3 values: 1, 0, Null.
The Bit type will appear to be a Yes/No field in Access.
The Bit type is what you get from the Access conversion
wizard.
The null value will cause problems with Access. If you
choose to use the Bit type with Access, make sure you
set the Not Null property (and give it a default value of 0).
If you use Numeric types instead, they will appear as
numeric types in Access.
Some people think that is a good thing.
Some people would like to have null values in their
Yes/No fields (as we had with an earlier version of Jet)
The value of true is +1 in SQL Server (for compatibility
with PDP8 microcomputers) and -1 in Access (for compatibility
with BASIC). This causes problems when coding web pages
to use either kind of data source interchangeably, but does
not cause a problem in Access.
For these reasons and others, people who code across
a variety of programming platforms and database platforms
will sometimes choose to use a Numeric field in Access
and SQL Server rather than a bit field in both.
But if you are converting from Access to SQL server, use
a bit field, because it links to Access as a YesNo field.
(david)
"Ron Hinds" < __ron__dontspamme@we
dontlikespam_garagei
q.com> wrote in message
news:%2323kEqS9FHA.340@TK2MSFTNGP09.phx.gbl...
> The equivalent data type in SQL Server is the bit data type. It can have
> only two values 1 (True, Yes) or 0 (False, No).
>
> "fniles" <fniles@pfmail.com> wrote in message
> news:%239bsUKS9FHA.1020@TK2MSFTNGP15.phx.gbl...
>
>
| |
| Br@dley 2005-11-29, 8:25 pm |
| Xref: TK2MSFTNGP08.phx.gbl microsoft.public.access.conversion:46150 microsoft.public.sqlserver.programming:568834 microsoft.public.access:188595
Joe wrote:[color=darkred
]
> bit
>
> "fniles" <fniles@pfmail.com> wrote in message
> news:%239bsUKS9FHA.1020@TK2MSFTNGP15.phx.gbl...
And remember you must set the default value of BIT fields in SQL server
if you want the recordset to be updatable in Access :)
--
regards,
Bradley
A Christian Response
http://www.pastornet.net.au/response
|
|
|
|
|