Upsizing a split Access database

Question

Hi, 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

As a matter of standard paranoia, I would just make a backup copy of the existing files and run the Upsizing Wizard on the front end. If anything undesirable happens, just revert the changes by overwriting with the backup copy.

Answer 2

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.

Answer 3

Personally I have found that the upsizing wizard does a very bad job of determining correct datatypes. I would create the tables myself in SQL server using the datatypes I need, then move the data to the existing tables from Access. other wise you will be stuck with text data when you could use varchar or float when you really need decimal.

Once the data has been moved then I would delete the Access tables and link to the SQL Server tables.

Do not do anything without having a backup copy of the database first.

Answer 4

Thanks Dave. A couple of resulting questions:

1. You say to upsize the backend and Jason above says to upsize the frontend. Is there one right answer here, or is this more like a design choice?

2. Following your instructions I was able to create a data source that's visible in the Linked Table Manager. Previously I had no such data sources--so what were the tables that I linked with the wizard being linked to?

Answer 5

Update the front end, and it will import the back end tables before it upsizes. I did this a week ago with a successful result.

However, any queries that use -1 instead of Yes will fail. Any full table deletes on tables without a primary key will fail, and you will get different behaviour from that than you will by merely using a pass-through SQL query to truncate table. The trunc will delete all rows, the Access version may leave a blank.

Also you'll need to include dbSeeChanges anywhere you have a recordset opening on a table with an autonumber column data type. SQL changes these to Identity data types, then gripes before you try to open the table. Good luck.