bookmark continuous form slow slow slow

[eighthman11]

using Access 2003 and sql server version 8.0

Hey everyone. Created a text box where the user types in an Inventory
number and it takes them to that inventory number on the contimuous
form. The form is based on a link table to sql server. Here is the
code:

Dim rst As DAO.Recordset
Dim InventoryItem As String
InventoryItem = "'" & "TextBoxValue" & "'"
Set rst = Me.RecordsetClone

rst.FindFirst "InventoryNumber = " & InventoryItem
If rst.NoMatch Then
MsgBox "Record not found"
Else
Me.Bookmark = rst.Bookmark
End If
rst.Close

Pretty simple code and it works, but it is slow, slow, slow. There is
only 8500 records on the continuous form. The closer the search
number is from the top the faster it goes. But it you go to a number
at the bottom it takes about 10 seconds.

When I linked by sql table (ODBC) I gave it a key but when I look at
the individual fields in design view for the table I can see the key
but no indexs on the fields. I have CREATE UNIQUE CLUSTERED INDEX
for the table on the sql server.

Is there anything I can do to make this "goto record" work faster.
Any help appreciated.

thanks ray

[Neil]

You say that when you linked the SQL table you gave it a key. You shouldn't
have had to do that. If you gave your SQL table a primary key, then the
linking process should have automatically picked that up and make that the
key for your linked table. If you were prompted for the key, then something
is wrong. Make sure your table has a primary key defined.

Sometimes, if you have indexes defined on your SQL table, and if those index
names fall alphabetically before your primary key, Access may pick up one of
those instead, and, thus, prompt you for the key, since it picked up a
non-unique index. A way to resolve that is to rename your primary key to
have it start with "aaaa" or something, to make sure it's first.

Second, using FindFirst is slow; but it shouldn't be that slow. I had slow
results once by using a form that was saved as an unbound form, to which I
added a recordset after the form was opened. I found that saving the form as
a bound form (even bound to a zero-record recordset) made a HUGE difference,
over saving it as an unbound form and then adding a recordset on the fly. So
make sure that you save your form as a bound form, and not as an unbound
form.

Last, though you should be getting better performance even using FindFirst,
you might want to consider a different mechanism. Do you need 8500 records
in your continuous form?? Is the user going to scroll through 8500 records??
Consider which records the user actually wants/needs and abbreviate your
recordset to show those. It could be that they only need one at a time, in
which case you can just change the recordset to display that one record. Or,
if they need a set of records. But I don't think they'd need 8500 records in
a continuous form! Unless they're speed readers, that is... ;-)

HTH,

Neil

"eighthman11" <rd******@nooter.comwrote in message
news:10**********************************@o6g2000h sd.googlegroups.com...

using Access 2003 and sql server version 8.0

Hey everyone. Created a text box where the user types in an Inventory
number and it takes them to that inventory number on the contimuous
form. The form is based on a link table to sql server. Here is the
code:

Dim rst As DAO.Recordset
Dim InventoryItem As String
InventoryItem = "'" & "TextBoxValue" & "'"
Set rst = Me.RecordsetClone

rst.FindFirst "InventoryNumber = " & InventoryItem
If rst.NoMatch Then
MsgBox "Record not found"
Else
Me.Bookmark = rst.Bookmark
End If
rst.Close

Pretty simple code and it works, but it is slow, slow, slow. There is
only 8500 records on the continuous form. The closer the search
number is from the top the faster it goes. But it you go to a number
at the bottom it takes about 10 seconds.

When I linked by sql table (ODBC) I gave it a key but when I look at
the individual fields in design view for the table I can see the key
but no indexs on the fields. I have CREATE UNIQUE CLUSTERED INDEX
for the table on the sql server.

Is there anything I can do to make this "goto record" work faster.
Any help appreciated.

thanks ray

[eighthman11]

On Nov 21, 5:30 pm, "Neil" <[e-mail]:

You say that when you linked the SQL table you gave it a key. You shouldn't
have had to do that. If you gave your SQL table a primary key, then the
linking process should have automatically picked that up and make that the
key for your linked table. If you were prompted for the key, then something
is wrong. Make sure your table has a primary key defined.

Sometimes, if you have indexes defined on your SQL table, and if those index
names fall alphabetically before your primary key, Access may pick up one of
those instead, and, thus, prompt you for the key, since it picked up a
non-unique index. A way to resolve that is to rename your primary key to
have it start with "aaaa" or something, to make sure it's first.

Second, using FindFirst isslow; but it shouldn't be thatslow. I hadslow
results once by using a form that was saved as an unbound form, to which I
added a recordset after the form was opened. I found that saving the form as
a bound form (even bound to a zero-record recordset) made a HUGE difference,
over saving it as an unbound form and then adding a recordset on the fly. So
make sure that you save your form as a bound form, and not as an unbound
form.

Last, though you should be getting better performance even using FindFirst,
you might want to consider a different mechanism. Do you need 8500 records
in your continuous form?? Is the user going to scroll through 8500 records??
Consider which records the user actually wants/needs and abbreviate your
recordset to show those. It could be that they only need one at a time, in
which case you can just change the recordset to display that one record. Or,
if they need a set of records. But I don't think they'd need 8500 records in
a continuous form! Unless they're speed readers, that is... ;-)

HTH,

Neil

"eighthman11" <[e-mail] in message

news:10**********************************@o6g2000h sd.googlegroups.com...

using Access 2003 and sql server version 8.0

Hey everyone. Created a text box where the user types in an Inventory
number and it takes them to that inventory number on the contimuous
form. The form is based on a link table to sql server. Here is the
code:

Dim rst As DAO.Recordset
Dim InventoryItem As String
InventoryItem = "'" & "TextBoxValue" & "'"
Set rst = Me.RecordsetClone

rst.FindFirst "InventoryNumber = " & InventoryItem
If rst.NoMatch Then
MsgBox "Record not found"
Else
Me.Bookmark= rst.Bookmark
End If
rst.Close

Pretty simple code and it works, but it isslow,slow,slow. There is
only 8500 records on the continuous form. The closer the search
number is from the top the faster it goes. But it you go to a number
at the bottom it takes about 10 seconds.

When I linked by sql table (ODBC) I gave it a key but when I look at
the individual fields in design view for the table I can see the key
but no indexs on the fields. I have CREATE UNIQUE CLUSTERED INDEX
for the table on the sql server.

Is there anything I can do to make this "goto record" work faster.
Any help appreciated.

thanks ray- Hide quoted text -

- Show quoted text -

------------------------------------------------------------------------------------------------------------------------------------------------
Hey Neil:

You say I shouldn't have to give the SQL table a Key when I linked
it? In access when I did "New Table - LINK" I did an ODBC link to
my SQL Table. After selecting the table I got an Access screen that
had the heading "Select Unique Record Identifier" Which listed all
the field in my SQL Table and I checked the ones that made up the
key. At the bottom of the screen it stated "To ensure data integrity
and to update records you must choose a field or fields that uniquely
identify each record".

It never automatically sets up my key when I do an ODBC connection.
Am I doing something wrong?

Ray

[Neil]

Hey Neil:

>
You say I shouldn't have to give the SQL table a Key when I linked
it? In access when I did "New Table - LINK" I did an ODBC link to
my SQL Table. After selecting the table I got an Access screen that
had the heading "Select Unique Record Identifier" Which listed all
the field in my SQL Table and I checked the ones that made up the
key. At the bottom of the screen it stated "To ensure data integrity
and to update records you must choose a field or fields that uniquely
identify each record".

It never automatically sets up my key when I do an ODBC connection.
Am I doing something wrong?

Ray

Right, that's exactly what I'm saying. You should not be getting the Select
Unique Record Identifier prompt. The only time you get that is when Access
can't determine on it's own the SQL table primary key.

You need to go into SQL Server and assign a primary key to the table. Then
go back to Access, completely delete any link that might be there, and then
recreate the link. Then it will work.

Neil

[eighthman11]

On Nov 26, 11:44 am, "Neil" <[e-mail]:

Hey Neil:

You say I shouldn't have to give the SQL table a Key when I linked
it? In access when I did "New Table - LINK" I did an ODBC link to
my SQL Table. After selecting the table I got an Access screen that
had the heading "Select Unique Record Identifier" Which listed all
the field in my SQL Table and I checked the ones that made up the
key. At the bottom of the screen it stated "To ensure data integrity
and to update records you must choose a field or fields that uniquely
identify each record".

It never automatically sets up my key when I do an ODBC connection.
Am I doing something wrong?

Ray

Right, that's exactly what I'm saying. You should not be getting the Select
Unique Record Identifier prompt. The only time you get that is when Access
can't determine on it's own the SQL table primary key.

You need to go into SQL Server and assign a primary key to the table. Then
go back to Access, completely delete any link that might be there, and then
recreate the link. Then it will work.

Neil

------------------------------------------------------------
Hey Neil I am afraid I have misled you. My ODBC connection is to a
SQL View (Not a Table) Our network administrator does not allow us to
link to tables (only views). I created my own ODBC connection to link
to the table instead of the view and the bookmark worked noticably
quicker. Unfortunately I can not use the table and must use the view
for this application. Any ideas on making the view quicker. Thanks

[Neil]

"eighthman11" <rd******@nooter.comwrote in message
news:a6**********************************@x69g2000 hsx.googlegroups.com...

On Nov 26, 11:44 am, "Neil" <[e-mail]:

Hey Neil:

You say I shouldn't have to give the SQL table a Key when I linked
it? In access when I did "New Table - LINK" I did an ODBC link to
my SQL Table. After selecting the table I got an Access screen that
had the heading "Select Unique Record Identifier" Which listed all
the field in my SQL Table and I checked the ones that made up the
key. At the bottom of the screen it stated "To ensure data integrity
and to update records you must choose a field or fields that uniquely
identify each record".

It never automatically sets up my key when I do an ODBC connection.
Am I doing something wrong?

Ray

Right, that's exactly what I'm saying. You should not be getting the
Select
Unique Record Identifier prompt. The only time you get that is when
Access
can't determine on it's own the SQL table primary key.

You need to go into SQL Server and assign a primary key to the table.
Then
go back to Access, completely delete any link that might be there, and
then
recreate the link. Then it will work.

Neil

------------------------------------------------------------
Hey Neil I am afraid I have misled you. My ODBC connection is to a
SQL View (Not a Table) Our network administrator does not allow us to
link to tables (only views). I created my own ODBC connection to link
to the table instead of the view and the bookmark worked noticably
quicker. Unfortunately I can not use the table and must use the view
for this application. Any ideas on making the view quicker. Thanks

If you're using a view, then, yes, you'll have to set the unique index on
the Access end. Make sure that the fields you're identifying as the unique
index are indexed on the SQL end. If they're all in one table, then create a
unique index on those fields. Otherwise, index the fields individually.

You might want to post in CDMSqlserver re. this question. You'll definitely
get a more authoritative answer there. The issue you're dealing with is a
SQL Server/ODBC issue, not specifically an Access issue.

Neil

[lyle fairfield]

"Neil" <no****@nospam.netwrote in
news:RZ*****************@newssvr17.news.prodigy.ne t:

Make sure that the fields you're identifying as the unique
index are indexed on the SQL end.

I don't use MS-SQL VIEWS very much; regardless, they can be indexed. I'm
guessing Access Forms use VIEW indexes as they use TABLE indexes. But I
haven't tried that, at least not recently. VIEWS must be schema-bound to be
indexed.

Examples:

CREATE VIEW View_2
WITH SCHEMABINDING
AS
SELECT ID, SchoolName, Active
FROM Schools
CREATE UNIQUE CLUSTERED INDEX idx_View_2
ON View_2
(
ID ASC
)

--
lyle fairfield

[Neil]

"lyle fairfield" <ly******@yahoo.cawrote in message
news:kI*******************@read2.cgocable.net...

"Neil" <no****@nospam.netwrote in
news:RZ*****************@newssvr17.news.prodigy.ne t:

> Make sure that the fields you're identifying as the unique
index are indexed on the SQL end.

I don't use MS-SQL VIEWS very much; regardless, they can be indexed. I'm
guessing Access Forms use VIEW indexes as they use TABLE indexes. But I
haven't tried that, at least not recently. VIEWS must be schema-bound to
be
indexed.

Examples:

CREATE VIEW View_2
WITH SCHEMABINDING
AS
SELECT ID, SchoolName, Active
FROM Schools
CREATE UNIQUE CLUSTERED INDEX idx_View_2
ON View_2
(
ID ASC
)

--
lyle fairfield

I assume this is an option available in more recent versions of SQL Server?
I tried running it against SQL 7, and I got, "'SCHEMABINDING' is not a
recognized option."

[lyle fairfield]

"Neil" <no****@nospam.netwrote in
news:u_*****************@newssvr19.news.prodigy.ne t:

>
"lyle fairfield" <ly******@yahoo.cawrote in message
news:kI*******************@read2.cgocable.net...

>"Neil" <no****@nospam.netwrote in
news:RZ*****************@newssvr17.news.prodigy.n et:

>> Make sure that the fields you're identifying as the unique
index are indexed on the SQL end.

I don't use MS-SQL VIEWS very much; regardless, they can be indexed.
I'm guessing Access Forms use VIEW indexes as they use TABLE indexes.
But I haven't tried that, at least not recently. VIEWS must be
schema-bound to be
indexed.

Examples:

CREATE VIEW View_2
WITH SCHEMABINDING
AS
SELECT ID, SchoolName, Active
FROM Schools
CREATE UNIQUE CLUSTERED INDEX idx_View_2
ON View_2
(
ID ASC
)

--
lyle fairfield

I assume this is an option available in more recent versions of SQL
Server? I tried running it against SQL 7, and I got, "'SCHEMABINDING'
is not a recognized option."

It may be the edition.Indexed views require the enterprise edition in MS-
SQL 2000 ... maybe.

[Neil]

"lyle fairfield" <ly******@yahoo.cawrote in message
news:3%*****************@read1.cgocable.net...

"Neil" <no****@nospam.netwrote in
news:u_*****************@newssvr19.news.prodigy.ne t:

>>
"lyle fairfield" <ly******@yahoo.cawrote in message
news:kI*******************@read2.cgocable.net.. .

>>"Neil" <no****@nospam.netwrote in
news:RZ*****************@newssvr17.news.prodigy. net:

Make sure that the fields you're identifying as the unique
index are indexed on the SQL end.

I don't use MS-SQL VIEWS very much; regardless, they can be indexed.
I'm guessing Access Forms use VIEW indexes as they use TABLE indexes.
But I haven't tried that, at least not recently. VIEWS must be
schema-bound to be
indexed.

Examples:

CREATE VIEW View_2
WITH SCHEMABINDING
AS
SELECT ID, SchoolName, Active
FROM Schools
CREATE UNIQUE CLUSTERED INDEX idx_View_2
ON View_2
(
ID ASC
)

--
lyle fairfield

I assume this is an option available in more recent versions of SQL
Server? I tried running it against SQL 7, and I got, "'SCHEMABINDING'
is not a recognized option."

It may be the edition.Indexed views require the enterprise edition in MS-
SQL 2000 ... maybe.

Maybe 2000 or maybe Enterprise Edition? My understanding was that the
desktop version had all the same functionality as the enterprise version,
except it was limited in capacity. Otherwise, if it didn't, it would be
pretty worthless as a development tool.

[lyle fairfield]

"Neil" <no****@nospam.netwrote in
news:rm**************@newssvr22.news.prodigy.net:

Maybe 2000 or maybe Enterprise Edition? My understanding was that the
desktop version had all the same functionality as the enterprise
version, except it was limited in capacity. Otherwise, if it didn't,
it would be pretty worthless as a development tool.

I think there are differences beyond capacity. We can see a list for MS-SQL
Server 2005 at:

http://www.microsoft.com/sql/prodinf...-features.mspx

I don't know of a smiliar list for MS-SQL Server 7, or MS-SQL Server 2000,
but someone may have them squirreled in his or her archives.

I guess someone with a lot more patience than I have might find something
by starting at:

http://support.microsoft.com/selectindex/default.aspx?
target=search&sreg=en-us&adv=1

[Neil]

"lyle fairfield" <ly******@yahoo.cawrote in message
news:Vm*******************@read2.cgocable.net...

"Neil" <no****@nospam.netwrote in
news:rm**************@newssvr22.news.prodigy.net:

>Maybe 2000 or maybe Enterprise Edition? My understanding was that the
desktop version had all the same functionality as the enterprise
version, except it was limited in capacity. Otherwise, if it didn't,
it would be pretty worthless as a development tool.

I think there are differences beyond capacity. We can see a list for
MS-SQL
Server 2005 at:

http://www.microsoft.com/sql/prodinf...-features.mspx

I don't know of a smiliar list for MS-SQL Server 7, or MS-SQL Server 2000,
but someone may have them squirreled in his or her archives.

I guess someone with a lot more patience than I have might find something
by starting at:

http://support.microsoft.com/selectindex/default.aspx?
target=search&sreg=en-us&adv=1

I see what you mean. Still, there don't seem to be any differences between
versions that deal with any SQL that one would write, or any objects that
one would create. Re. indexed views, it does state: "Indexed view creation
is supported in all editions. Indexed view matching by the query processor
is supported only in Enterprise Edition." So one should be able to create
indexed views in all editions. So it must be a SQL 7 thing (or "lack of
thing," rather).

[eighthman11]

On Nov 27, 12:49 pm, "Neil" <[e-mail]:

"lyle fairfield" <[e-mail] in message

news:Vm*******************@read2.cgocable.net...

"Neil" <[e-mail] in
news:rm**************@newssvr22.news.prodigy.net:

Maybe 2000 or maybe Enterprise Edition? My understanding was that the
desktop version had all the same functionality as the enterprise
version, except it was limited in capacity. Otherwise, if it didn't,
it would be pretty worthless as a development tool.

I think there are differences beyond capacity. We can see a list for
MS-SQL
Server 2005 at:

http://www.microsoft.com/sql/prodinf...-features.mspx

I don't know of a smiliar list for MS-SQL Server 7, or MS-SQL Server 2000,
but someone may have them squirreled in his or her archives.

I guess someone with a lot more patience than I have might find something
by starting at:

http://support.microsoft.com/selectindex/default.aspx?
target=search&sreg=en-us&adv=1

I see what you mean. Still, there don't seem to be any differences between
versions that deal with any SQL that one would write, or any objects that
one would create. Re. indexed views, it does state: "Indexed view creation
is supported in all editions. Indexed view matching by the query processor
is supported only in Enterprise Edition." So one should be able to create
indexed views in all editions. So it must be a SQL 7 thing (or "lack of
thing," rather).- Hide quoted text -

- Show quoted text -

Hey guys. I already tried the SCHEMABINDING but it caused me all
kinds of problems. Changed fields on me; many of my Stored Procedure
would no longer work. I finally ended up deleting my Scema Bound View
and the original Table and the triggers and indexes and then recreated
them to get back were I started. I got all kinds of error when trying
to create the schemabinding "Quote Identifier OFF" Quote Identifier
ON, Ansi nulls OFF Ansi nulls ON etc. With my level of expertise I
might be better off not missing around with the schemabinding .

I just wanted to say though thanks for the help. I got a lot of great
ideas. On the book mark goto, I got it to work faster by cutting the
file in half. Lets say there are 10,000 items with item numbers 1
thru 10,000. If the item you are looking for in 5000 or less I do a
findfirst if it is greater than 5000 I do a FindLast. This has
dramatically increased the speed. Once again thanks for the help.

[Neil]

I just wanted to say though thanks for the help. I got a lot of great

ideas. On the book mark goto, I got it to work faster by cutting the
file in half. Lets say there are 10,000 items with item numbers 1
thru 10,000. If the item you are looking for in 5000 or less I do a
findfirst if it is greater than 5000 I do a FindLast. This has
dramatically increased the speed. Once again thanks for the help.

Oh yeah, that's right. That's what we were talking about in the first place.
:-)

That's a good idea, using FindLast. Never thought of that. Still, I have to
wonder if you can cut down your recordset entirely and just use smaller
sets. Anyway, glad you got some improvement.

Neil