Question Upsizing from Access 2010 to SQL 2012 Express? [Split FE/BE] (1 Viewer)

[jonathanchye]

Hi all,

I have a split MS Access 2010 databases at the moment. I've managed to "convert" the backend to the SQL Express database using the built in SQL Upsize Wizard.

Instead of chossing the option to create ADP file I've selected the link option. Everything is fine bar some relationship-based errors which I can address later. Now the tables in my backend has been renamed while I have links to the new tables on the SQL server.

So far so good but I cannot figure out how to convert the front end to now link into the SQL Server. The only way I can see is to create a new ODBC connection and then link it into the front end. However, this also looks like I'll have to manually change the data source of all my forms, queries and reports (and also my code...) which basically means I have to re-create my whole front end?

I've tried the migration tool and it seems that it just does the same thing as the wizard...

Anyone here have experience of upsizing split databases to SQL?

 

[KenHigg]

After you link the tables in the front end you can change the table names back to the original names that the forms, etc recognize.

 

[jonathanchye]

After you link the tables in the front end you can change the table names back to the original names that the forms, etc recognize.

Hmmm do you mean something like :

dbo.tblCustomers -> tblCustomers (orignal name) ?

Not sure you if you rename SQL tables that way within Access?

 

[KenHigg]

Yes, thats what I mean. I'm not 100% sure you can either but it's worth a try.

 

[jonathanchye]

Yes, thats what I mean. I'm not 100% sure you can either but it's worth a try.

I'll give that a shot after lunch but I don't think it will be that easy

It seems that it'll be easier if upsizing from non-split DB instead. I've Googled and so far can't find any good answer to my conundrum...

It will definitely be hard work if I have to redesign the whole front end again! The other option would be to re-combine the split DB and then resize I guess but not much info on that as well

I'm hoping someone here has upgraded their split DBs onto a SQL backend...

 

[jonathanchye]

Changing name works! Although I need to tidy up some of the code especially ones using DAO/ADO stuff but at least it works!

Just have to figure out how to avoid changing names during upsizing as some of my databases has over 30 tables

 

[jonathanchye]

Wow... it seems that if we do go SQL I would have to basically re-write 80% of the FE as most if not all the queries are formed for JET... Just not something we have the resource to do at the moment but from now on I will be careful in designing new databases to make sure it's very SQL compatible.

 

[KenHigg]

Yeah - I guess thats a gotcha.

 

[jonathanchye]

Yeah - I guess thats a gotcha.

Indeed. I'll have to add that JET is pretty good at most stuff that I throw at it but when the data + users increase SQL is the way forward. Need a bigger dev team though to undertake this task

 

[Lightwave]

Its not best practice but as an interim you can simply strip the tables out of a copy of your front end (copy mind) and hook the tables in through the Odbc link and your application will very probably work fine.

The queries will do exactly what they did before provided the tablenames have changed. You may then be able to work on the queries of the particularly large forms. This works quite well for beginners because it gets them up and running and they start to get a grasp of how they should have integrated it from the start with SQL Server.

I'm about to do something similar but I've only got about 20 users and although the information is complicated there's not a lot of it so its working fine at the moment.

 

[Pat Hartman]

Wow... it seems that if we do go SQL I would have to basically re-write 80% of the FE as most if not all the queries are formed for JET... Just not something we have the resource to do at the moment but from now on I will be careful in designing new databases to make sure it's very SQL compatible.

I don't know what you are basing this statement on.

If you do the upsizing from the FE, the wizard will rename the SQL Server tables for you. If you do it from the BE, the tables won't be linked to your FE so you'll need to do it manually. Doing it manually means that you'll need to rename the linked tables to remove the DBO_ prefix (the wizard fixes these names for you if you upsize from the FE). This doesn't change the table name on the server. Take a look at the MSysObjects table and you'll see both the Name, which is the real name of the table and ForeignName is the name you will be using in the FE.

Generally, the only change you should need to make to your DAO/ADO code is to add dbSeeChanges to each .execute and .OpenRecordset that references a table with an autonumberID.

I don't think I've ever seen an article that specifically tells you to run the upsizing wizard from the FE of a split database so you can say you saw it here