Need Help on Implementation of New Database Design

[vermette09]

First a little background info. I am a third year accounting student that has worked at this shop (we build dies mostly for car parts) for 6 years. Slowly moving up the ranks and earning larger tasks. This summer I am being tasked with updating our current database that holds all our information for job costing from purchase orders and quotes to material and labour, everything goes through this database. The database is in access 2002 file format and is currently being used through a custom Visual Basic 6 application that we created ~12 years ago. Now, because of the way job numbers are being assigned, there needs to be an update to the design of the database and with that a software update. I have used Access in school and I am fairly fluent in it and have no problem with that aspect of the project. What I lack is the implementation knowledge once it has been designed and ready for production.



After doing some research I found that upgrading to Access 2013, splitting the database into a back and front end and giving the users access with Access runtime would be my best option. This would leave the datafile safe from unwanted modification and also allow me to give each user the limited access they need. When I ran my proposal by management they liked the idea of it but our IT guy had several comments.

1. He said that I will not be able to perform all the tasks that are down through VB6 because there is so much coding that went into it. - I asked him to tell me what I wouldn't be able to do and he couldn't give me an answer, just said so much coding goes into it. I wasn't trying to be arrogant I actually just don't see anything that can't be done through an Access form or report.

2. He said that crystal reports (what is used now) can do so much more than Access and I won't be able to do it. - Again, I don't see how Access would be inferior.

3. (The question I need an answer to the most) Currently, with the VB6 executable, based on the windows login of the person using the computer they have access to specified modules. If I am using the method of splitting the database, each person will have their own front-end DB with the objects that they need, but those will be files stored locally on the computer. Is it possible to give network level user access to certain front-ends other than putting user access restrictions on the specific files because he said that isn't good enough.

I read up on going through an SQL Server but I am not sure if that is what I am going to have to do.

Thanks for taking your time to try to help,
Josh

[June7]

1. VB6 might have some functionality not found in Access or might make it easier than with Access but I doubt you will miss it (list tree comes to mind).

2. I have heard that Crystal Reports can do some things Access can't, but again, probably won't miss it, I don't.

3. Multiple different user frontends - a maintenance nightmare. Have one frontend and build in restrictions based on user ID.

VB6 and VBA are very similar languages. I used VB6 books to help learn programming in VBA. A lot of code could probably be copy/pasted and then edited.

But if already have a functioning VB6 interface, why switch to Access 2013? Modify for the new data requirement. Surely the database is already split.

Access has a 2GB size limit so if you expect to outgrow that, SQL Server might be option, at a significant cost. MySQL or SQLServerExpress I think are free but also have size limits.

[vermette09]

I was thinking multiple front-ends would just include general uses such as purchasing, designers and accounting, but I can see what you mean by it being a nightmare to maintain. At the same time, the system that is in place now has been running since ~2002 and has had maintenance MAYBE once or twice, so it definitely wouldn't be a frequent occurrence. I wasn't aware you could build restrictions based on userID, would that be a viable solution? I thought it would be best to get out of VB6 just because of the simplicity factor. If you can build your forms and reports right in Access, why bother going through the coding of VB6.

[June7]

I agree, one app vs integrating multiple apps but you must weigh that against the cost/time of developing new app. Something you know works vs the unknown of Access frontend. Access does have some stability issues that maybe VB6 doesn't. I have a multi-user Access frontend/backend and occasionally automated update/replace of a user's frontend copy won't open properly and has to be manually replaced. Access files have been known to corrupt, especially during development when things are under constant change.

I use user identification to manage access to some features. For example, depending on user, some controls on a form won't be available. In another case, records are filtered by user ID.

[vermette09]

Well most of the new "design" is already done, the biggest change that was made was allocating our job numbers to a set of master numbers so each department and even activity's cost could be traced back. Would it be better to try to sync the new design into our current vb6 platform? I have 0 vb knowledge and thought it would be easier going forward to eliminate it. Even with daily backups of our master file would there still be issues with corrupt files? I'm not worried about the development process because the current system will be in place until the new one is ready to go

[June7]

Not sure about 'better' - so subjective.

I spent about a week exploring VB6 and got to the point I could create a simple project but never tried to integrate with database.

What do you mean design is 'done'? Have you built queries, forms, reports in Access? No idea how big your app is. Building objects can be a tedious exercise. Unfortunately, I don't know any shortcut for migrating from VB6 to Access - no conversion tools (although going the other way seems readily accommodated by 3rd party apps) and can't copy/paste objects.

Backups don't prevent a corruption event, just give you peace of mind. Other than the user updates of frontend, I haven't had corruption issue for years. Never had corruption of backend, but that can happen.

[vermette09]

Most of the queries can be pulled from the old Access database because they will function the same. I really only had to add a table and change the way our time sheets are recorded. So I guess it's not "done" but I'm not so much worried about the building of the objects because my most difficult queries have already been built and function properly. I don't need to migrate from vb6 because I can just pull the tables I need from the live database when I'm ready to switch over.

So basically I have the option of a) updating the database with new tables and queries and learning how to integrate them into vb6. Or b) splitting the database and giving each userID the proper permissions to select forms and reports in the front-end?

[June7]

That's appears to be the case.

Why isn't the db already split? VB6 doesn't have its own table structure. It has to connect to tables constructed in other software. Did you take the Access backend and build the new Access interface directly in it?

[vermette09]

The Access file has tables and queries and then VB6 has the forms and reports (reports with crystal), so yes I suppose it is split. To show management how I am going to improve the database I made a copy of our live-data and made new tables queries and reports within the copied file.