Upsizing a split Access 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.

Marilou

Answer 1

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.

Tantrum

Answer 2

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.

Transudation

Answer 3

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.

Universally

Answer 4

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.

Mastin

Answer 5

Do it all in the front end

You can simply export the tables to SQL Server.

You can then delete the linked tables you have in your frontend.

Then link the connection to SQL Server

Check:

• when you open tables you get records
• all your queries run
• compile your code

You will also have to consider how you are releasing the front end. If you are using a dsn file you will need to provide that to each user.

You will need to determine how the end user accesses SQL Server. Are you using a single login with the username and password stored in the connection?

You could also split your backend DB into multiple Access DB and link them in the frontend.

Talavera