Solved

'Upsizing' an accdb to SQL Server

Posted on 2013-11-13
5
850 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

808 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