Access behavior with SQL Backend

[Banana]

I'm currently researching on how to best migrate from Access back-end to a SQL backend, and was able to find wealth of information on how well Access can play with various SQL servers, how ODBC drivers are implemented, and how JET handles queries to backend. If this helps matter, I'm considering using MySQL as a backend.

That said, there are some gaps in my research that I'm hope someone will be able to fill in for me.

1) I have couple of queries that may use Access's custom function not otherwise supported in SQL servers. If I based the query on otherwise executable query, would JET be able to pass the sub query to SQL, and get the recordset and apply the next query using custom functions without any problems?

2) In general, DAO is best when you're using JET, but ADO is best for ODBC. I'm not clear whether it's possible to use a mixture of ADO and DAO, provided I've disambiguated the library (which I already did anyway) to make even efficient use of recordset. For example, I'd use ADO to work with data from backend and once it's bought over here, switch to DAO for faster handling by JET. Is that possible *and* advisable?

3) I found lists about limitations or problems that may arise due to differences between Access and SQL, which is good to know. However, I never found any answer as whether VBA may cause problems with using SQL data. Let's say I have a form that has lot of VBA coding to handle calculations, multiple selection in listbox to be added to a junction table, dynamically changing rowsource for a combobox, whatever that is based on a query that otherwise runs fine with SQL server- will the form still work just as fine or are there going to be any surprises after migrations?

4) Somehow related, if this is supported by whatever SQL server I may use, would I be better advised to use stored procedures in place of VBA for Access forms?

Thanks for your feedback.

 

[Shep]

I won't address your questions specifically because I have not upsized a backend to SQL and rewritten the frontend to work with the backend. Also, I don't fully understand a couple of them. For example, I don't know how you intend to get data from the server, then manipulate it with DAO. Regardless, I have no knowledge how efficient it would be. Unless your application is a good candidate for upsizing, you'll gain no benefit by doing so.

And, less than 1% of Access applications (BE or FE) are candidates for upsizing to SQL, according to the presenter in a Microsoft webcast I viewed. I tried to find it again to give you a link, but was not able to. Suffice to say this guy was a top-notch SQL pro, and he had charts showing which conditions might exist which would make your application a good candidate for upsizing. Very, very few are, particularly frontends.

I have, however, upsized backend data. It was a 75 table database normalized to third normal form, and it went without a hitch. Purely for academic purposes, I also allowed the upsizing wizard to establish links to all the backend tables. The application ran well and quickly. I ran the wizard again and created an .adp application. It looked horrible.

As for my frontend, I rewrote it. In Visual Basic 2005. The server I upsized to SQL was the Express (Advanced) Edition (free of charge from Microsoft). Five to ten hours of training (again, all free, from Microsoft) and you'll be amazed how quickly you can recreate your application. The data connection tools in VS are no less than incredible. And, unless your application is multi-user and being hammered by over, say, 50 users, there is no need to even use stored procedures. It would depend somewhat on exactly why you use them. At any rate, the resulting application runs like a top - whether connected to a local SQL database, to one on a server on the LAN, or via TCP/IP over the Internet.

Main point: You probably have every reason not to upsize, and slim to none to do so.

P.S. Visual Studio 2005 Express Editions are also available, and free of charge. You won't be able to connect to data on a server, however. Just thought I'd throw that in because you can develop with it now in conjunction with the Express edition of SQL Server and later take the project into one of the non-Express version of VS.

 

[pbaldy]

1) Jet is pretty good about passing what it can to the back end to get a restricted recordset back to work with further. You may find that converting queries to SP's and using SQL Server functions results in faster execution though.

2) As long as you disambiguate, you'll have no trouble using both. DAO will work fine with SQL Server by the way, it's just that ADO is better optimized for it. I wouldn't even bother changing existing stuff to ADO after you upsize. Most consider DAO to be more feature-rich anyway.

3) I'm not really clear on what you're asking here. Access works nicely with SQL Server. Most of what I create is MDB front ends with SQL Server back ends.

4) I use stored procedures for many queries, processes, etc, but most forms are bound to tables (or queries). I usually open forms in either data entry mode or with a wherecondition, so I'm not pulling all the records over the wire. While I do have some complicated SP's, I also use VBA extensively behind forms.

Like Shep, I don't have a lot of use for ADP's. After playing with them a bit, I've never considered using one for a production application. Microsoft itself has moved away from them, so I wouldn't bother with one.

 

[Banana]

Shep, I'm a bit confused when you talk about upsizing a front end application. It seems a bit nonsensical, since upsizing is specifically for back end, and has nothing to do with front end, correct?

That said, I'd love to see that information myself. My reason for wanting to upsize is threefold: 1) We have satellite offices and we need a way to effectively centralize the information, 2) we only have a dedicated server in our main office, where there's reliable backup hardware. The back end should be there. 3) Reliable connection, especially across VPN.

That said, I appreciate you sharing your experience and insights. It's good to know that it worked out well for you.

pbaldy-

1) Right. That's good in two ways- I get it up and running sooner and optimize as I go than having to rewrite everything to get my queries working with SQL and if for whatever reason Access has a function not otherwise supported by SQL backend, then I can just pulled the limited recordset and let JET handle it. That's good to know.

2) Agreed. I should point out that we're considering MySQL as a backend, which is why I am thinking about using ADO and assumed that MySQL doesn't support DAO (but I don't know for sure... hmm should check that myself), but wondered what if I wanted JET to handle some local data which DAO would be faster.

3) Sorry. To put this in two sentences: Is there anything at all within VBA modules that may cause problem with SQL backend? Does VBA (and by extension DAO/ADO) interact with backend directly or is that done by JET?

4) Good to know that Access can play nicely with SP (for SQL Server at least).

As for ADP, I never even considered them anyway. No good reason to, really.

 

[Shep]

Your frontend won't work with an SQL backend without modifying it. So, no, upsizing is not just for backends. On the contrary, much more consideration must be given to the frontend when upsizing than to the backend. If your backend is properly indexed and named, it's a quick and painless upsize to SQL. The frontend is a different matter, as you're no doubt aware.

Microsoft's recommended approach (for the frontend) is to upsize to Linked Tables. This allows your Access application to run unmodified after the upsize and then the application can be tuned.

If you're going to upsize the frontend manually, it is essentially a rewrite. There is much to consider, so much that it's impossible to address it all at one time.

Do you want to call stored procedures? Major rewrite.
Are you using all DAO? Major rewrite.
Are you using all ADO? Much less work, but still a ton.
Connections to the backend. DSN on each workstation? Possible maintenance nightmare.
DSN-less connections? Can be done by enumerating and modifying the TableDefs collection. Possible complications, however.
Parameter queries which reference Access forms? Won't work, as you've mentioned.
Queries which call custom functions? Must be rewritten in TSQL, or major rewrite.

And on, and on...

I determined that were I to upsize the backend and modify the frontend to work with it, it would take several weeks...about the same amount of time it took to recreate the frontend.

If you don't want to use linked tables, you're looking at loads of work, unless your application uses only simple Select queries and has no user-defined functions, and doesn't reference Forms in queries...all very unlikely.

Here's the white paper on upsizing.

I still can't find the specific webcast I mentioned. Sorry about that. It was actually a webcast about SQL basics, and there are dozens of them.

If I were in your shoes, and didn't want to recreate my frontend, I'd consider using the wizard and tell it to create linked tables to your SQL backend.

Then there are some other considerations.

Windows Authentication on the backend, or SQL?

If Windows, someone will have to create accounts for every user on the server in your main office.

If SQL, Trusted Connection or no? If no, connection string will include the password. How will you secure it, assuming you want or are required to?

I've never written a frontend for an SQL backend, so it's possible I'm overcomplicating things, but something tells me I'm not.

Good luck, whatever path you take.

 

[Banana]

Interesting. To be 100% honest I'm still not sure if I'm understanding how one "upsize front-ends". Are we talking about if I wanted to put my lookup tables, temp tables and other tables that are kept local in front end to a SQL backend?

In which cases, I'd think this quite bizarre; I have no intention of doing such thing, and intend to upsize the tables that are linked from Access (as a backend) to MySQL and leave local tables in front-end alone. SQL or not, it's faster to have some local data available for lookups, and can be updated periodically if necessary, and making a temporary table in backend is a stupid idea; think of what would happen if two users happened to call make-table query same time!

And I may be confused as I've come to understand upsizing as expressly limited to changing how data is stored (from Access's file to SQL server), and does not otherwise modify the forms and the likes. Or were we talking about how upsizing requires changes in how you access data in recordsources, VBA, whatever?

That said, I do totally believe you that things can get complicated. The best piece of advice I ever got WRT migration was this: Overshoot the estimate on time to market. By a far stretch. This way, if everything goes smoothly, everyone gets a pleasant surprise. But no disappointments when problems crops up left and right and ends up taking months to get everything running properly.

BTW, thanks for the link.

 

[Shep]

I am speaking of a split database, Access front and back ends. In any context, "sizing" is not an appropriate concept, as the size of the db doesn't really have anything to do with it, unless your reason for "upsizing" is because you're bumping up against db file size limitations. I suppose the whole thing is more of a "conversion" than anything. No matter what you call it, both parts have to be converted, unless you simply create links to your new backend data. Temp tables can just stay in your frontend as always. You can exclude them during a wizard upsize. According to Microsoft and to my own experience, creating links to the new SQL backend allow your application to run just as it is, with very minimal modifications. Mine required none, with the backend on the LAN, at least in the confines of my short test run. How well this scenario might work with a remote server I am not sure. How much better, or more reliably or efficiently it may run, I do not know...but I rather doubt you'll gain much in that regard by "upsizing". I opted not to use Linked Tables, but they may be more than suitable for your purposes.

As I learned, I gained little to nothing from "upsizing" the backend data to SQL were I to continue using the original Access frontend. This project was crying out for re-creation in another tool, so that's what I did. We'll be porting parts of it to ASP and will benefit from having SQL backend data in the long haul.

 

[Banana]

Yes, I do have to agree that the word upsizing is a bit odd however you look at it.

That said, I'll be sure to investigate how well everything runs after the conversion on linked tables only.

For now, I've decided that it's better to keep Access as FE, assuming there are no other 'gotchas', for three reasons:

1) RAD. Sure, I can do more in a .NET studio, but not as fast as I can do with Access. Heck, it's possible to create forms without having to write single line of codes. This is needed as the database is based on various contracts that may have requirement modified on an annual basis.
2) N-tiered architecture is something that I do not need at the present time or foreseeable future. Because I do not need extra layer, it means using Access will yield shorter development cycles. At least if time comes where I need to have 3 layers, I've already got the back-end that's perfectly compatible and capable of interacting with such front-end.
3) Using a web-based front end is not an option as this would require more red tapes in convincing people involved that yes, this is secure enough. Suffice to say there are luddities involved in the process. We both know that Access sucks big time with Data Access Page. Therefore, local application is preferred over a web-based application.

From what I've read so far, Access has shown to play nicely with various SQL providers provided that the developer know what he is doing with Access (e.g. using only and nothing but queries for all form's recordsource with restricting criteria so we're not pulling everything over the wire). Exactly how modifications is required seems to be more of a case-by-case basis, hence my interest in understanding every possible factors that could affect the conversion.

Do you happen to know exactly what is being upsized in a front-end? I googled briefly and am under the impression that you could use upsizing wizard to move your forms, reports and whatnots from a .mdb to .adp... Is that what we've been talking about upsizing front-end? If so, then feh. I have no intention of using .adp.

 

[pbaldy]

Is there anything at all within VBA modules that may cause problem with SQL backend? Does VBA (and by extension DAO/ADO) interact with backend directly or is that done by JET?

I can't think of anything. Whatever method you use to get data with (linked tables, ADO, DAO, etc), once the data is in Access, VBA will work with it the same way. It's simply a matter of a different connection string to work with different back ends.

I'm a little confused about what seem contradictory statements by Shep:

Your frontend won't work with an SQL backend without modifying it

This allows your Access application to run unmodified after the upsize

But it's probably just my brain taking Sunday off. We appear to agree that if you use linked tables, your front end can work virtually unchanged with SQL Server.

 

[Shep]

Sunday for me, as well, brainwise. I mean to say that the frontend won't work with an SQL backend without modifying it, unless you use linked tables. If banana mentioned somewhere that he (?) intended to do so, I missed it. Since the upsizing wizard will create the links for you, and banana doesn't include "upsizing" a frontend as part of the process, I assumed he did not intend to use links. It's easy to wander into useless territory when making assumptions heh heh.

 

[Banana]

No problem at all.

It's good thing that you pointed this out as I was under the impression that upsizing was confined to moving tables, but didn't realize it could do same for forms and reports and whatnot, which I do not want to. Since I'm considering MySQL, upsizing wizard would be of no help (not that it would have been a great help had I chosen SQL Server...), so it's just a matter of manually exporting tables to MySQL backend and re-linking the tables.

A bit tangential- I saw an article written in 2003 say that ODBC is being phased out of Microsoft's communication paradigm, and that ADP is the future. I already know that ADP was a half-assed project that is encumbered by far more limitations than benefits it offers. However, the part about ODBC being phased out is a bit odd. Could there be more modern protocol to connect with various database?

Another question- If I have a query and I decide to use backend's function instead of JET's function. Let's say the function called is IFNULL(), which is foreign to JET as it uses ISNULL(). Does that mean the query now has to be a pass-through (which has its own of limitations) or is JET smart enough to shut up and let backend handle the function?

 

[Banana]

Just in case my question was buried, I'd like to repeat it:

If I have a query and I decide to use backend's function instead of JET's function. Let's say the function called is IFNULL(), which is foreign to JET as it uses ISNULL(). Does that mean the query now has to be a pass-through (which has its own of limitations) or is JET smart enough to shut up and let backend handle the function?

 

[pbaldy]

It will need to be a pass through.

 

[Banana]

This follows up another question: Can JET accept a query that is based on a pass-through query?

Also, are any pass-through ever updateable? My impression is that NONE of pass through query will ever be, but wanted to make sure that was what I understood.

 

[pbaldy]

If you mean can a regular query be based on a pass through query, sure.

According to MS, a pass through query will not be updateable. I typically only use them for reports and such, so to me it never mattered.

 

[Banana]

So that dampens my idea of eventually optimizing queries to use the backend's custom functions, at least for forms.

Does that mean for forms and whenever I need a updateable query, I would have to stick with JET's functions based on simple select queries from backend, at the expense of a performance hit?

Furthermore, if SQL handles a data types different; say dates which JET wraps in #s while SQL Server uses 's, does that mean I'm SOL as well?

 

[pbaldy]

Not knowing what you're trying to do, I can't suggest the best method. As noted earlier, I typically use forms bound to the tables. A function could be on the form rather than in the query, couldn't it?

SQL Server does use the single quote around dates, but it's a simple matter to code it that way when required. It could even be dynamic, with a variable representing that character in code, set appropriately.

 

[Banana]

As I've yet to migrate, I don't have a specific method in mind, hence me asking the questions. Of course I will ask a specific question when the migration is underway and I'm stuck later time.

As for your solution, this is interesting. But if you're binding the form to tables, doesn't that mean you're now pulling the entire recordset from the server? Isn't that why we were supposed to use queries in first place; to place a criteria so we would pull only what is actually needed?

As for character differences, let's see if I'm understanding you- I could just make a parameter query, set it as string (when it's technically not a string) then pass the parameter as 'Date', then it'd work just dandy?

 

[pbaldy]

I usually open forms in either data entry mode or with a wherecondition, so I'm not pulling all the records over the wire.

The exception to that rule is lookup/maintenance type forms, where there are only a few records in the table anyway.

I guess I don't really follow where you're going with the parameters. If it's a Jet query, use #, SQL Server use ' (I don't know about MySQL). My suggestion was that if you were building SQL in VBA that could go either way, you could use a variable.

 

[Banana]

Funny, I seem to recall a old thread here talking about how wherecondition actually "filter" the data (i.e. pulling the whole recordset then narrowing the recordset locally) rather than pulling only the needed records, and that was why I have since avoided using wherecondition argument in favor of a query. Maybe something changed or I'm not completely understanding the problems associated with wherecondition.

It also sounds like you usually use VBA to build query and address the backend directly as opposed to calling a stored query, which lets you get around the differences in SQL dialects, correct?

I'll keep that in mind, in case using stored queries would cause more problems. You see, most of my forms uses a stored query as recordsource, and I kind of thought I'd be able to modify the query to conform with backend's dialect if I used parameters, defined the data type as string when in actuality I'm using date as criteria, and enter '1/1/2001' as the string, in which JET will pass along to the backend thinking it's a string, while the backend would read it as a date type and give out the appropriate response.

This is probably something I'll need to experiment with, though. I don't know how you stand on this, but I was told that if you use stored queries, especially for action queries, it's faster than if you use VBA to loop through a recordset, at least as long the logic involved is simple.

Did that clarify the matter?

Regardless, thanks so much for your insights!