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

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" <rdshultz (AT) nooter (DOT) com> wrote

On Nov 21, 5:30 pm, "Neil" <nos... (AT) nospam (DOT) net> 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

On Nov 26, 11:44 am, "Neil" <nos... (AT) nospam (DOT) net> wrote:
------------------------------------------------------------
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

"eighthman11" <rdshultz (AT) nooter (DOT) com> wrote


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

"Neil" <nospam (AT) nospam (DOT) net> wrote in
news:RZU2j.2344$C24.2039 (AT) newssvr17 (DOT) news.prodigy.net:

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

"lyle fairfield" <lylefair (AT) yahoo (DOT) ca> wrote

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."

"Neil" <nospam (AT) nospam (DOT) net> wrote in
news:u_W2j.2367$Dt4.1672 (AT) newssvr19 (DOT) news.prodigy.net:

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

"lyle fairfield" <lylefair (AT) yahoo (DOT) ca> wrote

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.