I would like to have some clarifications on certain doubts that I have got. In order to implement Access FE (Front end) and mySql BE (Back End), what would be the ideal way to go about?
1. Split the Access DB into a FE and BE and then link the BE to mysql tables. This would help me to achieve the advantages of Access FE.
2. Have only a single Access DB and straight away link the tables to mySql backend, without any splitting of the databases? If this is a good way to go, then can I make multiple copies of this DB and have a copy running locally on each client machine, with a central mySql server only? I was thinking this way just in order to avoid certain concurrency data access problems that Access has inbuilt (due to inherent structure, i guess!!!).
I look forward to hearing from all you Access experts out there regarding the above which will eventually help me deploy a good DB system for my orgranisation.
Thanks a ton in advance to all of you for all ur time and efforts.
I have had a lot of luck doing what you're proposing. As a general rule of thumb, Any tables that are used by more than one user or in more than one *.mdb file, I put in MySQL. Any tables that are local to the single user (Such as the Switchboard table that Access creates by default when you set up your switchboards), I leave in the FE *.mdb file.
Since it sounds like you're likely to have several users, give careful consideration before you create tables as to how you structure them. MySQL v4.0 (current production version) has the ability to create both schema-wide permissions as well as table and even column level permissions by user. (the MySQL Administrator is very user-friendly in this regard). If you structure your data with a little forethought at the outset, it can make life much easier down the road.
There are several ways to link to those tables on the BE, depending on how sophisticated you want or need to get, and which version of Access you're running. The simplist way is to just link in the tables. If you're going to be processing large amounts of data in your queries, I strongly reccommend the use of pass-through queries, as they process the data on the server itself, preventing the transmission of superfluous data across your network. If you need to pass run-time variables into those pass-through queries, you can do so with VBA. This can take some trial and error to get the syntax right, but you'd be amazed at how much faster things run when you're processing through tables with several thousand records, particularly if you're doing so with joined tables. For this, you'll want at least Access 2002 to minimize headachs.
Tks Spinnaker for that answer. It was quite helpful. I presume what I will do is split the Access DB into FE and BE and then link all the BE tables with MySql tables. This would be a better design, because then I just need to save a copy of FE locally on all client machines, rather than saving the whole database, without splitting, on the client machine. I could make a .mdb copy available on the network and all the clients can reference that same copy via a short cut on their desktop, but there are certain concurrency problems that I need to cater for (the bad ones, not the good ones), for smooth working of the DB. Hence the splitting sounds to be more better from my point.
Also, how about I convert the FE and BE into .mde files instead of .mdb?
You've got the general idea. Though if you're using MySQL as a BE, you don't really need a BE MDB file... that's just a wast of time and memory, as your FE db's will all have linked tables to MySQL anyways... That, and to alleviate headachs, don't connect to the tables with location "localhost", even if you're working on the server, or your users will get a message that mysql can't find the table at localhost, and you'll spend your first day running around fixing the same problems. (of course, i've never had experience with this problem MYSELF, i've just heard it's a pain... (: )
Assuming of course that you have ODBC installed (which I assume you know that you will need to do on all client machines for them to access the data in MySQL), exporting your tables to MySQL isn't too difficult a task, either.
Unless someone knows of a way to do so in batches (there may be applications or modules out there you can download, but i've never looked), you can right-click on each table and export it, and as a file type, choose ODBC. you then pick, or create, a link to the DB you are exporting to in MySQL, and the data will all carry over. The one down side to this is that while the table structure and data export cleanly, you will need to re-create all of your indices and re-set the Allow NULL parameters, as well as re-defining the primary key... but at least you won't lose data. the other way to do this is to create the table structure in MySQL, and then use an append query from Access to put the data there.... there are benefits to doing it either way...
As to the *.MDE file, i don't know what you know or have heard, but a word of caution:
***do NOT lose your original *.MDB file***, or you will have to start over from scratch if you ever need to make any alterations to the FE db (and i've never seen one yet that didn't need to be fixed, either because you missed something in programming, or because of end-user requests...)
(I have had the unpleasant experience of having to tell a prospective client that they wasted several thousand dollars having a programmer create a db, because he had lost the original file, and couldn't re-do the work.)
beyond that, it does have the benefit of insuring that your end-users can't screw up your work. Of course, if your users aren't sophisiticated Access users, you can prevent most of the damage they could do by disallowing the options in the "Startup" menu. then unless they know to hold the shift key down as they open the db, they won't be able to change the forms or code anyway.
Why did u not come to the forum early, buddy? U r a great help. The answer u wrote to my query is very much what I was looking for. However, certain doubts still persist.
1. U said of exporting the tables to mysql. Do I Link the tables or Export them?
2. If I need to export the tables, then will I need to do that at certain interval of times OR a dynamic link will be created that will allow the update of BE tables as the user makes changes to exisitng data or adds new data?
Finally, for the mde file, yes u r right, I need to keep my original mdb file. I have done research on that and was going to implement it the same way as u suggested. The example that u provided was really thoughful of u, as it will provide guidance not only to me but also to other users of the forum.
Awaiting ur reply soon. Thanks a ton again. Cheers.
sorry for the confusion about exporting vs. linking.... let's see if i can clear that up.
I didn't know where you are in the development of your project, hence the suggestion of exporting existing data. the data should only ever exist in one place.
If at this point you have no tables created at all, then by all means, create them only in MySQL and create links to those tables in your access FE. Access treats linked tables as if they were access tables, regardless of where the data actually exists. ( I have a few db's with Access tables, MySQL Linked tables, and DB2 Linked tables). The great thing is that while Access treats the data as if it were there locally, it must still follow any rules inherent in the actual table structure. (In your case, that would be whatever constraints you set up in MySQL.)
If, on the other hand, you're like I was, and you've got an existing Access MDB BE, and you're looking to migrate the data to MySQL for performance or other reasons, then Exporting those tables over is the ticket for you.
Either way, once you put the data in MySQL, then that's where it resides. The linked table you see in Access is nothing more than a "roadsign" that points Access to where the table exists, in this case on your server.
The best way to see what i'm getting at, though, is just to experiment and see what happens. I recommend spending some quality time in your local bookstore, and find a good reference book that looks like you can relate to it.... that, and find a good forum!
I've had my bacon saved from being burned several times, so I'm glad to do my fair share and help someone else.