Records not inserting...

I am totally new to SQL server. I am moving all of our Access DB's over to
an SQL server DB - and am only using Access as the front end interface and
development tool.
The tables came across no problem and after I relinked all of them to the
SQL server DB tables using the ODBC driver, the app even ran without anyt
errors!! I was impressed.
However, something strange is happening that I am sure a seasoned SQL server
person will know...
When a user changes certain data, in my application code, I write changelog
records of each change.
To do this I "insert" a record into a table. THis is not happening and no
errors are being generated - at least none that I can see...
I stepped through my code and the insert command runs fine and does not go
through the "on error" routine. However, when I check the table after I have
inserted a record, none has been inserted.
I have made sure that the logged on user (me) has insert rights etc...
What should I be checking?
PS...Now that I am using SQL server, I suspect it would be easy to write a
trigger that automatically inserts records in a table instead of placing
these actions in my application code. However, in a trigger, would I be able
to reference what the old value was before the change so I have a record of
what the old value/new values are?
Thanks,
Brad"Brad Pears" <donotreply@.notreal.com> wrote in message
news:O1dX3IEuEHA.2116@.TK2MSFTNGP14.phx.gbl...
> I am totally new to SQL server. I am moving all of our Access DB's over to
> an SQL server DB - and am only using Access as the front end interface and
> development tool.
> The tables came across no problem and after I relinked all of them to the
> SQL server DB tables using the ODBC driver, the app even ran without anyt
> errors!! I was impressed.
> However, something strange is happening that I am sure a seasoned SQL
server
> person will know...
> When a user changes certain data, in my application code, I write
changelog
> records of each change.
> To do this I "insert" a record into a table. THis is not happening and no
> errors are being generated - at least none that I can see...
> I stepped through my code and the insert command runs fine and does not go
> through the "on error" routine. However, when I check the table after I
have
> inserted a record, none has been inserted.
> I have made sure that the logged on user (me) has insert rights etc...
> What should I be checking?
You need to turn on ODBC tracing. Put a break in your code just before the
INSERT. Turn on ODBC tracing when i braks. Run the INSERT statement. Turn
off Tracing. View the trace log and see what was actually sent.

> PS...Now that I am using SQL server, I suspect it would be easy to write a
> trigger that automatically inserts records in a table instead of placing
> these actions in my application code. However, in a trigger, would I be
able
> to reference what the old value was before the change so I have a record
of
> what the old value/new values are?
Yes, you can! From SQL Server Books Online (partial paste):
Two special tables are used in trigger statements: the deleted table and the
inserted table. Microsoft SQL Server 2000 automatically creates and
manages these tables. You can use these temporary, memory-resident tables to
test the effects of certain data modifications and to set conditions for
trigger actions; however, you cannot alter the data in the tables directly.
The inserted and deleted tables are used primarily in triggers to:
a.. Extend referential integrity between tables.
b.. Insert or update data in base tables underlying a view.
c.. Check for errors and take action based on the error.
d.. Find the difference between the state of a table before and after a
data modification and take action(s) based on that difference.
The deleted table stores copies of the affected rows during DELETE and
UPDATE statements. During the execution of a DELETE or UPDATE statement,
rows are deleted from the trigger table and transferred to the deleted
table. The deleted table and the trigger table ordinarily have no rows in
common.
The inserted table stores copies of the affected rows during INSERT and
UPDATE statements. During an insert or update transaction, new rows are
added simultaneously to both the inserted table and the trigger table. The
rows in the inserted table are copies of the new rows in the trigger table.
An update transaction is similar to a delete operation followed by an insert
operation; the old rows are copied to the deleted table first, and then the
new rows are copied to the trigger table and to the inserted table.

>
> Thanks,
> Brad
>
>