January 30, 2006 at 8:03 am
I linked the SQL table for the some super users so that they can access my login table and add new user in to the login. But something it is strange, when they open the table in MS ACCESS, they are some row delete. I do not know why. Some time they did the change, the new user added in the login in the table in the SQL server did not change. I gave them the right as dbreader and dbwriter and public.
I do not know whether I post to the new forum, if not, please direct me.
Thanks.
Frances
January 30, 2006 at 11:10 am
This looks like a MSAccess matter.
Can we see the DLL of your Table? (use sql enterprise manager or sql analyzer to generate a create table script)
Does the linked table layout still matches the sql table layout?
January 30, 2006 at 11:32 am
Here is
if exists
(select * from dbo.sysobjects where id = object_id(N'[dbo].[login]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table
[dbo].[login]
GO
CREATE TABLE
[dbo].[login] (
[ID] [int]
NOT NULL ,
[Last] [nvarchar] (50)
COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[First] [nvarchar] (50)
COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Name] [nvarchar] (50)
COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[User] [nvarchar] (50)
COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Password] [nvarchar] (50)
COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Flag] [bit]
NULL ,
[Wincoach] [bit]
NULL ,
[HIPAA] [bit]
NULL ,
[DataEntry] [bit]
NULL
)
ON [PRIMARY]
GO
I use the link table wizdow to link the table in MS ACCESS. It should not have the issue for lay out. Thanks. Only thing I can think ID is auto number.
January 30, 2006 at 11:42 am
In order for Access to update a SQL table, all BIT fields must have a value and not allow nulls.
Michelle
January 30, 2006 at 12:22 pm
In addition you can set a default value for the bit fields so that Access doesn't try to insert NULL in them.
March 31, 2006 at 12:10 pm
how do i edit the table which have data there. Do I need to define bit_const. If I want to defaut value as 1. Can I do that in the design view without creating the default object.
CREATE DEFAULT bit_const AS '0'
GO
sp_bindefault bit_const, 'login.Flag'
go
sp_bindefault bit_const, 'login.Wincoach'
go
sp_bindefault bit_const,'login.DataEntry'
go
sp_bindefault bit_const,'login.HIPAA'
March 31, 2006 at 12:17 pm
how do i edit the table which have data there. Do I need to define bit_const. If I want to defaut value as 1. Can I do that in the design view without creating the default object.
CREATE DEFAULT bit_const AS '0'
GO
sp_bindefault bit_const, 'login.Flag'
go
sp_bindefault bit_const, 'login.Wincoach'
go
sp_bindefault bit_const,'login.DataEntry'
go
sp_bindefault bit_const,'login.HIPAA'
Thanks.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply