Jump to content

  • Log in with Facebook Log in with Twitter Log In with Google      Sign In   
  • Create Account

Subscribe to HRA Now!

 



Are you a Google Analytics enthusiast?

Share and download Custom Google Analytics Reports, dashboards and advanced segments--for FREE! 

 



 

 www.CustomReportSharing.com 

From the folks who brought you High Rankings!



Photo
- - - - -

Offline Db Suitable For A Mysql User - Ms Access?


  • Please log in to reply
12 replies to this topic

#1 rolf

rolf

    HR 6

  • Active Members
  • PipPipPipPipPipPip
  • 675 posts
  • Location:Suffolk UK

Posted 24 November 2008 - 09:20 AM

A client has a completely old and out of date database (MSDOS based) running a lot of essential functions in his office.

I was asking who built it and who maintains it, as it's so old not many people would be happy to be responsible for it these days - turns out that the guy who built it and used to occasionally support it died some years ago and my client doesn't know anyone who could help if it went belly up. To his credit, he knows this is a dangerous situation, but I was still a little shocked and had to sit down for a cup of tea and a biscuit!!

Anyway, he's asked me to migrate it to a newer platform, even though (as I explained to him) I'm a web programmer and don't really know which offline DB systems would be suitable, let alone how to use them. Still, he wants me to have a go, so I said I'd look into it and consider whether it was a good idea or not.

Does this sound like a nightmare in the making? I'm pretty proficient at MySQL and if I was building something for him in MySQL I would have no problem achieveing what he wants, but I'm not confident that this will translate easily to using an offline db.

What system(s) would be easiest for me to learn quickly?

Thanks in advance

#2 Randy

Randy

    Convert Me!

  • Moderator
  • 17,540 posts

Posted 24 November 2008 - 10:10 AM

Well, since you're already familiar MySQL that's the route I'd recommend. Setting it up is just a matter of setting up a server to hold the database, one that'll probably have Apache or something similar to hold the pages that access the db. It shouldn't take much of a server. I assume he probably already has a box that could be used in a pinch, though personally I'd want to develop it on a different box just to make sure I didn't toast his current database where there's no plan in place for emergencies.

The main issue is probably going to be how to export the data from the old MSDOS based system so that it can be imported into a MySQL database structure. There are lots of tools out there that will convert MS Access to MySQL and vice versa, but it sounds like the current db was a custom build. So someone would need to dig into it and at the very least figure out how to export the structure and data into a .csv or .sql format. It's difficult to say how hard that mountain might be to climb. But it might require hiring a consultant/contractor who has some skill with both MSDOS and MySQL.

#3 MaKa

MaKa

    HR 6

  • Active Members
  • PipPipPipPipPipPip
  • 856 posts
  • Location:Llantwit Major, Wales, UK

Posted 24 November 2008 - 10:37 AM

I'm with Randy and I would stick with MySQL if you know your stuff. I've done some basic development in MS Access etc. which I tried getting back into a while ago. I quickly found that I've grown too used to all my "magic" php/mysql functions/classes I've created over the years and simple things took ages to figure out.

I would ask for a copy of the software/database and do a quick initial investigation on how the data is stored. It could be anything from plain text files to some custom encryption. Also I would try to find out whether the source code of the program are available. Probably not, but it could make things easier if you can get it too compile again. Sounds like a fun project! smile.gif

#4 rolf

rolf

    HR 6

  • Active Members
  • PipPipPipPipPipPip
  • 675 posts
  • Location:Suffolk UK

Posted 24 November 2008 - 01:29 PM

Thanks, that all sounds pretty reasonable.

I was thinking that, being essentially a web setup, the apache/php/mysql approach would be a bodge job and there would be something more appropriate for office databases. I could definitely set him up with a server and get all the client machines to access it via IE/FF/WTF, but I don't have the first idea how the MSDOS setup is working so I'd need to look into that.

QUOTE
I assume he probably already has a box that could be used in a pinch


Yes and no. Yes it possibly could, but I personally wouldn't as it's too old and cranky - it's struggling to run win95 at any decent speed!! But either way, I think you're right about developing it on a different machine just from a safety point of view.

QUOTE
it sounds like the current db was a custom build


Absolutely right. I might ask to see his backups, but if they don't come as text or something I can easily read then I guess I'll have to find someone who knows about MSDOS databses to solve that.

QUOTE
Sounds like a fun project!


Yes, it's defintiely the sort of problem I find satisfying when I crack it, although I'm a little concerned that looking into the data processing is going to involve lots of investigatory work without any guarantee of paid work at the end.

This client is a decent guy and I get on well with him but he's, um... (***looks for diplomatic phrasing***) very careful with money, and I know he's not going to be happy to pay me for my time if I don't quickly get the end result we're after. (I guess that's kind of evident in that he's still running his office on an ancient database with no disaster recovery plan!!)

Guess I'll do a little investigating and see what I can find out about his current setup.

Thanks again.

#5 Randy

Randy

    Convert Me!

  • Moderator
  • 17,540 posts

Posted 24 November 2008 - 03:24 PM

Win 95 !?!?! eek.gif

As scary as that prospect is, it might actually be a good sign. Let me rephrase that statement. I could be a good sign in that if my decrepit, constantly failing memory serves me correctly (HA!) back in the mid-90's most MS-DOS "databases" were file-based. Meaning the info was simply there in a text file that had a funny extension that hid the fact that it was a simple text file. And most of these were usually set up where tab delimited formatting was the only major formatting. Other than the tabs it was simply text.

If this old, fading memory turns out to be the case for this project, it may in fact be quite easy to convert the custom built MS-DOS db into something that can slide right into a MySQL server. Frankly that's going to be your major hurdle IMHO.

#6 rolf

rolf

    HR 6

  • Active Members
  • PipPipPipPipPipPip
  • 675 posts
  • Location:Suffolk UK

Posted 25 November 2008 - 04:14 AM

Cool, that would be a total result! I'l drop in and see him later today and check if thats the case :-)

In that case, are the tables likely to relational and is there any likelyhood that the language used to get things working would be decipherable to someone who knows sql? As the system is working fine as it is, if I can simply recreate the tables and commands in mysql then this should be a failry straightforward job crossfingers.gif

#7 MaKa

MaKa

    HR 6

  • Active Members
  • PipPipPipPipPipPip
  • 856 posts
  • Location:Llantwit Major, Wales, UK

Posted 25 November 2008 - 05:03 AM

QUOTE(Randy @ Nov 24 2008, 08:24 PM) View Post
Meaning the info was simply there in a text file that had a funny extension that hid the fact that it was a simple text file. And most of these were usually set up where tab delimited formatting was the only major formatting. Other than the tabs it was simply text.


Or it would be stored in a struct with fixed width fields for strings. This maybe a little bit more complicated to convert, but should be do-able.

#8 rolf

rolf

    HR 6

  • Active Members
  • PipPipPipPipPipPip
  • 675 posts
  • Location:Suffolk UK

Posted 25 August 2009 - 11:47 AM

An update on this, and a question/request for advice and suggestions.

I was able to read the data files in notepad, however there was a lot of junk/unrecognisable characters in there that no application I used was able to read. Although I wasn't able to extract the data, I was able to get enough info from it to work out how the thing was structured and make a plan to essentially copy/update the functionality it provided.

Based on my estimate of how long it would take to build (and his agreement to be patient so I could squeeze this project into my dead time in exchange for for a good rate), he said he could trim the data sets and make it economical for one of his staff to manually transfer all the active records over.

So although it's not ideal we found a solution so that he will then have a database with proper backup/recovery in place and that other people can extract the data from if I was no longer able to run it for him.The back end is built and the prototype/testing of the front end has all been good. We're all happy and we're on the debugging/tweaking stage before it goes live :-) Thanks for the help

Just a further question or request for suggestions/ideas.

One of the ways the customer wants to be able to view the data is as one long record set he can scroll/tab through. I'm sure this is not going to be much use to him when there are several thousand records to scroll/tab through, and I've told him this, but he saw it work nicely with 10 test records and has decided that this is what he wants, so obviously I'm going to try and provide it.

We've currently got a test set of data of about 600 records and the browsers are getting really sluggish when they display the whole lot, so I'm somewhat skeptical of their ability to handle the full set of data. One idea has been to paginate the thing to x records at a time, but the client really likes the idea of being able to scroll through the whole lot so if anyone can suggest how this might be done without slowing the browser to a crawl then I would be keen to hear about it.

Thanks again

Rolf

#9 1dmf

1dmf

    Keep Asking, Keep Questioning, Keep Learning

  • Active Members
  • PipPipPipPipPipPipPip
  • 2,167 posts
  • Location:Worthing - England

Posted 26 August 2009 - 05:28 AM

Well I know most people here like to MS bash or at least are PRO *NIX and therefore mySQL.

But I used to run many sites , even the company day job DB's on MS access before we got SQL Server.

There is nothing wrong with it, ok if it's a webhost environment they need the special MS Access JET driver installed before you can connect to it, on windows it's non-issue, however *nix webhosting need to install it and not all will, as I found out myself.

With a few tweeks to use a DNS-Less connection, even my SQL module will work with MS Access, it's a better option than flat text files that's for sure.

Personally I'd go for MS Access over flat files everytime, but YMMV wink1.gif

And if it's a choice of having to prat arround setting up a linux/apache server and installing and running PHP , mySQL & myPHPAdmin , when I'm already using Windows platform, well the choice to me is a no-brainer... MS Access! -> damn sight nicer playing with your data and writing reports as oppose to myPHPAdmin , that sucks IMO! horrible DB interface.

#10 WSO

WSO

    HR 3

  • Active Members
  • PipPipPip
  • 94 posts
  • Location:Dallas, Texas

Posted 27 August 2009 - 12:59 PM

Rolf, if he's going to have 600 records or more, and your scrollable display is going to have several columns, then you're right, no matter what, it's going to take a very long time to load the page. And it sounds like the 600 might just be a subset of the final full recordset. In looking at one of my table displays just as an example, if I made it 600 rows, the resulting html code to display it is about a half a megabyte. It takes a while for the browser to download that file to display it!

I definitely think you should "sell" him the fact that it will be a much harder to use solution for him to have hundreds or thousands of records on the page. A better solution is as you say to paginate it, but make the column headings clickable to change the sort so that he can quickly locate the records he wants to scroll through.

Another good thing would be to put a filter screen in front of it, so that you can winnow down the list of records you want to scroll through (only records after a certain date, where last name begins with "B", etc.).

Tom


#11 rolf

rolf

    HR 6

  • Active Members
  • PipPipPipPipPipPip
  • 675 posts
  • Location:Suffolk UK

Posted 28 August 2009 - 07:30 AM

QUOTE
Personally I'd go for MS Access over flat files everytime, but YMMV


I appreciate the info but the main structure is built now (with MySQL) as is most of the interface, so it's just the fine tuning of the finished thing I'm working on now really. I'll have to make some time to have a play with Access, as it sounds like you're into it.

WSO: Yeah, 600 is just an example sub set for me to play with, the real thing is going to have several thousand. Further to that, each record in the main table can have numerous other associated records from 4 other tables, so a complete display for just the 600 records could be a table with a several thousand rows of info - over 12mb for one page at the last count, so it's no wonder IE is unhappy :-s

Will definitely try to sell him the pagination idea - thanks for the moral support to both of you:-D

#12 Scottie

Scottie

    Psycho Mom

  • Admin
  • 6,294 posts
  • Location:Columbia, SC

Posted 28 August 2009 - 08:12 AM

Hey Rolf-

I actually built something similar for an internal network for a local small business.

We installed WAMP on the machine that was to run the application and built a web application that everyone in the office could access via browser/internal IP address.

I actually hired someone to convert the data- we were going from Access to MySQL and posting a bid on Scriptlance I found a guy who really knew what he was doing. I don't know if he wants his email posted publically, so if you are interested PM me and I'll send you his details.

The price was really reasonable- we moved a couple of huge databases for around $250.

#13 1dmf

1dmf

    Keep Asking, Keep Questioning, Keep Learning

  • Active Members
  • PipPipPipPipPipPipPip
  • 2,167 posts
  • Location:Worthing - England

Posted 28 August 2009 - 08:31 AM

Rolf you could be really clever and use ajax to grab then next sets of data asynchronously so it appears as though you are scrolling through, but you would have to write the scrolling interface. with div's and overflow handling.

grabbing the data via JSON or similar and storing data means you could update the DOM dynamically from Javascript and the JSON data, however, it means coding an application and that equates to your time and so more money!

CODE
I'll have to make some time to have a play with Access, as it sounds like you're into it.
It's not like i'm into it, more like it's all my company had at the time and so it was a requirement to learn it, but i'm glad I did.

It's really handy because it gives you the power to write complete DB applications that run on any PC with MS Access. And if you have Access 2007 you can even now wrap your application up in a runtime and so it runs even on machines without MS Office let alone MS Access. wink1.gif

I've written a really cool app for my mates DJ business where he stores all the tracks he has, title, artist, CD no and Track no, so when he gets requests he just types in the search boxes provided and it shows him all the disks the track is on and the track number.

It also intergrates with his website, so he can Syncronise his local DB with the mySQL db on the website. then customers can login and create their own play lists.

He then Synchronises customer playlists to his desktop (well laptop) application and by a press of a button it brings up all the tracks on the customers playlist, with a click of the mouse he can select a track and that will bring up a window telling him what CD's and track numbers the tune is on.

He can also mark the playlist tracks as played as he goes through them.

With the reporting engine in MS access, it was simple to generate a couple of reports which he binds and places on pub tables for people to choose tracks they want to sing for his Karaoke part of the business.

It took me 3 afternoons to write! you can't knock that now can you smile.gif

It also makes your data highly portable and highly exportable, to make it compatible with the website / mySQL , I just wrote the code to export the access DB as pipe dilimeted, then wrote some perl backend code to update the mySQL, easy peasy!

And hey if you keep the MS acccess as your actual web backend DB and moved host, no pratting around with DB exporting , or tying to convert from mySQL to MS SQL if hosting changes.

You download the ms access file and you have the whole DB tables and data in one handy file to do with what you want.

OK if you need to store millions of records MS Access is not efficient enough to use in an intense eCommerce environment, but basic applications it woks fine.

Edited by 1dmf, 28 August 2009 - 08:38 AM.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

We are now a read-only forum.
 
No new posts or registrations allowed.