Access Upsizing ADP or ACCDB

[Jkash]

Hi Guys,

I have got a booking system created in Microsoft Access 2003 that keeps crashing I think that it is time to upsize the back end to SQL Server while keeping the same Access front end.

This leaves me with two questions:

1. Should I convert my MDB file to a ADP or ACCDB
whilst migrating to SQL Server back end.

2. Are there any situations that will cause the front end forms, vba code and reports to stop working after the migration.


Thanks advance.

 

[ajrimmer]

No answers so far so here's my 2p.

1. I'd go ADP but that's a purely subjective personal choice.

2. There's likely to be issues to resolve & code to change. Impossible to predict how much. Some will upsize without issue, some will require a rewrite - depends on what you're doing. Would suggest you read up as much as possible about pitfalls before starting.

Where I looked at possibly upsizing in the past for my own work - I ended up starting (a new ADP) from scratch on the grounds of "if I knew then what I know now, then I wouldn't have done it like that in the first place.

 

[Crowley16]

1) you should, I have found .adps to be MUCH more reliable and stable thatn .mdbs, however you will have to re-write a LOT of stuff, all your queries for one...

2) yes, lots of situations. Unless all your forms are very simple, it's more than likely that each and every form will need to be fixed before it'll work.
Even so, I'd still recomment going adp...

--------------------
Procrastinate Now!

 

[Jkash]

Thanks for the replies guys, I think I will use adp but the task is quite daunting there are loads of queries to rewrite and the forms are quite complex.

I am just wondering if there are any good tutorials or books that tackle the issue of upsizing?

 

[Crowley16]

I recon you'd be better served if you read up on sql server, or go on a course...

--------------------
Procrastinate Now!

 

[ESquared]

I wrote tools for my ADP conversion that did some standard query syntax changes, by stepping through every form's controls and looking for things that matched and fixing them.

When I found a new problem, I wrote a new thing that would fix exactly that problem and ran that against all my forms again. In short order I had fixed 95% of everything and had little else left that I had to do completely manually.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[lameid]

Should I convert my MDB file to a ADP or ACCDB
whilst migrating to SQL Server back end.

You can accomplish alot of the benefits an ADP brings just by upsizing the backend and using an MDB frontend. The nice thing about taking this approach is everything should still work, although maybe not as quickly. However doing this, you can more slowly migrate to SQL queries by using SQL Pass through queries to replace reports and if you need to replace forms' queries (inline user-defined funcitions are updateable and can take criterai to run). This gives you SQL server now and lets you worry about fixing the most painful performance problems without going all or nothing. When most of it has SQL counterparts, you can look at doing a final migration to an ADP.

Personally I think there are some Access things that just do not work quite right in ADP's. The system I converted left a bad taste in my mouth, and personally I would be hard pressed to make another ADP Frontend vs MDB Frontend. There is more you HAVE to do to get updates working in an MDB with SQL Server but I like the versatility of MDB's better.

ESquared, if your code is versatile, would you right a FAQ and put your code in there?

 

[ESquared]

lameid,

I'm actually a little reluctant to post my code. I sort of consider my utility libraries to be personal tools, tools that give me an edge in a competitive market and that make me look like a magician when I'm just reusing tools I wrote in the past. I'm all for helping people, but I also have my side jobs to think about. I started a new thread to discuss this.

[COLOR=black #d0d0d0]When I walk, I sometimes bump into things. I am closing my eyes so that the room will be empty.[/color]

 

[ESquared]

Upgrading MDBs to ADPs, search and replace in forms/controls

[COLOR=black #d0d0d0]When I walk, I sometimes bump into things. I am closing my eyes so that the room will be empty.[/color]