MS Access / SQL Server reminders

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
GO
SET ANSI_NULLS ON
GO

ALTER       TRIGGER dbo.Formats_INSERT ON dbo.Formats
FOR INSERT AS BEGIN

SET NOCOUNT ON

INSERT into dbo.FormatsAudit(
 [ChangeWhen],
 [ChangeBy],
 [ChangeType],
 [ChangeProgram],
 [ChangeWorkstation],
 [FormatsTableID],
 [PreservationID],
 [FormatID],
 [FormatOther]
 )

SELECT GETDATE(),
 (SELECT SUBSTRING(SYSTEM_USER,CHARINDEX('\',SYSTEM_USER) + 1,LEN(SYSTEM_USER))),
 'I',
 LEFT(APP_NAME(),50),
 HOST_NAME(),
 [ID],
 [PreservationID],
 [FormatID] [int],
 [FormatOther]

 FROM INSERTED
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER       TRIGGER dbo.Formats_INSERT ON dbo.Formats
FOR INSERT AS BEGIN

-- TRAP identity from previous insert so MSAccess can verify base insert ok
DECLARE @identity int, @strsql varchar(128)
SET @identity=@@identity
--

SET NOCOUNT ON

INSERT into dbo.FormatsAudit(
 [ChangeWhen],
 [ChangeBy],
 [ChangeType],
 [ChangeProgram],
 [ChangeWorkstation],
 [FormatsTableID],
 [PreservationID],
 [FormatID],
 [FormatOther]
 )

SELECT GETDATE(),
 (SELECT SUBSTRING(SYSTEM_USER,CHARINDEX('\',SYSTEM_USER) + 1,LEN(SYSTEM_USER))),
 'I',
 LEFT(APP_NAME(),50),
 HOST_NAME(),
 [ID],
 [PreservationID],
 [FormatID] [int],
 [FormatOther]

 FROM INSERTED
END

-- Set @@Identity back to before this trigger
SET @strsql='select identity (int, ' + cast(@identity as varchar(10)) + ', 1) as id into #tmp'
EXECUTE(@strsql)
--

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
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
CREATE TABLE dbo.Tmp_tbFunctions
 (
 ID int NOT NULL IDENTITY (1, 1),
 Form nvarchar(25) NOT NULL,
 [Function] nvarchar(25) NOT NULL
 )  ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_tbFunctions ON
GO
IF EXISTS(SELECT * FROM dbo.tbFunctions)
  EXEC('INSERT INTO dbo.Tmp_tbFunctions (ID, [Function])
  SELECT ID, [Function] FROM dbo.tbFunctions TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_tbFunctions OFF
GO
DROP TABLE dbo.tbFunctions
GO
EXECUTE sp_rename N'dbo.Tmp_tbFunctions', N'tbFunctions', 'OBJECT'
GO
ALTER TABLE dbo.tbFunctions ADD CONSTRAINT
 PK_Table1_1 PRIMARY KEY CLUSTERED
 (
 ID
 ) ON [PRIMARY]

GO
COMMIT

 


 Access change event on a combo box: access the new value via the .text property.


Collation:

SELECT *
FROM ::fn_helpcollations()

EXEC sp_help DatabaseName