MS SQL Conversion and Deployment

[ssteinke]

Hi guys, I hope somebody can give me a quick step-by-step.

I've nearly completed a program written in Access and have started to venture into marketing the program to a local company. When asked what other modifications they would like to see i was sent an email stating;

"A major item would be to deploy in MS SQL. You should be able to do that free of charge with the low tier version of SQL."

I had a feeling this would come up, but I don't want to sound like a total idot when talking to these folks... I really have no true understanding of what they are asking, but I suspect it refers to conversion from jet. Question: Is this a reasonably easy modification? What do I need to purchase? Would I be better off telling them the program is 'as-is'? Can this be easily done by someone who has never done it before? I'm sure other threads addressed this issued but I would truly appreciate a full understanding of my specific problem. Thanks, Scott.

 

[Pat Hartman]

If you have created the Access application with client/server in mind, converting the be to SQL Server will take less than an hour since all you would need to do would be to upsize the tables with the upsizing wizards.

You can read the articles on client/server optimization in the KB or MSDN library and they will help you determine what you would need to change in the application to make it work effectively with an ODBC back end.

 

[shadow9449]

...converting the be to SQL Server will take less than an hour since all you would need to do would be to upsize the tables with the upsizing wizards.

What about local queries? Don't they have to be transferred to stored procedures to get the benefits of client/server?

I assume that when you say "created with client/server in mind", you are referring to the forms and reports, that they are unbound or based on very limiting queries.

SHADOW

 

[ssteinke]

"Created with client/server in mind" brings me to another question, are their some major DO's and DONT's when programming with the intent on upsizing later? Specifically, my program is split, most forms are unbound using DAO to populate forms, with the exception of listboxes and continuous subforms.

Are their some common programming mistakes people can avoid or that may simplify the upsizing process with less headache?

 

[Banana]

ssteinke, I'm in the same shoes as you are. I already read the kb articles.

The only one major thing that stood out to me was about how Jet and SQL differ in binding. With Jet, Autonumber is assigned as soon a new entry is made, and you can use that to set subforms or other related records. However, with SQL, IDENTITY is assigned only when the record is saved. Therefore anything that needs to have same ID for related records has to be moved to after saving the record.

That said, I can't help but feel there's more to the pictures.

 

[Pat Hartman]

What about local queries? Don't they have to be transferred to stored procedures to get the benefits of client/server?

No. This is a common misconception. Jet is really very smart. It attempts to "pass through" ALL queries. The client/server optimization articles will tell you the things that prevent Jet from doing this. The primary problem is using VBA or user-defined functions in the Where clause. These cannot be translated by the ODBC driver to T-SQL and so only the Select clause will be sent to the server. Jet will apply the criteria locally. So as long as you are careful with your queries, Jet will automatically pass them directly through to the server for processing and only the requested records will be returned.

When I say created with client/server in mind, I mean that ALL forms use queries with Where clauses to limit the number of records returned from the server. It does you no good to upsize if you bind your forms to a table or to a query with no selection criteria. These forms will simply open a pipeline to the database and keep sucking data over the network until the entire table has been transferred to the local PC. Using the Where argument of the OpenForm or OpenReport Methods serves the same function as a Where clause that is embedded in the query.

 

[Banana]

but what about functions such as Nz and IIF? I read somewhere that they are not necessarily supported?

 

[Banana]

Funny,

I was just reading Alison Balter's Mastering Access 2002 about developing/distributing Access.

It says if Access is to be used as a front end, all queries must be pass-through, and all forms must be unbound.

Furthermore, it even says that using Access as a pure front-end may not as effective as developing an application in VB.

Is that accurate?

 

[Pat Hartman]

In my experience, Access works fine as a front end for Oracle, DB2, and SQL Server databases. I ALWAYS use bound forms and I NEVER use pass-through queries. As I said earlier, Jet makes every effort to pass through EVERY query. It just doesn't always succeed so you need to understand what causes problems. All you need to do is to use the SQL Profiler to see for yourself what Jet passes to the server. You can compare it to what gets passed for pass-through queries.

In one case I needed to create a stored procedure because of complications in the Where clause and in one case I needed to create a pass-through query when I had to delete the contents of a table. This is the only the only type of query that Jet can't efficiently process and that is because Jet gives you the option of cancelling the delete so it has to do extra work when the be is ODBC to make that happen. This doesn't cause me a problem since I almost never use temp tables.

You can use any function you want in the Select clause. If Jet can't translate it to T-SQL, it will be applied when the recordset is returned from the server. You can use any function you want in the Where clause. The problem is that if the function is not supported by T-SQL, Jet will strip the where clause and send only the Select clause to the server. It will apply the Where clause locally. This isn't a show stopper, it simply means that more data than necessary is returned from the server.

If you have SQL Server installed, you can view the SQL sent to the server by Access by using the Profiler tool.

 

[shadow9449]

So as long as you are careful with your queries, Jet will automatically pass them directly through to the server for processing and only the requested records will be returned.

This is VERY interesting info, Pat. It's contrary to what I've heard from Access to SQL Server upscaling "gurus". I'm glad that it's not as bad a task as they make it sound.

Thanks, Pat.

SHADOW

 

[Pat Hartman]

If you compare the SQL for a pass-through query with that of a jet query with the recordset property set to Snapshot, you will see that the query sent to the server is identical. Updatable queries have different rules that is why you need to be extra careful to limit the number of records that the form will request from the server and you can do that either with the Where clause of the query or with the Where argument of the OpenForm and OpenReport methods.

You don't even need to take my word for it. Use the profiler and see for yourself. Then tell those gurus what you found.

I'm not saying that there is no overhead associated with letting Jet process the queries before it sends them to the server. Of course there is but it is not significant. You can also minimize that by using stored querydefs. When a querydef is saved, Jet prepares an access plan and saves it. This would include determining what it will "pass-through" and what it won't when the tables are ODBC.