Unanswered: Combine several Access dbs into one application
I’m relatively new to Access development, so I would appreciate any suggestions. Thank you for advance.
My organization has several Access databases such as Personnel, Financial, Contractors, and a few others. All of them are completed dbs with switchboards, forms, reports, etc. Now they want to combine them all into one application. They want to have one screen where anyone would choose the task they needed. So different buttons will open different .mdb files. What software should be used for that type of interface?
Would VB be a good choice? I did projects in VB when I had to reference different .mdb files. If I use VB to accomplish that will it be too complicated to adjust all existing .mdbs to work as one application?
Why not use Access? You could in fact import all the forms into an access Front End, create a Menubar and enable disable according to passwords or what have you.
All the tables could be placed in one huge backend and if the need arose upsized to SQL.
The advantage of this approach is the backend could share data, for example contractors address details from the contractors files could be used by the financial files for paying bills and so on.
I would suspect that normalisation of the data in the backend could lead to a reduction in data and room by about 50% or greater, with consequent improvements all round. This may require thinking outside the 'square', in as much as contact data could all be stored in one massive table instead of four or five seperate tables, with a field to reference what contact type they are.
Thaks for your reply.
I heard a lot that Access fails to have more that 20 connections at a time. That's why I thought that it would be more easier to keep all that dbs as separate files. In fact, they do share some tables.
The advantage of a single backend, is that even items that seem unrelated in a split set up can in fact be related, sometimes directly, sometimes with a little creative thought. I developed / developing a super application that essentially runs my entire division there are literally hundreds of tables yet nowhere is any data repeated.
Access connections is not an area I have had to worry about, however, I think you could upsize from Access to SQL as the backend and the problems go away. This is probably wise from the storage limits in Access as against SQL anyway.
Perhaps a post should be started on connections in excess of five to a backend database and see what others solutions are.
We use access with about 20 connections, and it works fine. The only issue is running the make-table queries by more than one person at the time, the tables blow - but this is easy to take care of by adding a control for rebuilding the tables.
Regarding consolidating or keeping separate files, there are advantages to both. I do agree you can normalize your data maximally by using a single back-end, but if that is not your concern, you can just add buttons to the swithcboard that would run your other access databases, and it would seem as they all run from the same place. This way if one of them goes down, you can isolate it and fix it, without having a complete outage.