Linked SQL Table into Access -1 doesn't register

[BrianDP]

I have a table that has always been in my back-end of this application.
The table is getting quite large, and, on top of that, we lose records
out of this table. They just dissappear! No rhyme or reason to it.

I imported the table into SQL, and re-linked it back into the front end
of the application. All the fields show. I had to pick a primary key,
which is fine, it's a text field (nvarchar 255), and it is indeed a
unique value.

The problem started when I tried to use a simple query on this table.
The query is in access, SQL reads:

select * from catalog where catalog.exists = -1

even though -1 be a value in the exists field in catalog, (0 is the
other value) it doesn't return a lick of data. If I change the query
to:

select * from catalog where catalog.exists <0

then the query returns the proper record set. Anyone have any idea why
this could be happening?
In access (the old table) catalog had exists as a type Yes/No. Default
was no. In SQL, during the import the data type was changed to (Bit
Length 1, Not Null). Bit sounds problematic here, I'm really wanting a
true/false. Shouldn't it be boolean? Is there a boolean as a SQL
type, would this be more appropriate for a true/false situation? Am I
going to run into lots of problems with data types that get
auto-converted from access into SQL?

I noticed that SQL wants to bracket many of field names I have used
previously in this table in Access. User, Desc, Exists, Group, Open,
Close. This table is so big and unwieldy, and there is the problem of
the losing records, so I feel like I must put it in SQL to get a handle
on it - Access has no way to keep track of who or what is deleting a
record. My understanding is that it is possible to turn auditing of
some kind on SQL so that I can figure out who/what is deleting those
records.

Sorry too many questions in one post.
Thanks in Advance!

-BrianDP
Accessor on the move to SQL

[Rich P]

Greetings,

If you are certain that rows in your table are being removed, then I
would guess that some procedure in your Access front end is causing this
to happen or some user is unwittingly removing rows. Nothing happens
just by chance. Only some action can cause rows to be removed.

As for auditing deletes on the table, this is easy. Now that your table
is in a Sql Server DB, you can write a delete trigger with a timestamp
to write the deleted row to another table so you can look at it. Matter
of fact, if you don't want rows to be able to be deleted from the table
you can use a trigger like this one:

Create Trigger t_NoDeletes On dbo.tbl_yourTbl
For Delete

As

rollback transaction
declare @s varchar(50)
set @s = 'No deleting allowed from tbl_yourTbl
RaiseError (@s, 16, 10)

Return

actually, in the body of the trigger (after the AS keyword) you only
need >rollback transaction << to prevent deletes. But I like to add a
message so that people know that deletes won't happen on this table.
RaiseError will raise a messagebox in Access with the message I
included.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Rich P]

Here is a better version of the dontDelete trigger

Create Trigger t_NoDeletes On dbo.tbl_yourTbl
Instead Of Delete

As

RaiseError ('Deleting not allowed on this table!', 16, 10)
rollback transaction

Return

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[pietlinden]

Rich P wrote:

Here is a better version of the dontDelete trigger

Create Trigger t_NoDeletes On dbo.tbl_yourTbl
Instead Of Delete

As

RaiseError ('Deleting not allowed on this table!', 16, 10)
rollback transaction

Return

Rich

*** Sent via Developersdex http://www.developersdex.com ***

Rich,
not to totally hijack this post, but do you have any good references
for migrating to SQL Server and/or upsizing to SQL Server? I know
Baron & Chipman's book is good... is there a new ones coming for SQL
Server 2005?

Thanks
Pieter

[Rich P]

Actually, I am in your boat on this. We are migrating to Sql Server
2005 over at my place, and I don't have any solid reference books on it.
But I do subscribe to a sql server journal where I am currently getting
most of my information. Just type sql server journals in google, and
that will bring up several publications.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[BrianDP]

Rich,

Thanks for the nodelete script. I've suspected it was something in my
front end for years.

I hate to be a bugger, but what about the bit/binary question? Binary
seems like a more logical choice for a Yes/No type of question. I
assume the values for Binary are 0/1? So, um, typically in C anyway, 0
is no, and -1 is Yes, so if my supposition about SQL's Binary is
correct, 0/1, 0 would be no/False, and 1 would be Yes/true? Do I need
to somehow write this into the DTS that imports the table?

Seems like I'm going to have a few roadblocks when I come to converting
stuff straight from Access to SQL. The Wiazards would have you belive
"Just use the import script!" It'll create the DTS for you, and
violla! You're finished! Nothing ever seems so simple as that though,
especially if you're letting a wizard do the work for you. Maybe what
I do, is create a new field in the imported table, that is my new
binary field, and populate it from the Access side with some vb code,

sub convertexistsbutton()

dim db as database
dim rst as recordset

set db = currentdb()
set rst = db.openrecordset("catalog")

rst.movefirst
do while not rst.eof
rst.edit
if rst!exists = 0 then
rst!bin_exists = 0
else
rst!bin_exists = 1
end if
rst.update
rst.movenext
loop

end sub

Sweet aircode! I love VB/Access.....
Someone let me know if there is a problem with this since I"m dealing
with an attached SQL db..

TIA!

-BrianDP
Laboring with SQL Conversion
On Oct 16, 5:31 pm, Rich P <[e-mail]:

Here is a better version of the dontDelete trigger

Create Trigger t_NoDeletes On dbo.tbl_yourTbl
Instead Of Delete

As

RaiseError ('Deleting not allowed on this table!', 16, 10)
rollback transaction

Return

Rich

*** Sent via Developersdexhttp://www.developersdex.com***

[Bri]

I hate to be a bugger, but what about the bit/binary question? Binary
seems like a more logical choice for a Yes/No type of question. I
assume the values for Binary are 0/1? So, um, typically in C anyway, 0
is no, and -1 is Yes, so if my supposition about SQL's Binary is
correct, 0/1, 0 would be no/False, and 1 would be Yes/true? Do I need
to somehow write this into the DTS that imports the table?

Yes, Bit can be either 0 or 1. Access' Boolean field is -1 or 0. I often
convert to an SmallInt rather than to Bit so I can continue to store -1
and 0. Yes, its a waste of space, but it reduces the amount of code you
have to check over. If you do convert to Bit, you can use comparisons to
True or False then Access will send the correct request to SQL Server,
but if you compare to -1 it will look for that value and it can't exist.

--
Bri