bookmark continuous form slow slow slow

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

Re: bookmark continuous form slow slow slow

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" wrote in message
news:1027e17d-7f29-419c-ab0b-50729f8bdd01@o6g2000hsd.googleg roups.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
>
>
>

Re: bookmark continuous form slow slow slow

On Nov 21, 5:30 pm, "Neil" wrote:
> 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" wrote in message
>
> news:1027e17d-7f29-419c-ab0b-50729f8bdd01@o6g2000hsd.googleg roups.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

Re: bookmark continuous form slow slow slow

> 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

Re: bookmark continuous form slow slow slow

On Nov 26, 11:44 am, "Neil" wrote:
> > 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

Re: bookmark continuous form slow slow slow

"eighthman11" wrote in message
news:a6868c92-e152-4d03-8ecc-42d96a983697@x69g2000hsx.google groups.com...
> On Nov 26, 11:44 am, "Neil" wrote:
>> > 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

Re: bookmark continuous form slow slow slow

"Neil" wrote in
news:RZU2j.2344$C24.2039@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

Re: bookmark continuous form slow slow slow

"lyle fairfield" wrote in message
news:kIV2j.16613$xa2.11910@read2.cgocable.net...
> "Neil" wrote in
> news:RZU2j.2344$C24.2039@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."

Re: bookmark continuous form slow slow slow

"Neil" wrote in
news:u_W2j.2367$Dt4.1672@newssvr19.news.prodigy.net:

>
> "lyle fairfield" wrote in message
> news:kIV2j.16613$xa2.11910@read2.cgocable.net...
>> "Neil" wrote in
>> news:RZU2j.2344$C24.2039@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.

Re: bookmark continuous form slow slow slow

"lyle fairfield" wrote in message
news:3%X2j.16198$9F1.839@read1.cgocable.net...
> "Neil" wrote in
> news:u_W2j.2367$Dt4.1672@newssvr19.news.prodigy.net:
>
>>
>> "lyle fairfield" wrote in message
>> news:kIV2j.16613$xa2.11910@read2.cgocable.net...
>>> "Neil" wrote in
>>> news:RZU2j.2344$C24.2039@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.

Re: bookmark continuous form slow slow slow

"Neil" wrote in
news:rmY2j.3278$fl7.1@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/prodinfo/features/compare-featu res.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

Re: bookmark continuous form slow slow slow

"lyle fairfield" wrote in message
news:VmZ2j.16664$xa2.16416@read2.cgocable.net...
> "Neil" wrote in
> news:rmY2j.3278$fl7.1@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/prodinfo/features/compare-featu res.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).

Re: bookmark continuous form slow slow slow

On Nov 27, 12:49 pm, "Neil" wrote:
> "lyle fairfield" wrote in message
>
> news:VmZ2j.16664$xa2.16416@read2.cgocable.net...
>
>
>
>
>
> > "Neil" wrote in
> >news:rmY2j.3278$fl7.1@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/prodinfo/features/compare-feat ures.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.

Re: bookmark continuous form slow slow slow

> 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