Solved

'Upsizing' an accdb to SQL Server

Posted on 2013-11-13
5
822 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
5 Comments
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 100 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 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 100 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 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 150 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 150 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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now