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@nooter.comwrote in message
news:1027e17d-7f29-419c-ab0b-50729f8bdd01@o6g2000hsd.googlegroups.com...
Quote:
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
>
>
>
