Does anyone have experience Upsizing that could give me a couple pointers? I just Upsized a MS Access 2003 database into SQL Server 5 test/dev environment and have run into a couple of snags. I am thinking I did something horribly wrong during the process but I don't know enough about the process to know for sure.

I Upsized using the handy dandy Upsize wizard that MS Access 2003 has built into it and chose not to upsize what little test data I had in the database at the time.

After the wizard completed I was left with my local 8 tables and nothing seen for SQL tables. Thinking this was like splitting the database I deleted the local tables then ran the Link Tables utility under File/Get External Data/Link Tables.

Now instead of only having the original 8 tables I now have 289 tables and the wizard sent me through a screen for each one to choose fields for Unique Record Identifiers.

FYI, not knowing what to do I started out choosing some and realized I didn’t know what I was doing so I skipped 98% of the tables.

Now all my forms can’t find the Upsized tables and pointing the form’s record source to the Upsized Tables is easy enough but will I have to go back into all my code and hard code all my queries and functions to the Upsized tables as well?

Thanks in advance for your assistance.

James

1. The upsize wizard for 2003 can't handle going to SQL Server 2005 (if you are really talking about 2005 instead of 5) so you would use the SQL Migration Assistant instead.

2. If you had to choose the key for each table then you most likely didn't have primary keys set in each table, or if you did the upsizing wizard couldn't handle it for 2005.

3. Hopefully you have a backup of your Access database and can blow the SQL Server database away and start from scratch. (this time downloading and using the SSMA instead)

4. When you use your ODBC connection to link you will need to use the right driver (if you are on Windows XP or prior you would need to use the SQL Native Client driver. If you are on Vista or above then you use the normal SQL Server driver).

1. The upsize wizard for 2003 can't handle going to SQL Server 2005 (if you are really talking about 2005 instead of 5) so you would use the SQL Migration Assistant instead.

2. If you had to choose the key for each table then you most likely didn't have primary keys set in each table, or if you did the upsizing wizard couldn't handle it for 2005.

3. Hopefully you have a backup of your Access database and can blow the SQL Server database away and start from scratch. (this time downloading and using the SSMA instead)

4. When you use your ODBC connection to link you will need to use the right driver (if you are on Windows XP or prior you would need to use the SQL Native Client driver. If you are on Vista or above then you use the normal SQL Server driver).



Hi Bob,

Thank for helping out.

Item 1.
I had the IT Guy check and we are using SQL Server 2005. Would it be better if I upgraded my MS Access to 2007?

Item 2.
I had Primary Keys in each of the 8 tables I built. But during the Upsizing the SQL Server put in another 281 tables that I have no idea what they are and why they are there. It was these tables that needed a Unique Identifier.

Item 3.
I make frequent backups and made one before I tried Upsizing too. As for SSMA, I have never used it but I am researching it now.

Item 4.
The only driver the upsizing utility would let me use was one named SQL. But I will watch for the SQL Native Client driver.

Thanks again for your help.

James

1. Not necessary to upgrade to use with SQL Server 2005. In fact, where I'm working we currently use SQL Server 2005 with Access 2003.

2. I think you might have better luck with SSMA.

3. See #2

4. If you don't have it installed you may need it. However, most features will work fine with the default SQL Server driver but if you find you need some of the newer 2005 features then you would need to use the Native Client driver (your IT department should be able to install on each PC if it isn't).

Thanks Bob.

Jms

Hey Bob,

I installed SSMA 2005 for Access 4.2 and during the installation process I was prompted to install a perquisite AccessRuntime 10 before finishing the install. I installed it and then rerun SSMA. Now when I try to launch the database I get what looks like a new version of MS Access 2010.

However, when I get the Access Security Notice I can see what looks like a grayed out “Home” tab with headers for Views, Clipboard, Sort & Filter, Records, Find, Windows, and Text Formatting.

After I click the Open button on the Access Security Notice I loose the Home tab and the only tabs I see are File and Acrobat.

I can navigate through the splash and the switchboard but I don’t know how to get back to my tables or anything else.

Did I do something wrong? Or is Access 2010 part of the process?

James

You shouldn't need the Access 2010 runtime. I would uninstall it.

And which page did you get SSMA from? There are different versions.

I think you want to go to this page (http://www.microsoft.com/sqlserver/2005/en/us/migration-access.aspx) and then use this link (http://www.microsoft.com/downloads/details.aspx?FamilyID=5abe098d-c7e1-46c6-994a-09a2856eef0b) for the download.

And you might want to look at the README (http://www.microsoft.com/sqlserver/2005/en/us/migration-access-readme.aspx) and FAQs (http://www.microsoft.com/sqlserver/2005/en/us/migration-access-learning.aspx) before going on.

You shouldn't need the Access 2010 runtime. I would uninstall it.

And which page did you get SSMA from? There are different versions.

I think you want to go to this page (http://www.microsoft.com/sqlserver/2005/en/us/migration-access.aspx) and then use this link (http://www.microsoft.com/downloads/details.aspx?FamilyID=5abe098d-c7e1-46c6-994a-09a2856eef0b) for the download.


That’s the pages I downloaded the SSMA from.

I will try to uninstall the Runtime 2010 and see if it corrects the issue.

Thanks again,
James

Hey Bob,

I talked with my IT people today and they agreed that I needed to uninstall the RunTime 10. After uninstalling the application the computer hung up during the process and I just got it back up.

IT wants me to upgrade to Office 2007 Pro and they think that should take care of the issue.

A question I am hoping you can help me with is that during the migration process earlier, when the utility got to the point where it asked me what tables I wanted to Upsize I clicked “Select All” not really looking through the little window and seeing just what I was selecting.

By chance do you know if MS Access may be Upsizing tables from the Access program or something?

James

Yeah, it might have selected the system tables too, but there aren't that many of them so I don't know how you got so many.

Did you make sure to select NEW database for the SQL Server side and you didn't select an Existing one like the MASTER or something like that?

Ah, you know I chose an Existing one. The IT people built a SQL database and gave me the name for my MOD and PROD so I assumed that I should choose an existing one.

I just chatted with IT and they said there were only 7 tables in the database. From my view I could see nearly 300 links to tables. I could even open them!

Anyway, thanks for all your help. I will be upgrading to Office 2007 and that is supposed to be the magic wand to fix everything, right? LOL

James