So you’re ready to upsize…

Here are a few tips when upsizing tables to an SQL Server that I have picked up along the way…

Make a Back-Up of your Database… (added 7.26.2013)

Need I say more?

Just say *NO* to…

…Special Characters or Symbols

These characters have meaning when used in Access and on an SQL Server.  While wrapping your table/column names in square brackets will *allow* it, it is best to avoid these symbols when name Objects or Fields…

…Spaces

Like Special Characters and Symbols you will have to bracket any table/field name that has a space.

…Words reserved for Access of SQL Server

In Microsoft Access you can pretty much name your fields whatever you like.  You can even use Reserved Words just as long as you remember to bracket them when using in VBA and, yes, the same applies to an SQL Server.  However, you may not realize you’ve used a Reserved Word and will spend hours trying to figure out why your query isn’t working, for example…

This individual could not figure out what was wrong with their statement.  Several people chimed in to help and no one noticed the Reserved Word *Group*…

strSQL = "INSERT INTO Table101Contract(IDContract, Group) " & _
          "SELECT" & lngIDContract & ", Group " & _
           "FROM Table101Contract WHERE IDContract = " & varIDContract

Once *Group* is bracketed the statement performs as it should…

strSQL = "INSERT INTO Table101Contract(IDContract, [Group]) " & _
          "SELECT " & lngIDContract & ", [Group] " & _
           "FROM Table101Contract WHERE IDContract = " & varIDContract

…however, avoiding the use of such words will save you lots of headache in the long run.

Reserved Words for Microsoft Access and SQL Server
Allen Browne’s Complete List of Reserved Words  The most complete list on the Internet!
SQL Server 2012 Reserved Words
SQL Server 2008 R2 Reserved Words
SQL Server 2005 Reserved Words
SQL Server 2000 Reserved Words

…Look-Up Fields

Lookup will not be upsized to SQL Server or any other database as they are exclusive to Access.  In theory, it was a brilliant idea but in reality it’s a short cut you need to avoid.  And, they really don’t do you any favors in Access; see Why you should never use Look-Up Fields.

…Multivalued fields

Introduced in Access 2007 complex columns that can record multiple values my give you what you want in a regular database but it is not compatible with any other database.  Instead use what I call look up tables

…Hyperlink data type

Introduced in Access 97, it will store paths to documents on your computer, web sites, network path but has no equivalent on SQL Server.

…Prefixing tables with Sys, USys, MSys or a tilde *~*

This has only one real drawback…  In Access, prefixing a table with USys* or *MSys* (Access 97 or earlier) hides it from Navigation Pane but it can be shown by checking *Show Hidden Object* in Navigation Pane Options.  This little tip can be used to hide table(s) from the End User.

When a table is deleted, Access will often keep it around, with a tilde prefix, until you *Compact & Repair* and then it is gone.

…Prefixing Queries with a tilde *~*

See above…

...Yes/No Data type (also known as the BIT data type)

Okay, so this one is not *that* important to not use but when upsizing…

The first thing to check is any and all *YesNo* fields in all tables.  They must be unchecked to not allow NULLS and you must put in a Default Value of 0 (zero).  AND make sure you have a Default Value in the field or it will fail when changing the Properties on the Server.  Failing to do so will make the table un-updateable.

  

Another little tip about the BIT field…  SQL Server stores Yes as 1 and False as 0.  While it will recognize True/False, it will not return the correct results if you use -1/0.  So, when running your query against an SQL Server use…

            To find TRUE

            …WHERE YourField <> 0   ‘True
            …WHERE YourField = True   ‘True

             To find FALSE

            …WHERE YourField = 0   ‘False
            …WHERE YourField = False   ‘False

…Attachment Data Type (added 7.27.2013)

There is no *Attachment* data type in SQL Server.  The only suggestion I can offer here is FILESTREAM Storage.

Always use…

…Primary Keys (PK)

Make sure every table has a Primary Key whether you use or not.  I use Autonumber.  However, remember, this key is not guaranteed to be sequential so don’t count on for anything.  This Key should only be used as a unique identifier for the records in the tables.

…4 digits for years

You can show 2 digit years and even have your Users enter two digit for years but store 4 digits.

…a Naming Convention

Decide on a Naming Convention… My Naming Conventions (and make sure you data is properly normalized.  You don’t want to upsize only to find out your table structure needs to be redone!)

Your VBA…

While most of your code will work fine, there are a few traps that will result in errors.  When running and execution query against an SQL Server you will need to use dbSeeChanges or your queries will fail.  The exception to that rule is if you are opening the query/table in *Read Only* mode.

Open for Read/Write…

Set rst = dbs.OpenRecordset(“tblImport”, dbOpenDynaset, dbSeeChanges)

Set rst = dbs.OpenRecordset(“tblImport”, dbOpenDynaset, dbSeeChanges + dbFailOnError)

Open to run Action *Query*…

strSQL = “UPDATE…
        CurrentDb.Execute strSQL, dbFailOnError + dbSeeChanges

strSQL = “INSERT INTO…
        CurrentDb.Execute strSQL, dbFailOnError + dbSeeChanges

Open for Read Only…

Set rst = dbs.OpenRecordset(“tblImport”, dbOpenSnapshot)

dbFailOnError will roll back the *transaction* and give you a *useful* error message, plus, one you can trap and give your Users information they can use.  However, I have found that if you put…

      DoCmd.SetWarning False

…prior to your section of code, it will *fail* silently.  So, if you are in the habit of using the above line then add…

      DoCmd.SetWarning True

…before your strSQL line.

HUH?…

In Access, AutoNumbered fields are visible as soon as the record is added even if it hasn’t been saved yet.  That doesn’t work for SQL Server as AutoNumbered fields are only visible after the records have been saved.  So, you would need something like…

In DAO…

…
rs.AddNew
…
rs.Update
rs.Move 0, rs.LastModified
lngNewID = rs!ID
…

…to retreive the Autonumber.

text/ntext are deprecated… (added 1.19.2013)

In SQL Server 2005 I use to use *ntext* in place of a *MEMO* field data type in Access.  Well, those are gone use varchar(MAX)/nvarchar(MAX) instead.  (Thanks Ben Clothier, Access MVP, for pointing that out!)  For a complete list of what’s gone and its replacement, if it has one, see Deprecated Database Engine Features in SQL Server 2008 and Deprecated Database Engine Features in SQL Server 2012

Removing the *dbo_* prefix on linked tables… (added 7.23.2013)

After linking you’re now left with Linked Tables in Access that are prefixed with, typically, dbo_.  (This is because SQL Server uses Schemas.)  This is a problem for all your Queries, Forms, Reports, VBA, etc…  Let’s not panic, no need to fix *everything* in your database.  Simply, copy/paste the below into a Module (either a new one or… I keep this code in modUtilities)… 

Function FixTableNames(strPrefix As String)
'?FixTableNames("dbo_")

     Dim db As Database
     Dim tdf As TableDef

     Set db = CurrentDb
     For Each tdf In db.TableDefs
        If Left(tdf.Name, Len(strPrefix)) = strPrefix Then
          DoCmd.Rename Mid(tdf.Name, Len(strPrefix) + 1), acTable, tdf.Name
        End If
     Next

     Set tdf = Nothing
     Set db = Nothing

End Function

Press Ctrl+G on your keyboard to bring up the Immediate window and type…

?FixTableNames(“dbo_”)

…click Yes (or OK) while the vcode loops thru your Table Objects removing the *dbo_* prefix.

Validation Rules (Table Level)… (added 7.23.2013)

…are converted to Table Constraints.  This may cause a problem when trying to add new records and/or may not Upsize correctly or properly.  (I do my Feild Validation at the Form level.)  For more information see Incompatible Access Features (Access to SQL).

Made changes?  Refresh your links… (added 9.17.2013)

You’ve made some changes to your Tables and now you can’t enter anything, what gives?  You need to Refresh you links, while the links are permenant they are more like *snapshots* that will stay the same until you make a change then you need a new snapshot.

Access 2010 (and Access 2007 and Access 2013) go to the External Data tab…

Select Linked Table Manager…

Then select Select All then OK.  You can also do this from code…

Reconnect Attached tables on Start-up from Allen Browne’s Tips
Refresh Links from The Access Web

Date/Time surprises… (added 11.7.2015)

There are four types of Date fields in an SQL Server (all not available on earlier versions of SQL Server)…

• DATE
• DATETIME2
• DATETIME
• DATETIMEOFFSET
• SMALLDATETIME
• TIME

When upsizing your Tables to SQL Server they may convert as TEXT fields, this will depend how you upsize. Sure way to know whether this happened, short of going to look, is the Date Picker will not work. Should this happen you will have to go to your SQL Server tables and change the data type to DATETIME or DATETIME2 and then relink/refresh your Tables and the Date Picker will then work.

Some will say having a Time Stamp, aka Row Version Data Type on every Table with more than a few fields is a performance boost I have yet to see any performance boost or lack thereof if I do/don’t add one.

For more information…

The Access Date/Time data type supports a range of values from 1/1/100 through 12/31/9999 whereas the SQL Server DATETIME data type is limited in range to values from 1/1/1753 through 12/31/9999. SSMA will flag values that fall outside of the SQL Server DATETIME range in an assessment report. Note that SSMA can convert Access DATETIME to SQL Server DATETIME2, which was introduced in SQL Server 2008. However, the new date and time data types are not supported in Access if you use Access as a Frontend after converting your data. For more information, see Using Date and Time Data in SQL Server Books Online.

From SQL Server Migration Assistant (SSMA) Team’s Blog (Working with SQL Server datetime data ranges)

and…

Solving the Datetime Mystery

and posted in the Microsoft Answers Forum by Albert Kallal (Access MVP, 2003-2017) (added 7.22.2018)…

Datetime2 columns work without issue with Access.

However, you WILL have to link your tables using the SQL Native 11 (or later drivers). And you thus will have to install these drivers on each workstation.

If you link your tables using the SQL Driver (this is a older driver installed on all versions of windows by default). If you use the older ODBC driver, then datetime2 columns will be seen by Access as text columns.

So your choice is:

Always migrate using the older datetime format. You can change this setting in SSMA.

After you migrate simply use sql design tools (SSMS) and change the column type to datetime. If  you just have say a few tables, then this is not much work.

If you have a lot of tables, then I would consider adopting the native 11 (or later) drivers – that way you don’t have to change anything server side, and access will see + work with those types of columns correctly.

The newer drivers are better, and supposedly faster – but I not experienced performance increases.

So Access has no problem with datetime2, but you have to re-link your tables using a Native 11 or later driver for Access to see these columns correctly. About the only downside of this option is the noted require to ensure that these drivers are installed on any workstation you plan to run your application on .

Regards,
Albert D. Kallal (Access MVP, 2003-2017)
Edmonton, Alberta Canada

Tip!  If you need to store Time ONLY then be sure to select TIMEVALUE when setting up your tables in the SQL Server.

And finally… Hopefully, something in this article made your job easier.  Ready to try it for yourself?

SQL Server 2012 Express
SQL Server 2017 Express
Microsoft SQL Server Migration Assistant for Access v7.8 for Access
Creating and Managing Projects (AccessToSQL) (for SQL Server 2017)  (added 7.24.2018)

Using DSN-Less Connections by Doug Steele (Access MVP)  (added 12.5.2015)

Microsoft SQL Server Express: Version Comparison Matrix and Free Downloads (added 11.8.2015)
For the first time, all the different versions of SQL Server Express from 2005 to 2014 are shown, compared, and referenced with download links.

Automating the Backup of Your Microsoft SQL Server Express Databases (added 11.8.2015)
If you’re using SQL Server Express, you still need to create backups of the database. Here’s how to automate it which wasn’t easy to discover.

When and How to Upsize Microsoft Access Databases to SQL Server (added 11.8.2015)

7 Mistakes You Can’t Afford to Make When Upgrading to SQL 2012

This article is a *work in progress*, as I come across more tips and tricks for upsizing I will post them.  If you find a tip or trick you want me to add… Submit a Tip!

 2,631 total views,  1 views today