Hi Everyone!

Our medium-sized business has been running all of its data processing through several custom-programmed Access database interfaces. These are reasonably sophisticated systems that all tie to a replicated Access backend data center wth 70+ tables.

I inherited about 5 years of programming and have done about 3 additional years myself, so as you can all probably imagine, the amount of VBA programming and stored procedural stuff contained within is massive. We maintain legal contract records, financial transactions, product development reviews, and product concept inventories. We also have a piece of software that I programmed as a custom salesforce automation tool which integrates with Outlook a great deal (contacts, calendars, sending/receiving e-mails, etc.).

Almost everyone that works here uses these databases in some capacity. As I mentioned earlier, we replicate between sites (2 additional sites) and make extensive use of user level security to show/hide different views where appropriate. I am the only person here who manages and continues to develop and maintain our database systems.



Since I've discovered that Access 2007 seems to have discontinued both Replication and ULS (both critical to our needs), I am beginning to explore migrating to SQL Server 2005...at least for our backend data structure. I am a novice with SQL Server (I have only recently been given the proper tools and access to work with it), and I need some opinions and advice about how to attack this.

Within our largest data interface, we have over 80 forms, over 120 queries, and several VBA modules (additional to the tens of thousands of lines of VBA code behind the forms). We also have around 100 reports of varying complexity which our departmental managers depend on.

What are my options and where do I even begin? I've started playing with the upsizing manager, but it hasn't been very successful in translating much of the data. I get entire fields that come over as 'Null' and several critical tables don't even export. Once I do get the data over, I am concerned about the relationship integrity as well as the compatibility with our current functionality in the Access frontends.

Obviously, it might make this project impractical if I am forced to re-code all of the forms and queries into ADO (which I am familiar with) and Transact-SQL (which I'm not yet very familiar with). I'm already pretty over-worked just maintaining our current systems, but I certainly don't want us to be caught with our pants down if Access becomes legacy software (or maybe it already is...who can say).

Any help and advice is greatly appreciated!

Mike