Need tips on building front-end when splitting database

Topic

New Reply

Hi All,

I am in the process of splitting a database. IT has grabbed the back-end (MySQL) and I need to re-do the front-end and keep it Access 2010-compatible. What I am looking for is this:

One or two tips from anyone who is interested in answering: “I wish I had known about ________ before I started building the front-end”. Or anything that you feel is REALLY important that is easy to overlook until it bites you!

What would you like to tell me to make my life easier?

Appreciate any and all input, as always.

mm

Reply | Quote

Replies

Will this be running in a multi-user, distributed scenario?

Reply | Quote

Yes. I will be responsible for distributing the front-end (probably through link in email) and unfortunately I know just enough to be dangerous!

Reply | Quote

One of the major concerns here will result from the fact that you will have the network as a delaying factor. This means most often that not, avoiding bound forms, and to be very frugal in what you request from the database – the less data you retrieve each time you get it from the database, the better. It’s easy to treat a split database as if it were a single one, and Access will allow it, but the UI response can be problematic, if you are not frugal.

Reply | Quote

I already have various scenarios for querying and filtering the data-ask but I will have to read up on all that is involved in bound forms as I think that is what I mostly have right now.
Thanks for your suggestions!
Meleia

Reply | Quote

In addition to Rui’s comments, are you users all on a co-located LAN? If so, the issue of bound forms isn’t that much of an issue, assuming the LAN runs at 100 Mbit. If on the other hand, they are geographically distributed, then it becomes a major issue. We have had some success with what is called a hybrid approach, putting the back-end in the cloud, but as Rio indicated, you want to carefully limit the return of any data to a small number of records to get acceptable performance. If you do have to resort to using unbound forms, the additional coding required defeats most of the development time advantages with using Access as a front-end.

I don’t have that much experience with mySQL, but we almost exclusively use SQL Server back-ends with Access front-ends, and get excellent performance. You do have to be concerned about multiple join queries however – anything with more than 3 tables, and sometimes 2 tables, is likley to want to pull all of the data into Access and run the query in Jet rather than in the database engine. The unknown in those situation is the robustness of the ODBC driver for mySQL. One solution we have found is to create an Indexed View in SQL Server and then link to it – I believe there is a similar construct in mySQL that you should be able to connect to the same way. Another solution is to write “pass-through” queries in Access, where you construct the SQL statement in mySQL syntax. However the results of such queries are gennerally not updateable.

Hope that helps get you started – feel free to post with questions as you proceed.

Reply | Quote

And yes, I do read alot of what is on line along with the books that I own, but there is just so much data that it’s easy to get buried.
That’s why I’ve asked for your top tips and ideas :rolleyes:

Reply | Quote

Tables will all be on one server in CO. Users are all across the country although I wouldn’t expect more than 10 to 20 on at any given time and most would just be pulling data for their geographical areas so that will limit some of the total data pull.

I have most of the queries broken down to 2-4 tables per “part” of the the final query with maybe 2 to 3 of these queries needed to get to the final product. This has been necessary because I have several many-to-many join tables (unfortunately). But also makes new queries a lot easier with these “base” parts already completed for reuse.

I have a few union queries that I have already been warned against as their purpose is to intentionally pull a cartesian product.

i have several forms that I still want to use and hopefully there will be no problems. The forms are all based on queries, not tables. The main form holds a subform; clicking on an item on the subform pulls up three tabbed sheets with various information, one of which holds photos. The tricky part here is — how does SQL do with photos? I am hoping I don’t have to do away with this information. But haven’t found anyone yet that really knows how they interact. Any record may have up to 25-30 related photos.

Data output will be Excel or PDF most likely. Can’t think of anything else right now.

I’ll take any and all suggestions on tips for distributing the front-end also. And any security problems that have surfaced out of nowhere that I should look for.

Does this give everyone enough to think about? Couldn’t do this without you all.

Reply | Quote

With that additional background, you do need to be concerned about limiting the amount of data collected. If the main form is only displaying a single record, that works pretty well, and the subforms will as well as long as they aren’t displaying a large number of records related to the main form record. However, if you are using photos, that may be a game changer. I can’t advise you about storing photos in mySQL as opposed to simply storing a link. But each photo will typically be in the 100Kbytes to a few Mbytes, and that can slow things to a crawl if you try to download 25 or 30 of them. We typically only store the path to a photo, and them populate an image control based on that path. That may be a significant challenge.

The other tricky thing is setting up the ODBC link to a remote mySQL server – a brief search suggests that each user may need to have a VPN in order to do that. Creating a connection to a SQL Server instance that is remote is a fairly tricky issue as well, which is why MS has deployed SQL Azure. I would tackle that issue first by setting up a simple test and seeing how easy or difficult it is to get a connection to a remote mySQL system.

Reply | Quote

Can I ask why are you using Access for this? It would seem a web app would be the way to go here, given the distributed nature of the access, unless you do have a VPN and performance is good. I would offer that it is possible that Access will be able to run in such a scenario, but the architecture you are proposing hardly strikes me as the best option to do this.

Reply | Quote

I agree – that’s a very appropriate question. I suspect it’s the development effort to do a web app and having the existing Access app. We’ve had some success with hybrid databases using a SQL Server back-end in the cloud, but it requires significant tuning. Another option would be to use a terminal services approach with Citrix or other alternatives. Then you have the whole app on one server, and it’s just the transfer of keystrokes, mouse movements and screen repaints, and no tuning is required. However it does require a software investment.

Reply | Quote

By the way access to the database would be by their regular NT logon.

Reply | Quote

A little history. And when you have added all you wish to add to this subject, please feel free to say so.

I am not an IT person nor is it my background. I created a database in dBase 20 years ago then another in Access about 10 years ago. It has always been a case of necessity. I created this Access database about 5 years ago, have maintained and updated it and have been running it locally. Additional tables, queries, forms have been added as requests from different departments have been made. These are typically data combinations that haven’t been made before, i.e. one department will find a need to relate their data to another department. It’s my job to find that “third” or “fourth” source that I can relate to “this” that I can then relate to “that”, etc. and finally come up with what is needed.

This has worked well however the amount of “matched” data is now at the point where the powers that be want to put the tables on a server (where some of the data will be updated from other databases) and want me to maintain and distribute the front end. Remember, I’m not IT; I don’t even know if this is the best solution and don’t want to end up under the bus. I will have no control over the tables and any changes will have to be made formally to another department to get to when it fits their schedule. To be honest I see disaster and I’ve worked too hard to see this go to waste.

After reading your comments from yesterday I ran a few queries, only a few (simple ones) ran smoothly, about a second or so. Others hung up the system entirely.

So with all this in mind, what would you suggest for the back-end as well?

Know this is asking a lot but your voices of experience are invaluable (ego-stroke there).
Thanks
Meleia

Reply | Quote

Yours seem to be a typical scenario for Access – start small, add things here and there, interest grows up and you end with something that probably should migrate to a completely different platform, architecture.

I doubt MySQL could be an issue. with decent design, performance should be enough for your needs. If you do need Windows integration, in terms of login, I think that is not supported by MySQL (although I am not sure), and it is supported by SQL Server. So going SQL Server, for the backend, may be an option.

It seems using a web app, connecting to the SQL Server / MySQL backend, would probably be a better option, in terms of handling the distributed access and performance requirements for your app. This would mean ditching Access as front-end and would require web developing skills for the front-end. Of course, your own findings raise one question here – for the situations where performance was bad, what was the reason -were you retrieving too many records, were you using bound forms, was there something else that could be solved?

Using Access as the front end does bring advantages in terms of the apparent simplicity of the UI design, but things are not so clear cut. It may be harder and costlier to use a web app, but it is also likely that such an option would be the best in terms of performance.

All these considerations should be taken with care. I don’t really know the specific situation you are facing, so I am at a disadvantage here :).

Reply | Quote

You and Wendell have given me plenty to think on. And part of my problem is that even I don’t know all the specific situation parameters. I believe my next step will be to study various scenarios and offer up what I can do and what I am not qualified to do. My answer to any request has always been “sure, no problem” then figuring out how to do it. It may now be time to say “not so fast”, there need to be a few more eyes on this to come up with an optimal solution.

I know I can create a front-end page that will return the results to a few queries. But the results are for upper level and they are not going to be happy with a screen full of data with no analysis.

So first step, setting up the above page, then enlisting sponsors for more detailed usage.

I do thank you for your time and thought on this and am sure I’ll be back!

Meleia

Reply | Quote

The evolutionary path that Microsoft would suggest is to create a web-app using SharePoint, and that might be an alternative, but it does preclude the use of VBA in your “front-end” – does your current Access front-end use VBA? There is also a cost of using SharePoint and that may be an issue. I think you should consider engaging an experienced consultant to look at your current design before you go much further. I agree with your concern in an earlier post; this has the potential to be a disaster that throws you under the bus!

Reply | Quote

Company policy — no Access databases on the Sharepoint sites. There is very little VBA in the database anyway. Think I’ll create a few simple queries that I know will retrieve some of the most requested data, then “encourage assistance from others” to create the sophistication that is expected.

Thanks for the assistance, particularly in saving time by my not getting engaged in a losing process to begin with.

Will let you know if I see progress and I’m sure you’ll here from me again!
Meleia

Reply | Quote