Quick Access->Sql server Upsizing Question - split mdb front/back end - which to upsize?

Posted on 2006-05-25
Medium Priority
Last Modified: 2013-12-05

   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
Question by:fruhj
  • 4
  • 4
LVL 65

Accepted Solution

rockiroads earned 1000 total points
ID: 16766428
Simply put

yes, u have to upsize your backend

Your frontend does have to change though

Firstly, u have to relink to SQL Server

Microsoft came about with ADP, its like MDB but made for SQL Server. It is recommended, though not required to migrate to an ADP.
ADP's are made specifically for SQL Server

What you may find also is that you have to tweak your queries to run SQL Server style (T-SQL)

Any VBA code that uses DAO, I would recommend you change to using ADO
LVL 65

Expert Comment

ID: 16766431
Do note, ADP's you cannot really create local tables, as all data is direct in SQL Server

some more info
LVL 44

Assisted Solution

by:Leigh Purvis
Leigh Purvis earned 1000 total points
ID: 16767931
And as fun and handy as they can be - MS are backing off from the ADP these days.

You'll likely find it a much easier transition sticking with an mdb and linked tables (as Jet with the ODBC provider will do a *lot* of interpretation for you) allowing you to keep your queries as they are.

It isn't ideal - and there's lots of avenues for slowness due to inefficiency (a Server database isn't a "magic fix" for all ills).

But if you're finding your way then having to write T-SQL might be a bit much to start with?

(And as rockiroads says - you'd have to use ADO... for example CurrentDb effectively has no meaning in an ADP - even though it's still there!)
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

LVL 65

Expert Comment

ID: 16768137
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 :)
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16768183
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.
LVL 65

Expert Comment

ID: 16768355
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

LVL 44

Expert Comment

by:Leigh Purvis
ID: 16768408
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? ;-)
LVL 12

Author Comment

ID: 16774057
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
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16774880
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.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses
Course of the Month14 days, 11 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question