Solved

'Upsizing' an accdb to SQL Server

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

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 58

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

724 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