A personal repository of random information in compensation for a fatigued biological computer
EXEC sp_changeobjectowner 'rossli.zWork Request Table', 'dbo'
http://www.functionx.com/vbaccess/Lesson01.htm
http://www.accelebrate.com/sql_training/ssis_tutorial.htm
Reminder: You can use conditional forms to set read/edit access to fields for individual records. (As in a list view, you cannot progmatically manipulate access per field per record, what is set for 1 field applies for all visible records).
Was getting error:
'The data was added to the database but the data won't be displayed in the form because it doesn't satisfy the criteria in the underlying data source'
When updating data in subforms, linked via a many to 1 back to a single record view on the main form.
Any update or insert on the subforms has triggers for audit table inserts. It is the inserts that is causing MS Access to see an incorrect @@Identity after its insert/update action is returned from the SQL server (2000). From my reading (thanks Google) this is also the same with SQL Server 2005 & MS Access so I expect this issue to come up for the next few years.
IF @AccessionNumber = 'ALL'
-- Add box to all incomplete accessions
-- No check for bxID already in an accession as this should only be called on
-- a new bxID being added (called by tbBox INSERT trigger)
BEGIN
INSERT INTO dbo.tbValuationText (
acAccessionNumber,
bxID)
SELECT acAccessionNumber, @bxID
FROM dbo.tbAccession
WHERE acValuationCompletedDate is NULL
END
ELSE
-- Add box to specific accession only
-- Assumes bxID does not exist for this accession, and does no other checks
BEGIN
INSERT INTO dbo.tbValuationText (
acAccessionNumber,
bxID)
SELECT @AccessionNumber, @bxID
END
GO
http://jagbarcelo.blogspot.com/2006/06/problems-with-identity-fields-in-ms.html
Solution: http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125475
I.e In the triggered procedures, trap the @@identity from the MS Access insert and at the end of the procedure, set it back to what it was prior to the trigger (by doing an insert into a 'temporary' temporary table. (MSAccess waits for all triggered procedures to complete).
Example:
CREATE TRIGGER [dbo].[trg_MSsync_ins_TableName] on [dbo].[TableName] for insert not for replication
AS BEGIN
DECLARE @identity int, @strsql varchar(128)
SET @identity=@@identity
-- Rest of the trigger here
SET @strsql='select identity (int, ' + cast(@identity as varchar(10)) + ', 1) as id into #tmp'
EXECUTE(@strsql)
END
--------------
Trigger before: | After: |
SET QUOTED_IDENTIFIER ON ALTER TRIGGER dbo.Formats_INSERT ON dbo.Formats SET NOCOUNT ON INSERT into dbo.FormatsAudit( SELECT GETDATE(), FROM INSERTED GO |
SET QUOTED_IDENTIFIER ON ALTER TRIGGER dbo.Formats_INSERT ON dbo.Formats -- TRAP identity from previous insert so MSAccess can verify base insert ok SET NOCOUNT ON INSERT into dbo.FormatsAudit( SELECT GETDATE(), FROM INSERTED -- Set @@Identity back to before this trigger GO
|
Date validation:
MS Access recognises dates in the range -657,434 (January 1, 100 A.D.) to 2,958,465 (December 31, 9999 A.D.).
SQL Server recognises January 1, 1753 to year 9999.
So IsDate may be valid for MS Access but it will fall outside SQl Servers range:
Calling scalar UDF: SELECT dbo.udfIsxxxCompleted('xxxx')
SQL 2000 generated change script for adding a field to a table, shows backing up and repopulating including 'IDENTITY_INSERT ON':
BEGIN TRANSACTION GO |
Access change event on a combo box: access the new value via the .text property.
Collation:
SELECT *
FROM ::fn_helpcollations()
EXEC sp_help DatabaseName