I have a split Access 2003 database and a new Azure account I'm struggling with. I want to migrate the back-end tables into Azure and set up ODBC connections so about a dozen 2003 users can connect, login and update data directly using a front-end .mdb file. I'm expert in Access and teach computing, but I don't know much about ODBC, SQL Server or Transact-SQL. The largest table has less than 1,000 rows and three tables are joined.   I've searched and tried threads here and elsewhere for specific how-to's without much success. Every time so far I've reached a point where their instructions don't apply or simply don't work and I've wasted a lot of time finding that out. Most of the authors don't seem to have actually tried it.   Is there someone who's successfully done this who can help please? The instructions need to be detailed - for instance if I have to write script WHERE do I write it and how do I get there? Links are OK as long as they're high-quality.   I just set up and configured a classroom WLAN network from the router manual (never done anything with networks before) so I can follow instructions OK.

Hello Mirihika,

Have you tried the steps mentioned in https://msdn.microsoft.com/en-us/library/hh313051(v=sql.110).aspx. Please let us know if they don't work. Also, please post the steps that you are following to migrate the tables and the error message(s) that you get.

Regards,

Kumar Bijayanta

• Marked as answer by Mirihika Friday, January 23, 2015 12:52 AM
• Unmarked as answer by Mirihika Tuesday, January 27, 2015 4:36 PM
• Marked as answer by Mirihika Tuesday, January 27, 2015 4:41 PM

Hello Mirihika,

Have you tried the steps mentioned in https://msdn.microsoft.com/en-us/library/hh313051(v=sql.110).aspx. Please let us know if they don't work. Also, please post the steps that you are following to migrate the tables and the error message(s) that you get.

Regards,

Kumar Bijayanta

• Marked as answer by Mirihika Friday, January 23, 2015 12:52 AM
• Unmarked as answer by Mirihika Tuesday, January 27, 2015 4:36 PM
• Marked as answer by Mirihika Tuesday, January 27, 2015 4:41 PM

Hello Mirihika,

Have you tried the steps mentioned in https://msdn.microsoft.com/en-us/library/hh313051(v=sql.110).aspx. Please let us know if they don't work. Also, please post the steps that you are following to migrate the tables and the error message(s) that you get.

Regards,

Kumar Bijayanta

• Marked as answer by Mirihika Friday, January 23, 2015 12:52 AM
• Unmarked as answer by Mirihika Tuesday, January 27, 2015 4:36 PM
• Marked as answer by Mirihika Tuesday, January 27, 2015 4:41 PM

Hi Mirihika,

As other post, please post the error message for analysis.

And you can also check the following blogs about how to use SQL Server Migration Assistant (SSMA) for Access to migrate Access database tables to SQL Azure.

Access to SQL Server Migration: How to Use SSMA
http://blogs.msdn.com/b/ssma/archive/2011/01/28/access-to-sql-server-migration-how-to-use-ssma.aspx

Migrating Access Jet Databases to SQL Azure
http://blogs.msdn.com/b/ssma/archive/2011/04/13/migrating-access-jet-databases-to-sql-azure.aspx

Thanks,
Lydia Zhang

Don't know if it's the full answer yet, I need to try it and that will take a few days.

First; I would unsplit the 03 and migrate it to Access 2013 and get it working ok in this mode - you'll be going from the old toolbar to the ribbon and things can be affected in just this step... so you definitely want this much working before doing anything else.....

then; I would recommend that you get a 365 Small Business Account to hold the back end as sharepoint lists.... I am unable to explain how this differs from an Azure account.....but all the literature related to Access is coupled with a 365 account......

With Access 2013 you can then split the db and one of it embedded feature is to split it to your 365 account.....

Note that every table must have a primary key field.....

Thank you for that suggestion but I would only try it as a last resort. Here's why:

The existing database has a dozen users who all have Access 2003 (and don't want to change), and its back-end is in a shared Dropbox folder. They have individual front-ends containing different VBA-driven forms matching their permissions and roles. Dropbox has significant sharing issues so I want to keep their existing front-ends and link to a back-end database on-line (using ODBC) where they each have their own login. Migrating tables intact would be the ideal solution. I looked at what's available and Azure seemed to be the best fit as all I'd need to learn was some simple TSQL to reconfigure what's different. I'll also need help with ODBC.

The table fields are full of lookups and joins and validations so those must be retained - and all tables have primary keys.

Thank you Kumar. I've marked this as the answer after reading it right through and it does seem to describe what I want to do.

However there are 17 pages of instructions to follow and many new concepts to understand along the way, so it will take a while to do. I'll post to this thread if there's anything I can't solve, but it might be a week or two as I have other tasks as well.