{"id":371,"date":"2013-01-16T01:33:01","date_gmt":"2013-01-16T01:33:01","guid":{"rendered":"http:\/\/regina-whipp.com\/blog\/?p=371"},"modified":"2019-08-02T20:15:59","modified_gmt":"2019-08-03T00:15:59","slug":"upsize","status":"publish","type":"post","link":"https:\/\/regina-whipp.com\/blog\/?p=371","title":{"rendered":"So you’re ready to upsize…"},"content":{"rendered":"

Here are\u00a0a few tips when upsizing tables to an SQL Server that I have picked up along the way…<\/span><\/span><\/p>\n

Make a Back-Up of your Database…<\/span> <\/strong>(added 7.26.2013)<\/span><\/p>\n

Need I say more?<\/p>\n

Just say *NO* to<\/b>\u2026<\/span><\/p>\n

\u2026Special Characters or Symbols<\/span><\/span><\/b><\/p>\n

These characters have meaning when used in Access and on an SQL Server. \u00a0While wrapping your table\/column names in square brackets will *allow* it, it is best to avoid these symbols when name Objects or Fields\u2026<\/span><\/span><\/p>\n

\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Reserved Symbols<\/span><\/b><\/td>\n<\/tr>\n
.<\/span><\/td>\n<\/tr>\n
\/<\/span><\/td>\n<\/tr>\n
*<\/span><\/td>\n<\/tr>\n
;<\/span><\/td>\n<\/tr>\n
:<\/span><\/td>\n<\/tr>\n
!<\/span><\/td>\n<\/tr>\n
#<\/span><\/td>\n<\/tr>\n
&<\/span><\/td>\n<\/tr>\n
–<\/span><\/td>\n<\/tr>\n
?<\/span><\/td>\n<\/tr>\n
“<\/span><\/td>\n<\/tr>\n
‘<\/span><\/td>\n<\/tr>\n
$<\/span><\/td>\n<\/tr>\n
%<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

\u2026Spaces<\/span><\/b><\/span><\/p>\n

Like Special Characters and Symbols you will have to bracket any table\/field name that has a space.<\/span><\/span><\/p>\n

\u2026Words reserved for Access of SQL Server<\/span><\/b><\/span><\/p>\n

In Microsoft Access you can pretty much name your fields whatever you like.\u00a0 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.\u00a0 However, you may not realize you\u2019ve used a Reserved Word and will spend hours trying to figure out why your query isn\u2019t working, for example\u2026<\/span><\/span><\/p>\n

This individual could not figure out what was wrong with their statement.\u00a0 Several people chimed in to help and no one noticed the Reserved Word *Group<\/b>*\u2026<\/span><\/span><\/p>\n

\r\nstrSQL = "INSERT INTO Table101Contract(IDContract, Group) " &amp;amp; _\r\n          "SELECT" &amp;amp; lngIDContract &amp;amp; ", Group " &amp;amp; _\r\n           "FROM Table101Contract WHERE IDContract = " &amp;amp; varIDContract\r\n<\/pre>\n
Once *Group<\/b>* is bracketed the statement performs as it should…<\/span><\/span><\/p>\n
\r\nstrSQL = "INSERT INTO Table101Contract(IDContract, [Group]) " &amp;amp; _\r\n          "SELECT " &amp;amp; lngIDContract &amp;amp; ", [Group] " &amp;amp; _\r\n           "FROM Table101Contract WHERE IDContract = " &amp;amp; varIDContract\r\n<\/pre>\n
…however, avoiding the use of such words will save you lots of headache in the long run.<\/span><\/span><\/p>\n
Reserved Words for Microsoft Access and SQL Server<\/span><\/a>
\nAllen Browne\u2019s Complete List of Reserved Words<\/span><\/a>\u00a0 The most complete list on the Internet!
\n<\/span>SQL Server 2012 Reserved Words<\/span><\/a>
\nSQL Server 2008 R2 Reserved Words<\/span><\/a>
\nSQL Server 2005 Reserved Words<\/span><\/a>
\nSQL Server 2000 Reserved Words<\/span><\/a><\/p>\n
\u2026Look-Up Fields<\/span><\/span><\/b><\/p>\n
Lookup will not be upsized to SQL Server or any other database as they are exclusive to Access.\u00a0 In theory, it was a brilliant idea but in reality it\u2019s a short cut you need to avoid.\u00a0 And, they really don\u2019t do you any favors in Access; see <\/span>Why you should never use Look-Up Fields<\/span><\/a>.<\/span><\/span><\/p>\n
\u2026Multivalued fields<\/span><\/span><\/b><\/p>\n
Introduced in Access 2007 <\/span>complex columns that can record multiple values<\/a> my give you what you want in a regular database but it is not compatible with any other database.\u00a0 Instead use what I call look up tables<\/span><\/span><\/p>\n
\u2026<\/span>Hyperlink data type<\/span><\/a><\/b><\/p>\n
Introduced in Access 97, it will store paths to documents on your computer, web sites, network path but has no equivalent on SQL Server.<\/span><\/span><\/p>\n
\u2026Prefixing tables with Sys, USys, MSys or a tilde *~*<\/span><\/span><\/b><\/p>\n
This has only one real drawback…\u00a0 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 <\/span>Navigation Pane Options<\/span><\/a>.\u00a0 This little tip can be used to hide table(s) from the End User.<\/span><\/p>\n
When a table is deleted, Access will often keep it around, with a tilde prefix, until you *Compact & Repair* and then it is gone.<\/span><\/span><\/p>\n
\u2026Prefixing Queries with a tilde *~*<\/b><\/span><\/span><\/p>\n
See above\u2026<\/span><\/span><\/p>\n
...Yes\/No Data type (also known as the BIT data type)<\/strong><\/span><\/span><\/p>\n
Okay, so this one is not *that* important to not use but when upsizing\u2026<\/span><\/span><\/p>\n
The first thing to check is any and all *YesNo* fields in all tables. \u00a0They must be unchecked to not<\/b> allow NULLS and you must put in a Default Value of 0 (zero).\u00a0 AND make sure you have a Default Value in the field or it will fail when changing the Properties on the Server.\u00a0 Failing to do so will make the table un-updateable.<\/span><\/p>\n
\u00a0\"bit\"<\/span>\u00a0<\/span><\/p>\n
Another little tip about the BIT field\u2026\u00a0 SQL Server stores Yes as 1 and False as 0.\u00a0 While it will recognize True\/False, it will not return the correct results if you use -1\/0.\u00a0 So, when running your query against an SQL Server use\u2026<\/span><\/span><\/p>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 To find TRUE<\/b><\/span><\/span><\/p>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u2026WHERE YourField <> 0\u00a0\u00a0 \u2018True
\n<\/span><\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u2026WHERE YourField = True\u00a0\u00a0 \u2018True<\/span><\/span><\/p>\n
\u00a0<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 To find FALSE<\/b><\/span><\/span><\/p>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u2026WHERE YourField = 0\u00a0\u00a0 \u2018False
\n<\/span><\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u2026WHERE YourField = False\u00a0\u00a0 \u2018False<\/span><\/span><\/p>\n
\u2026Attachment Data Type <\/b>(added 7.27.2013)<\/span><\/p>\n
There is no *Attachment* data type in SQL Server.\u00a0 The only suggestion I can offer here is FILESTREAM\u00a0Storage<\/a>.<\/p>\n
Always use\u2026<\/span><\/b><\/span><\/p>\n
\u2026Primary Keys (PK)<\/b><\/span><\/span><\/p>\n
Make sure every table has a Primary Key whether you use or not.\u00a0 I use Autonumber.\u00a0 However, remember, this key is not guaranteed to be sequential so don\u2019t count on for anything.\u00a0 This Key should only<\/strong> be used as a unique identifier for the records in the tables.<\/span><\/span><\/p>\n
\u20264 digits for years<\/span><\/span><\/b><\/p>\n
You can show 2 digit years and even have your Users enter two digit for years but store 4 digits.<\/span><\/span><\/p>\n
\u2026a Naming Convention<\/span><\/span><\/b><\/p>\n
Decide on a Naming Convention… My Naming Conventions<\/span><\/a>\u00a0(and make sure you data is properly normalized.\u00a0 You don’t want to upsize only to find out your table structure needs to be redone!)<\/p>\n
Your VBA\u2026<\/span><\/b><\/span><\/p>\n
While most of your code will work fine, there are a few traps that will result in errors.\u00a0 When running and execution query against an SQL Server you will need to use dbSeeChanges or your queries will fail.\u00a0 The exception to that rule is if you are opening the query\/table in *Read Only* mode.<\/span><\/span><\/p>\n
Open for Read\/Write\u2026<\/span><\/span><\/b><\/p>\n
Set rst = dbs.OpenRecordset(“tblImport”, dbOpenDynaset, dbSeeChanges<\/b>)<\/span><\/span><\/p>\n
Set rst = dbs.OpenRecordset(“tblImport”, dbOpenDynaset, dbSeeChanges<\/b> + dbFailOnError)<\/span><\/span><\/p>\n
Open to run Action *Query*\u2026<\/span><\/span><\/b><\/p>\n
strSQL = “UPDATE\u2026
\n<\/span><\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CurrentDb.Execute strSQL, dbFailOnError + dbSeeChanges<\/b><\/span><\/span><\/p>\n
strSQL = “INSERT INTO\u2026
\n<\/span><\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CurrentDb.Execute strSQL, dbFailOnError + dbSeeChanges<\/b><\/span><\/span><\/p>\n
Open for Read Only\u2026<\/span><\/span><\/b><\/p>\n
Set rst = dbs.OpenRecordset(“tblImport”, dbOpenSnapshot)<\/span><\/span><\/p>\n
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.\u00a0 However, I have found that if you put\u2026<\/span><\/span><\/p>\n
\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>DoCmd.SetWarning False<\/span><\/span><\/p>\n
\u2026prior to your section of code, it will *fail* silently.\u00a0 So, if you are in the habit of using the above line then add\u2026<\/span><\/span><\/p>\n
\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span>DoCmd.SetWarning True<\/span><\/span><\/p>\n
\u2026before your strSQL line.<\/span><\/span><\/p>\n
HUH?…<\/strong><\/span><\/p>\n
In Access, AutoNumbered fields are visible as soon as the record is added even if it hasn\u2019t been saved yet.\u00a0 That doesn\u2019t work for SQL Server as AutoNumbered\u00a0fields are only visible after the records have been saved.\u00a0 So, you would need something like…<\/p>\n
In DAO…<\/strong><\/span><\/p>\n
…
\nrs.AddNew
\n…
\nrs.Update
\nrs.Move\u00a00,\u00a0rs.LastModified
\nlngNewID\u00a0=\u00a0rs!ID
\n…<\/p>\n
…to retreive the Autonumber.<\/p>\n
text\/ntext are deprecated\u2026 <\/b>(added 1.19.2013)<\/span><\/span><\/p>\n
In SQL Server 2005 I use to use *ntext<\/strong>* in place of a *MEMO<\/strong>* field data type in Access.\u00a0 Well, those are gone use varchar(MAX)\/nvarchar(MAX) instead.\u00a0 (Thanks Ben Clothier, Access MVP,\u00a0for pointing that out!)\u00a0 For a complete list of what’s gone and its replacement, if it has one, see Deprecated Database Engine Features in SQL Server 2008<\/a> and Deprecated Database Engine Features in SQL Server 2012<\/a><\/p>\n
Removing the *dbo_* prefix on linked tables\u2026 <\/b><\/span>(added 7.23.2013)<\/span><\/p>\n
After linking you’re now left with Linked Tables in Access that are prefixed with, typically, dbo_.\u00a0 (This is because SQL Server uses Schemas.)\u00a0 This is a problem for all your Queries, Forms, Reports, VBA, etc…\u00a0 Let’s not panic, no need to fix *everything<\/strong>* in your database.\u00a0 Simply, copy\/paste the below into a Module (either a new one or… I keep this code in modUtilities)…\u00a0 <\/span><\/p>\n
\r\nFunction FixTableNames(strPrefix As String)\r\n'?FixTableNames("dbo_")\r\n\r\n     Dim db As Database\r\n     Dim tdf As TableDef\r\n\r\n     Set db = CurrentDb\r\n     For Each tdf In db.TableDefs\r\n        If Left(tdf.Name, Len(strPrefix)) = strPrefix Then\r\n          DoCmd.Rename Mid(tdf.Name, Len(strPrefix) + 1), acTable, tdf.Name\r\n        End If\r\n     Next\r\n\r\n     Set tdf = Nothing\r\n     Set db = Nothing\r\n\r\nEnd Function\r\n<\/pre>\n
Press Ctrl+G on your keyboard to bring up the Immediate window and type…<\/p>\n
?FixTableNames(“dbo_”)<\/strong><\/p>\n
…click Yes (or OK) while the vcode loops thru your Table Objects removing the *dbo_<\/strong>* prefix.<\/span><\/p>\n
Validation Rules (Table Level)…<\/strong><\/span> (added 7.23.2013)<\/span><\/p>\n
…are converted to Table Constraints.\u00a0 This may cause a problem when trying to add new records and\/or may not Upsize correctly or properly.\u00a0 (I do my Feild Validation at the Form level.)\u00a0 For more information see Incompatible Access Features (Access to SQL)<\/a>.<\/p>\n
Made changes?\u00a0 Refresh your\u00a0links\u2026 <\/b><\/span>(added 9.17.2013)<\/span><\/p>\n
You’ve made some changes to your Tables and now you can’t enter anything, what gives?\u00a0 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.<\/p>\n
Access 2010 (and Access 2007 and Access 2013) go to the External Data<\/strong> tab…<\/p>\n
\"External
External Data Tab<\/p><\/div>\n
Select Linked Table Manager<\/strong>…<\/p>\n
\"Linked
Linked Table Manager<\/p><\/div>\n
Then select Select All<\/strong> then OK<\/strong>.\u00a0 You can also do this from code…<\/p>\n
Reconnect Attached tables on Start-up<\/a> from Allen Browne’s Tips<\/a>
\nRefresh Links<\/a> from The Access Web<\/a><\/p>\n
Date\/Time surprises\u2026 <\/b><\/span>(added 11.7.2015)<\/span><\/p>\n
There are four types of Date fields in an SQL Server (all not available on earlier versions of SQL Server)\u2026<\/p>\n