Linked Tables Vs Stored Procedures

[UHNSTrust]

I am upsizing my Access Db to SQL server.

To make sure I am getting the best performance I want to know whether it is better to have forms (read only) bound to linked sql tables (ODBC) or if to have them based on a Pass Through Query (set to return records) running a stored procedure.

The same applies to reports. Should they be based on stored procedures or access queries on linked sql tables?

Any advice/recommendation is very welcome.

Jonathan

 

[jsteph]

In my opinion, I always use unbound forms. When using Oracle, SQL-Server, or any other db with Access (or VB) as a front end, my very basic archtecture is this:

For browse screens, fill a grid via a readonly, ,forwardonly recordset. In Access, you'd use a continuous form based on a passthru.

When a record is selected from the browse screen, load that data to a single-record form with a new fetch on a separate rs for that single record.

When the record is updated (or new added) do it via SP or raw sql...never updated via a recordset.

For locking/contention, there are many ways you could go but they include using the timestamp datatype field, comparing field-by-field the original fetch with a new fetch after locking the record prior to update, etc etc.

Basically, what I'm getting at is that you should never use bound forms (in Access) or the data control (in vb). It may be extra coding, but you'll have more control and better performance.
--Jim

 

[Alito]

Indexes do not work with linked tables, any ideas?

 

[jsteph]

<<Indexes do not work with linked tables, any ideas?>>

What do you mean by this? Indexes work with ISAM or ODBC linked tables just fine.
--Jim

 

[Alito]

When I try to use indexes with linked tables, I get an error
massage "this is operation is not supported", I do not know anything about ISAM or ODBC, I am simply linking tables across a windows 2000 network. Can you help?

Thanks

Ali Gavary

 

[Alito]

Can someone help me with indexing Linked Tables across
a LAN in windows 2000?

This is a sample of code I use in my app.

Set rstA = db.OpenRecordset("All_Orders")
rstA.Index = "Primary"

"All_Orders" is a Linked table across the windows 2000 network. When I try to run the code I get the error message
"Operation is not supported for this type of object" with index line highlited.

Can someone help?
Is there any other way to do this?
Yes, I do need the index to do a search.

Thanks
Ali Gavary

 

[RoyVidar]

To set/have indexes on linked tables do work, and tends to speed up retrieval of information. But you are talking about using the .Index property of a DAO recordset Object. Using the .Seek method utilizing the .index property, I think is designed to work on native (not linked) tables (though thru some tweaks you might get it to work on linked tables too). I think if you open the other database before creating a recordset, see the samples in the help files on the .Index property/Seek method, it might work.

Else, just use the .FindFirst method in stead.

Roy-Vidar