Programmer Created Custom Database, Hard Drive Crashed, DB won't work on new computer

[lv2drft]

First of all, if any of you know a progammer named Mark Patterson living in Canada, please send him a link to this thread. He is the programmer I'm referencing below.



So I am trying to help my daughters preschool with a computer problem. MS Access was used (probably access 2003) on their old computer along with Quickbooks to manage their billng, attendence, sign in sheets, etc. A programmer was hired to set this up. I believe he used visual basic to create a custom interface (screen with buttons that performed various functions such as editing the enrolled children, updating the data for quickbooks, etc). Whenever you open their database file, it automatically loads up his custom GUI.

This was all fine and dandy until they got a new computer. They hired a computer tech to transfer all their files to the new computer. Everything worked great except this custom access / quickbooks interface. He had it set up where the preschool administrator could use access and quickbooks with the old computer, and the information was saved to the new comp throgh the LAN. I later found out why he had it set up this way.

I was called in when the hard drive from the old computer had crashed. Luckily the database file was backed up via auto backup on an external HD. Access (I believe it's access 2007) is installed on the new machine. The problem is that wheneve I attempt to open their database I get a bunch of visual basic "do you want to debug?" errors and it can't update. In my novice opinion (PLEASE corrct me and guide me if I'm wrong), I would conclude that the program was written only for one specific machine, and cannot be installed on a new one. At this point, I'm not concerned with having a custom interface any more. We just need to find a way to GET RID of the old custom interface WITHOUT losing any of the data inside it. There's years and years of important info in this database.

How can I save the information, remove all non-standard programming tweaks to the database (so the db is universal again), then link it to quickbooks again?

Thanks in advance for your help here. I'm doing this for free as a favor to a non profit, wonderful preschool. Please try to help us out, the kids are counting on me and I'm a "weekend computer geek" but have very limited programming knowledge.

[NTC]

I will hazard the guess that the db is split, and that the various moves/crashes/new PCs did not include relinking FE to BE. Plus the move from an older version to a newer version db can always create the potential for some code issues that need to be tweaked. I have never yet seen a computer tech that knows about or understands client/server apps such they would know how to relink them when moving to new hardware. And in fact to be fair to that set of PC specialists, they really can't be expected to and one should always involve the application specialist when something like that is to take place.

Although it is totally unuseable at the moment, it is probably totally salvageable. Even the original author can't be expected to warranty changes in technology, crashes, etc.

Anyone managing a db needs education via a textbook, easily found at Amazon or a big book store. You perhaps need some pro help though....not sure.

Can't speak for Quickbooks but the Access files are probably .mdb (possibly .mde) - you should look for these. If .mdb, try to determine which is FE vs BE and then open the FE holding down the 'shift' button - and then relink using the Link Table Manager. All these tasks will be in a textbook.

Hope this helps a little.

[slave138]

I would also point out that some of those tweaks might be necessary to link the DB to Quickbooks depending on how it was used previously.

[lv2drft]

So basically it seems like either I'll have to study access for a few months or we have to hire someone again, right?

Just to ask, in a desperate hope to attempt to save the expense to the school--
Is there any way to / free program that would remove all the custom tweaks so we can salvage the basic data (which should be universal for any machine). At this point I'm happy to do it this way. We just need our forms and stuff that was in the database, then can attempt to link to quickbooks with a free program.

Thanks again!

[slave138]

The data may or may not be easily recoverable depending on whether the backup you found is a complete database (DB) or is the back-end (BE) of a split DB. If you are dealing with the front-end (FE) of a split DB then you might be able to recover the forms but the data is somewhere else (if it exists at all).

One thing you can try is to open a new DB and try to import from the backup. This can also give you some clues to the nature of the DB you are working with. If it includes both tables and forms in the objects available for import, it is likely a complete DB. If it only lists tables it is probably a BE and if it lists forms and queries but no tables it is probably a FE.

Someone might be able to offer a little more help if they knew exactly what nature of DB you are dealing with (FE/BE, complete) and the exact errors you get when opening it.

[lv2drft]

Thanks for the suggestion. I can't believe I didn't think about creating a new db and importing. I'll avoid asking any dumb questions and tinker with importing on access a little first. I'll also look into if it's a fe or be database and if it saves files elsewhere or within the database.

In the meantime, if and when I can get this back to a "standard universal" database, does anyone know of a free and easy way to link the database to quickbooks?

Thanks again for all your help guys!! I'm so grateful to have found this forum!!

[lv2drft]

OK... WOW was that a fun experience. I'll share what I did in case anybody else has a similar issue:

I found a backup of the old hard drive's image. I reinstalled windows, made sure to partition a C: drive (same drive letter as the original drive had) and ghosted the image back onto my partition drive. It had ALL the original files on it in their exact order. Then reinstalled quickbooks and access on the new computer's Window's partition. Imported the company file into quickbooks, opened the database and voila! it worked without the Debug errors!!!

Now the linking part won't work. Acess runs fine and the FE and BE parts both are working together, I can save files, edit the database, etc. But the programmer who made this custom database had a selectable box item in this database for "Update / Maintenance" which is where you were supposed to go to send the Access information into Quickbooks whenever the database was changed. I obviously won't be able to reprogram his VBA application to link to the new quickbooks, nor do I have any idea what software he used for his linking application (I'd say its a safe bet he created his own linking software). On to the actual question...

I'm looking at something called Synergration AccessBooks, which provides a one-way transfer of your database into Quickbooks. If I set this up, would it automatically transfer anything from the database into quickbooks, or would this require tweaking of his application to tell AccessBooks what to transfer and what not to transfer. If not with AccessBooks, does anybody know of a free (or inexpensive) and easy way to simply open a program that will send all information from a user-selected database file to the installed version of Quickbooks??

Help me out with this one last thing, please, and then I've got it all finished 100%... FINALLY! :-)

Thanks again for all the help

[NTC]

The last time I looked into Access/Quickbooks integration; I found that QB needed a specific file format, with headers/trailers etc that one couldn't generate via a regular query/export because it was multi line. There is SDK info at QB's web site.

At that time I also googled around and found a commercial specialty software company, which I no longer can remember the name, that had a premade package. They actually answered my email inquiry in a timely fashion and seemed viable. There are alot of zombie products out there no longer supported.

I am surprised I don't get involved with this more considering how widely both Access & Quickbooks are found in the same organizations - but.....

In any case; generically speaking one typically uses an Access query to define the record/data that is to go to QB...and then you the translator software to bring it into QB. Of course QB has lots of tables too: customer, accounts receivable, accts payable, etc etc etc...and so one needs to do it right. One can also export out of QB to Access too but again I think the formats differ so much that one needs a translator to reformat. I'm surprised if you have remade the original set up that this isn't working. Not sure this helps much but I for one would be interested in how your final translator solution goes. Will file it away in my QB folder if you post.

[lv2drft]

So basically what you're saying is that it's complicated and there's really no easy solution. No real translator program that will figure out the information. I think I'm going to have to find a freelance programmer. Anybody know of a good place to find an access / VBA qualified programmer?

Thanks

[lv2drft]

Or.... I could do a windows repair install on the old hard drive partition. Try to run the new system from the old windows OS. then use gparted to flag the old partition as boot and see how that works. Hmmmmm. Worth a shot I suppose.

[NTC]

I believe that if you find a commercial product out there that does the translation - - and you double check that they really are in business and not a zombie - - it may be that their product can be handled by a non-tech. Not sure, just maybe. Worth a look though.

One would think if you are going to go through all this that you would want to set it up to work on your new environment.