Moving access backend to SQL Server speed of Paramatized ODBC query 1

[juddymar58]

Hi, I'm currently investigating moving one of our Access Databases to SQL Server. Originally I was looking using stored procedures to populate unbound forms as well as update the data when a save button is clicked. This sounds nice and clean however there are a couple of forms in the project which contain many subforms as well as alot of bound controls.
I was originally going to use the method these but have been considering running a parametrized query on an odbc linked table to return only one record at a time based on the users selection. Doing this will mean that I can still use a bound form, meaning there is far less work involved.
I'm concerned about the speed of the form, I have heard that jet will try to process the whole table even if the recordsource is filtered to return only one record. Is this true? The form is based on a table with about 600,000 records so I would like to keep things reasonably fast. I'm just wondering what peoples thoughts are on using this approach?

Thanks
Justin

 

[kjv1611]

I think what you really need to be after with this solution is this sort of setup (my opinion):
[ol][li]Use Passthroug Queries, not Imported Tables if at all possible. Easy way:[ol][li]Link in a table from the database you are linking from.[/li]
[li]Create a query in design view, but don't select tables.[/li]
[li]Change the query type to Pass-Through - really easy in 2010 - itll be a button, basically, on the Ribbon under Query Tools - Design[/li]
[li]Right-click your linked table, select Design View... Answer Yes to the "do you want to open it anyway?" question.[/li]
[li]In the Properties Sheet, find Description, select that entire string from the field, and copy it.[/li]
[li]Now, you can close the linked table..[/li]
[li]Go to your new query you created, in design view, and paste to the field, ODBC Connect Str[/li]
[li]In the same field, go to the end of the connection string, and delete the table reference that is there, as you only need a reference to the server and database not table.[/li]
[li]Type or paste in your SQL code as if you were working directly on the SQL Server via SQL Server Management Studio[/li]
[li]Save the query - test if you want, and already have a working query in it... you have to have some working SQL code in the query in order to save it... so if you're not ready, just type something like SELECT TOP 10 * FROM MyTable[/li]
[/ol][/li]
[li]Use VBA to build the dynamic SQL for grabbing your one record each time[/li]
[li]Build a static Append or Make-Table query that grabs the results from your above query, and appends to an internal "temp" or "current" table from which the form will be based [/li]
[li]Build passthrough query (will have to label it as "does not return records" by simply copying the first passthrough query, renaming, and changing the returns records option.. and make sure it contains some Action SQL, not just SELECT[/li]
[li]Use your VBA to send the updated record via the action passthrough query back to your SQL Server Table[/li]
[/ol]

Your PassThrough Update Query will be run from your Save Button. And you may want to build in some error checking for such cases as:
1. The SELECT query doesn't return a record at all.
2. Either Query times out, or comes back with an error that may point to there being a problem at the server level...
3. And keeping #2 in mind, you may want some code to always check for a previously existing record in your internal "temp" or "current" table to be sure it does not overwrite the record unless it has been updated to the server... so maybe so long as the record matches the server record, I suppose.

Anyway... that might be how I would go about it. I helped another group set up something similar to that a few months ago here where I work.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[juddymar58]

Thanks for the reply kjv1611.
I'm actually going through the process now of using a temp frontend table which gets updated with the record selected via a ado connection (will most likely change to a pass through query).
With the frontend table I have one record which I set to null then update it with the values from the ado connection instead of deleting and adding a new record (avoids bloat with access). My only issue now is writing a stored procedure to handle the record locking which I'm about to start a new thread on.
Once I get one form up and running I will post how I have done it, as i'm sure it might be helpful for other newbies like myself. It will also give other people a chance to tell me what I could have done differently to improve the performance.
Thanks
Justin