fruhj
asked on
Quick Access->Sql server Upsizing Question - split mdb front/back end - which to upsize?
Hi,
I have an access database thats already split into 2 mdb's - a front end and a back end. the front end contiains all the forms and a few temporary/user specific tables, and the back end contains all the data thats shared.
I'd like to upsize it to sql server in such a way that the front end MDB connects right to SQL server, and the back end mdb is no longer needed.
Can I do this with the upsizing wizard? I assume that if I upsize the back end, that I'll need to modify my front end by hand to get it to connect to SQL server instead of the MDB.
Just looking for some confirmation / simple advice before I start down this path...
I'm starting at 250 points, but I'm more than happy to up the points and split if there are several good replies.
- Jack
I have an access database thats already split into 2 mdb's - a front end and a back end. the front end contiains all the forms and a few temporary/user specific tables, and the back end contains all the data thats shared.
I'd like to upsize it to sql server in such a way that the front end MDB connects right to SQL server, and the back end mdb is no longer needed.
Can I do this with the upsizing wizard? I assume that if I upsize the back end, that I'll need to modify my front end by hand to get it to connect to SQL server instead of the MDB.
Just looking for some confirmation / simple advice before I start down this path...
I'm starting at 250 points, but I'm more than happy to up the points and split if there are several good replies.
- Jack
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Leigh, whats the reason for moving away from ADP's? Ive not heard anything about that yet
Mind you not surprising when my current role is AP with Oracle/Perl and hopefully some Java :)
Mind you not surprising when my current role is AP with Oracle/Perl and hopefully some Java :)
I think it's the Access team themselves rather than the corporate voice of MS that are gently backing away from ADP's.
Office 12 seems to have little to say on ADP's as far as advancements - and the word was a while back the head of the Access team had stated that mdb's and DAO were the method of choice for data access.
(I'm not convinced of that for server data mind - DAO? Eurgh. But I suppose he'd know lol).
I'm sure they'll better support SQL 2005 in 12 of course.
Office 12 seems to have little to say on ADP's as far as advancements - and the word was a while back the head of the Access team had stated that mdb's and DAO were the method of choice for data access.
(I'm not convinced of that for server data mind - DAO? Eurgh. But I suppose he'd know lol).
I'm sure they'll better support SQL 2005 in 12 of course.
still sticking with DAO?
well I guess its easy and simple to use, just like how the MS minds operate probably
Perhaps they think ADP's dont need more work, as MDB's surely still do, I dunno
well I guess its easy and simple to use, just like how the MS minds operate probably
Perhaps they think ADP's dont need more work, as MDB's surely still do, I dunno
My understanding is a definite lack of desire/effort to move adp's along as mdb's are being.
I'm not necessarily saying Office 13 won't have them at all... who knows what it'll have. mdb's even? ;-)
I'm not necessarily saying Office 13 won't have them at all... who knows what it'll have. mdb's even? ;-)
ASKER
Hey Guys (Presumed), thanks for the commentary.
I'd heard the same thing about the lack of support for ADP's.
My plans for now are to stick to the doa based code in the MDB.
I tried upsizing the back end to SQL server and it was a bit of a rocky road, but I managed to get all the data across one way or another (There were some dates in the access database from year 103, that caused the SQL wizard to fail without any reason given, but luckily the error was very clear when I tried with DTS)
I modified my front end with some code I got from the Oreily Access Cookbook, so it connects using ODBC, but without the need for a system level DSN.
Had a bit of a problem, but thats a different question if I can't figure it out.
Upping the points to 500 and splitting...
- Jack
I'd heard the same thing about the lack of support for ADP's.
My plans for now are to stick to the doa based code in the MDB.
I tried upsizing the back end to SQL server and it was a bit of a rocky road, but I managed to get all the data across one way or another (There were some dates in the access database from year 103, that caused the SQL wizard to fail without any reason given, but luckily the error was very clear when I tried with DTS)
I modified my front end with some code I got from the Oreily Access Cookbook, so it connects using ODBC, but without the need for a system level DSN.
Had a bit of a problem, but thats a different question if I can't figure it out.
Upping the points to 500 and splitting...
- Jack
No problem.
FWIW my preference (and that of some other's here too) would be to access the server data in code (likely through ADO though) and bind your form's to those recordsets (or if you can be bothered go unbound :-S).
But that's a lot of work.
Allowing Jet to play a role in the mdb can cost you efficiency - and hence scalability.
FWIW my preference (and that of some other's here too) would be to access the server data in code (likely through ADO though) and bind your form's to those recordsets (or if you can be bothered go unbound :-S).
But that's a lot of work.
Allowing Jet to play a role in the mdb can cost you efficiency - and hence scalability.
some more info
http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q325017
http://www.sqlservercentral.com/columnists/kKellenberger/accesstosqlservertheupsizingwizard.asp