upsizing database

Question

I need to upsize a split Access database, i.e., one that's currently split between tow mdb files, a front-end and back-end. I see many webpages that in essence say, "run the Upsizing Wizard." My first, very basic question:

Should I be running this wizard in my front-end mdb or my back-end mdb?

I assume I don't want to link main mdb -> backend mdb -> sql server. Should I run the wizard on the backend mdb, and then in the frontend mdb change the linked tables to point to sql server rather than to the backend mdb? If so, how is this done? When I right-click and go into the Linked Table Manager for a table in the frontend (linked to the backend md), it only seems to let me choose a new mdb file.

Or, is there any good tutorial you can recommend on upsizing a database that's already split?

Thanks!

Answer 1

Hi Julia,

I would agree with your first guess: you will want to run the wizard on the back-end mdb.

Once that's in SQL Server, also as you guessed, you'll want to link the front end to work with the SQL Server data. One way to do this is to set up an ODBC data source for your new SQL Server database and select that in the Linked Table Manager.
 1.Open the Data Sources (ODBC) shortcut: in XP Pro, this is in the Control Panel under Administrative Tools. (If you don't see it, you probably don't have permission to create a data source, so you'll have to work with your network people to do this.) This will open the ODBC Administrator.
 2.On the File DSN tab, click Add.... You'll see a list of available drivers. Select SQL Server and click Next. (If the front end is only being used on your machine, you can create a System DSN instead.)
 3.Find a common location and name your data source.
 4.Click Next and Finish. This will set up the first part of the data source, and will open the SQL Server data source wizard.
 5.Name the data source and select the server on which you've put the upsized back-end database.
 6.Change the rest of the settings as needed (you may not need to change much, but the scope of those changes may require a second question) and click through to Finish.
 
Once you have the data source set up, then Get External Data should give you the option to select it as your source. (In 2007, you can get there from the External Data ribbon. ODBC data sources are available under More.)

To expand a little further based on Matt's follow-up questions:

How you do it is a design choice. I recommend upsizing the back-end mdb because that would allow you to keep whatever forms and such you had in Access; I think it's less of a transition if your data is in SQL Server.

Before you upsized, your tables were linked to the back-end database, and the Linked Table Manager showed the links. After you set up the ODBC data source and linked those tables, it'll show that link. You'll view the links in two different ways because they're actually different types of links (Access vs. ODBC), even though the links may look the same in your front-end mdb.

---

Iric Wen
TechNet Community Support