Question Data going to SQL Server Express- can still keep forms editable? (1 Viewer)

[Margarita]

Hello,
We want to migrate the data tables only to SQL SE but keep front end the same, which I understand is not a overly complicated thing to do in itself. However, to make it efficient and not run into a lot of network-caused slowness, I understand that the queries that are needed for reports and for producing read-only datasets have to be re-written as pass-through in the SQL that SQL Server understands. Fine, we can make that happen (I would get help from someone who can do this re-writing).
But what will happen to the bound forms which the users are using to edit data in the tables? All those forms are bound to editable query sets and that's how the users interact with the tables. Could the queries behind those forms stay the same- meaning in Jet sql? And if they do stay the same, will they be really, really slow and would the userhave to sit there forever and ever for each edit they make to a record to be saved?

Also, I have a number vba procedures in this database that use sql statements created directly in the code. Those statements are used by two kinds of vba procedures:
-snippets of code in forms' on load events that set the row source of some of the forms' controls
-larger code procedures that take datasets from stored queries as well as sql strings defined directly in the vba code and paste it into excel reports.

Would it be possible and advisable to leave the sql strings in vba as they are- written in Jet SQL? Or does everything have to be re-written for SQL Server?

Both parts of this question are important points for me, but if you have any advice on either one of them, I would be very grateful.
Thank you.

 

[pbaldy]

As a general rule, you can link to the SS tables in Access and forms can continue to be bound to them, Jet/VBA queries will run against them, etc. Network slowness is more about design than what back end is used, meaning to restrict data coming across the wire to the necessary record(s). You could certainly migrate the tables to SS, link to them and leave everything as is, and then address any performance issues that arise.

 

[Margarita]

As a general rule, you can link to the SS tables in Access and forms can continue to be bound to them, Jet/VBA queries will run against them, etc. Network slowness is more about design than what back end is used, meaning to restrict data coming across the wire to the necessary record(s). You could certainly migrate the tables to SS, link to them and leave everything as is, and then address any performance issues that arise.

Hi Paul, thanks for your reply. My gut instinct was to follow just what you outlined- to leave everything as-is at first and hope for the best.
I am mostly concerned with the editable forms continuing to function as they currently do without extra time lag. Let's hope they do.

Migration happening this week. Very excited and scared! Woohoo!
Thanks!

 

[pbaldy]

No problem, post back if you have problems.

 

[Pat Hartman]

I understand that the queries that are needed for reports and for producing read-only datasets have to be re-written as pass-through in the SQL that SQL Server understands

A pass-through query will in most cases be insignificantly faster and the only reason it is faster is because there is less chatter between Access and the server when running a pass-through query. What too many people don't understand is that Access attempts to "pass-through" ALL queries. You can of course defeat this so you need to understand a little of what prevents Access from passing through the query. There are several excellent articles floating about that discuss client/server optimization that would be useful for you to read.

The biggest change to applications that were developed without thought of upsizing is that forms tend to be bound directly to tables or to queries without any selection criteria. Running a query with no criteria against a SQL Server table of any size will almost always be SLOWER than running the same query against your Jet/ACE BE, especially if the Jet/ACE BE is local. In order to take advantage of SQL server it is imperative that you restrict your queries to reduce the number of records they return. Because of the way Jet/ACE work, a great deal of data is read from the BE and processed locally but with SQL server, the query is passed to the server and the server finds the requested records and returns only those. If your tables are under 10,000 rows, you probably won't see any real difference but as they grow, efficient queries will become more and more important.

The reason that IT hates Access with a purple passion (ok, it's just one of the reasons) is because an unknowing programmer can create applications with forms bound to tables or unqualified queries and they just open up and sit there sucking rows down from the server like a vampire and they will keep sucking until all the data is local. You don't see this because Access opens the form as soon as it has a few records to populate its recordset but the DBA and network admin see all the network traffice and will build voodoo dolls that look like you and stick pins in them