F
flouw
I have an Access2000 ADP that I want to run under Access2007. The problem I
have is that some forms take up to 45 seconds to open in Access2007! These
are not complicated forms--just simple navigable reference forms like setting
up transaction types etc. that are based on basic select statements like:
SELECT * FROM dbo.TableName
I get similar results when the data source is a stored procedure like this:
CREATE PROCEDURE dbo.spselTableName
AS
SET NOCOUNT ON
SELECT * FROM dbo.TableName
Where TableName is a reference table (less than 10 columns, all int
or nvarchar(100) max) containing about 15 or 20 rows. These forms open
instantly in Access2000.
I put a trace on to see what is happening on the SQL Server, and I noticed
heaps of nasty code like this that generates tens of thousands of reads:
select object_name(sotblfk.id), user_name(sotblfk.uid),
object_name(sotblrk.id), user_name(sotblrk.uid) from sysreferences srfk,
sysobjects sofk, sysobjects sotblfk, sysobjects sotblrk where srfk.constid =
sofk.id and srfk.fkeyid = sotblfk.id and srfk.rkeyid = sotblrk.id and
user_name(sofk.uid) = N'dbo' and object_name(sofk.id) =
N'FK_ForeignKeyTableName_TableName_PrimaryKeyFieldName'
It looks like Access2007 is reading all of the constraints for the
underlying table, including all foreign keys. My SQL database contains 1400+
tables all with properly constructed foreign keys and other constraints.
Any suggestion on how to NOT have Access2007 do this? Right now, Access2000
works great for this enterprise app, but I really like the new Access2007
features (and I don't want to still be developing Access2000 apps in 2010).
have is that some forms take up to 45 seconds to open in Access2007! These
are not complicated forms--just simple navigable reference forms like setting
up transaction types etc. that are based on basic select statements like:
SELECT * FROM dbo.TableName
I get similar results when the data source is a stored procedure like this:
CREATE PROCEDURE dbo.spselTableName
AS
SET NOCOUNT ON
SELECT * FROM dbo.TableName
Where TableName is a reference table (less than 10 columns, all int
or nvarchar(100) max) containing about 15 or 20 rows. These forms open
instantly in Access2000.
I put a trace on to see what is happening on the SQL Server, and I noticed
heaps of nasty code like this that generates tens of thousands of reads:
select object_name(sotblfk.id), user_name(sotblfk.uid),
object_name(sotblrk.id), user_name(sotblrk.uid) from sysreferences srfk,
sysobjects sofk, sysobjects sotblfk, sysobjects sotblrk where srfk.constid =
sofk.id and srfk.fkeyid = sotblfk.id and srfk.rkeyid = sotblrk.id and
user_name(sofk.uid) = N'dbo' and object_name(sofk.id) =
N'FK_ForeignKeyTableName_TableName_PrimaryKeyFieldName'
It looks like Access2007 is reading all of the constraints for the
underlying table, including all foreign keys. My SQL database contains 1400+
tables all with properly constructed foreign keys and other constraints.
Any suggestion on how to NOT have Access2007 do this? Right now, Access2000
works great for this enterprise app, but I really like the new Access2007
features (and I don't want to still be developing Access2000 apps in 2010).