Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

'Upsizing' an accdb to SQL Server

Posted on 2013-11-13
5
Medium Priority
?
929 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 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 49

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 59

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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

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

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

572 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