?
Solved

'Upsizing' an accdb to SQL Server

Posted on 2013-11-13
5
Medium Priority
?
883 Views
Last Modified: 2013-11-16
I am developing an app in Access 2010 for a client that will eventually be run on SQL server.  In the accdb 'options' I checked the option to make Queries, SQL Server Compatable.

I have SQL Server installed on my machine but have not used it for anything yet.

I have never gone thru the 'upsize' process before and need guidance from someone that has gone thru the process.  I see  the 'SQL Server' button and have clicked it.  I know I will have to tell it where SQL server is, the password etc..

What are the undocumented problems that I will encounter?

Once I get past that, 'Is it magic'?  Will a SQL server database be created, linked to my database  with all of my tables, indexes etc..?

Once upsized, if I want to add a new field to a table how will I do that?  

How to add a new table?  Will this all have to be done thru SQL Server?
0
Comment
Question by:mlcktmguy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 400 total points
ID: 39645410
Generally, the upsizing process is pretty straight forward and automatic.

Potential problems:

-  Access-specific field types, such as Attachment and Multi-Vale fields will not convert.
-  Make sure that any bit fields have a default value, and that they don't allow nulls

Adding fields and tables will have to be done through Management Studio (or whatever interface you are using with your SQL tables).

Any recordsets or Execute statements in your VBA will need the dbSeeChanges option.

eg:

CurrentDB.OpenRecordset strSQL, dbOpenDynaset, dbSeeChanges

CurrentDB.Execute strSQL, dbFailOnError + dbSeeChanges
0
 
LVL 48

Assisted Solution

by:Dale Fye
Dale Fye earned 400 total points
ID: 39645454
Also, make sure that every table in your database has a primary key.  If not, you will not be able to update those records from Access once you migrate the data.  

Once I migrate the data to SQL Server, I always make sure that there is also a Timestamp field in each of the tables, which assists in identifying write conflicts.
0
 
LVL 1

Author Comment

by:mlcktmguy
ID: 39645710
Thanks for your responses, good items and suggestions.  You mention the format of the recordset statement.  Is ADO supported?  Most of my recordset opens look something like this:

selectString = "Select " & passedSelectFieldName & _
               " From " & passedSelectTableName & _
               " Where " & passedCompareFieldName & " = " & passedNumericID

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open selectString, CurrentProject.Connection, adOpenKeyset, adLockOptimistic


Should I revise the rs.open to look like this?


rs.Open selectString, CurrentProject.Connection, adOpenKeyset, adLockOptimistic, dbSeeChanges
0
 
LVL 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 600 total points
ID: 39645766
Well first thing, you *don't* want to use the upsizing wizard in Access.

For quite a few years now, the SQL Server Migration Assistant for Access has been written and developed on the SQL Server side and it's quite a bit better then the upsizing wizard in Access.

 So much so that the upsizing wizard in Access has been retired.

You can download SSMA here:

Microsoft SQL Server Migration Assistant for Access v5.2  
http://www.microsoft.com/en-us/download/details.aspx?id=28763

 The reporting is far better as is the conversion process.

 One thing you do need to do though is dig down through the options and check "Add a timestamp column to each table".   Not sure why it's not checked on by default.   It's almost mandatory for tables in order for Access/JET to work with them properly.

There's also some good detail here:

http://blogs.msdn.com/b/ssma/archive/2011/01/28/access-to-sql-server-migration-how-to-use-ssma.aspx

with some screen shots and what not that will help.

For the most part, it is pretty painless, but it depends on the complexity of your DB.

One thing that will trip you up the most is that SQL Server does not allow nulls in unique indexes.

Jim.
0
 
LVL 9

Accepted Solution

by:
Armen Stein - Microsoft Access MVP since 2006 earned 600 total points
ID: 39648979
I've written a PowerPoint presentation on techniques for using Access as a client-server front-end to SQL Server databases.  It's called "Best of Both Worlds" at our free J Street Downloads Page:

http://www.JStreetTech.com/downloads

It covers the pros and cons (yes, there are cons) of adding Timestamp (aka RowVersion) fields.  Spoiler - you should almost certainly add them.

It also includes some thoughts on when to use SQL Server, performance and security considerations, and techniques to help everything run smoothly.

Cheers,
Armen Stein
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question