Opening a recordset to a linked table (Access 2000)

[EvanScamman]

I have been using the following code to open a recordset and modify the data in a linked table (split database, with back end and front end components).

I discovered that the code I am using causes the database to be very slow updating the data.
For example, i have several records listed in a list box. If i use my code to change or remove a record, then I tell the listbox to requery (listbox.requery), the changes don't show up. I have to manually requery (F9 key) several times until the data actually refreshes.

I know there must be another way to access the recordset that allows fast refreshing of data.
Please point me in the right direction, or if anybody has any sample code, I wolud really appreciate it.

Originally I used the plain recordset method, but this does not work across a linked table.

Thanks, and here's my code:

-----------------------

Dim cn As ADODB.Connection
Dim recOrderRequest As ADODB.Recordset

Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "c:databasesupplies data.mdb"
.Open
End With

Set recOrderRequest = New ADODB.Recordset
With recOrderRequest
.ActiveConnection = cn
.Source = "tblOrderRequest"
.CursorLocation = adUseServer
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open Options:=adCmdTableDirect

[charlotte]

Is your front end in Access or VB? What are you using the recordset for? If it's for updating records in linked tables, an update query would probably be much faster, even if there's only one record being updated. If all you're doing is populating a listbox, how many records are you returning to the listbox? How are you actually populating the listbox? If you use code to change the underlying table values, you won't see any difference in your listbox unless the listbox is based on a query. If you populated it in code the way it's normally done in VB and other Office apps, you'll have to rerun the routine to populate it again, because requery won't do it. I assume this is *not* the way you're populating it, because you say it does requery after several attempts.

There isn't any point in trying to use a server-side cursor with the Jet 4.0 provider because the Jet database engine is not a database server. For that, you need SQL Server or some other database server provider. With Jet, you might as well use the client-side cursor because that's what you get anyhow.

<hr>Originally I used the plain recordset method, but this does not work across a linked table<hr>

I don't understand what you mean by this. I open recordsets on linked tables all the time using adCmdTable instead of adCmdTableDirect. Have you tried that?

These should normally be pretty fast operations, but there are some other things that can slow them down. The one I hate the most is subdatasheets. They're turned on by default in Access 2000, and you have to turn them off on each individual table. If you used any lookups in your table designs, the subdatasheets can cause a significant slowdown just in loading linked tables, so I'd look there first. There is code in on-line help to turn off subdatasheets, but you'll have to run it on your back end database as well as the front end. Try that and see if it makes a difference.

[EvanScamman]

Charlotte, thanks for your reply. I'm afraid much of what you said is over my head. What do you mean about using the server-side cursor? i didn't even mean to use it, and i don't know what it is.

My front end is in Access. In my form I have 2 list boxes, one lists orders that are waiting to be processed, the other lists orders that have been processed. I use the recorset to open the table, modify data in the underlying tables, and then mark the order as processed. At this point, both list boxes are supposed to requery, and the particular order is supposed to move from one list box to the other. This is where the database bogs down - the order doesn't move at first. I have to refresh several times before it does.

I am populating the list box by a query.

Can you suggest another way to open the recordset using a linked table. If you can please point me to a reference that contains the actual code. I don't really understand all the concepts and I usually write code by copying and pasting.

Thankyou,
Evan

[charlotte]

<hr>What do you mean about using the server-side cursor? <hr>

That's what you're requesting when you use this:

.CursorLocation = adUseServer

If your backend were SQL Server, this would tell ADO to execute the cursor on the server rather than in the front end. Since your backend is Jet, this is meaningless and you might as well use adUseClient.

<hr>Can you suggest another way to open the recordset using a linked table<hr>

I'm not sure what you're trying to do. If you're using an unbound form, then you should be able use an ADO recordset without any problems, although performance may be impaired if the recordset is very large.

It sounds like you're trying to create something usually referred to as a pick list, where you select items from one listbox and move them to another listbox, removing them from the first. If that's the case, you don't need to do anything with the recordset until you've made all the changes you require, and then you can run a single routine to update the records in the second listbox to the underlying table. Just in case that's what you're trying to do, I'm going to attach an Access 2000 sample of a pick list form. There's no recordset manipulation going on here, just moving the items back and forth between the lists to show you how it can be done.

[EvanScamman]

Charlotte, the pick list may be exactly what I'm trying to do. I haven't had a chance to think through everything yet, but it seems like it might be exactly what I need. Thanks so much for sending over the code. i really appreciate it. BTW, our server does have SQL installed on it, I have just never taken advantage of it because I don't know how to use it. can you point me to any references that explain how to jump from Access back-end to SQL server? Thanks so much!

[charlotte]

There's an upsizing wizard built into Access 2000 and the SQL Server technical notes has information on migrating an Access database to SQL Server as well. There are several white papers available on the Microsoft site, so you can search through <A target="_blank" HREF=http://msdn.microsoft.com/library/default.asp?url=/nhp/default.asp>MSDN online</A> to find them. Plus, I'd recommend a Lounge search on upsize upsizing to find previous threads on this topic.

[EvanScamman]

I'm trying to implement the pick list you sent me in my database. The problem I'm having is I'm using a list box with about 8 columns of data. So It appears that populating the list box by editing the value list property is not a realistic way to do this. Is there a way to use the pick list to determine which records show up in the list box, but then get the rest of the data from the query?

Thank you,
Evan

[charlotte]

The demo picklist just shows the basics of a simple, unbound picklist. In your case, base your listboxes on queries instead of a values list. For example, if the list in the first listbox is based on table1, you could use an outer join to table 2 and put Is Null in the criteria for the linking field in table 2. In the second listbox, you'd base your list on those items in table 2. After each selection from the first listbox and whatever code you would use to append a record to table 2, you would requery both listboxes

Does that help?