Issues in the life of an MS Access/SQL Developer


14/9/22 - True and False in SQL Server

In a recent conversion of an Access Database to a SQL database hosted in the cloud I had an issue with a bit data type.

This would have been a Yes/No field in MS Access.

It was a field to Block certain Clients from building mail merges but on occasion the user would like to see all clients including the blocked ones.

The query in Access to show Blocked clients used the criteria for the block field when it equals -1

The same query in SQL Server did not work because the Yes equivalent in a bit field is 1.

An Access Yes/No data type corresponds to the SQL Server BIT data type. In Access TRUE is -1 and FALSE is 0. In SQL Server, TRUE is 1 and FALSE is 0.

In Access, if you don't provide a default value for a Yes/No field, it is FALSE. In SQL Server the default value is NULL.

Therefore in SQL Server there are three states for a bit field


03/10/22 - Hiding the Navigation Window with VBA

It used to be called the Database Window then it was renamed the Navigation Window.

It is indispensable when building the database but you may wish to hide it or completely remove when the database is running.

This can be done using vba.


DoCmd.NavigateTo "acNavigationCategoryObjectType"



14/10/22 - Mail Merge from Sharepoint - Part 1

Many clients these days are moving from old style network servers and using a Cloud based servers, an O365 Office solution with Sharepoint.

One such client had a database used Word templates stored on the server. The database held the shared path location.

Files are synced into the OneDrive client, which means the local path changes with the user. The pathnames would be different for each user.

Attempts to point to Sharepoint location failed.

Mail merge is always a pain to set up and sometimes freezes without warning. The letter templates are useful yet can be moved or changed sometimes without the users knowledge.

The requirement needed to be in Word.

My approach to replacing this as quickly as possible with minimal disruption was to create the letters within Word and use VBA to create the letters.


14/10/22 - Mail Merge from Sharepoint - Part 2

The first step in creating a letter in Word from Access VBA is to open an instance of Word.

In this example my data is contained within a query. It is a table of selected clients with addresses.


Dim objWord As Word.Application
Dim doc As Word.Document
Dim WordHeaderFooter As HeaderFooter
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String

Set conn = CurrentProject.Connection
Set rst = New ADODB.Recordset

strSQL = "SELECT qryEmailLetter.*, * FROM qryEmailLetter"

Set objWord = CreateObject("Word.Application")

strTitle = "Letter" & Format$(Now, "hhnnss")

Use the CreateObject to open an instance of Word.

Also ensure that in the code references the correct version of Word is selected.

With objWord
 .Visible = True
 Set doc = .Documents.Add
 doc.SaveAs CurrentProject.Path & strTitle & ".doc"
End With

Create the document and then open the recordset to begin looping through the records

rst.Open strSQL, conn

If rst.EOF And rst.BOF Then

 Do Until rst.EOF

 strDate = Format$(Date, "dddd , dd mmmm yyyy")
 With objWord.Selection

  .Font.Name = "Times New Roman"
  .Font.Size = 12
  .ParagraphFormat.LineSpacingRule = wdLineSpaceSingle
  .ParagraphFormat.LineSpacing = 12
  .Paragraphs(1).Alignment = wdAlignParagraphRight
  .ParagraphFormat.SpaceBefore = 2
  .ParagraphFormat.SpaceAfter = 2

  .TypeText strDate & vbNewLine
  .TypeText "Member No:" & rst![Entity_ID]

  .Paragraphs(1).Alignment = wdAlignParagraphLeft
  .TypeText rst![Title] & " " & rst![Forename] & " " & rst![Surname]
  .TypeText rst![Address_Line_1] & vbCrLf
  If rst![Address_Line_2] > "" Then
   .TypeText rst![Address_Line_2] & vbCrLf
  End If
  If rst![Address_Line_3] > "" Then
   .TypeText rst![Address_Line_3] & vbCrLf
  End If
  If rst![Address_Line_4] > "" Then
   .TypeText rst![Address_Line_4] & vbCrLf
  End If
  If rst![Postcode] > "" Then
   .TypeText rst![Postcode] & vbCrLf
  End If




19/10/22 - 'ID' Is not an index in this table

This error was found in an Access front end database linked to a SQL Database on a local network.

The same database worked on other machines. All users have their own copy of the frontend.

When a repair was attemped the error table mentioned the table MsysStorage

It seems to be a corruption of sorts and was solved by repairing a copy of the database from a backup.


20/11/22 - Server Execution Error Word VBA

This is a strange error which occurs when Word is opened on just one machine.

It is due to Macros warnings as it is always accompanied when you switch to Word by a Macro warning.

Microsoft Office has identified a potential security issue. Microsoft has blocked macros from running because the source of this file is not trusted.

Setting Trusted Locations did not help. I read somewhere it could be a damaged profile and tried reinstalling Office also.

Finally, I stumbled across some code from Doug_Robbins_Word_MVP

The actual thread of the problem that was useful is here

This issue is caused when you use CreateObject to create an instance of Word when the Word application is already running.


Modify the code in your Access application so that it uses a construction of the following format to access the existing instance of Word if there is one.


                            Dim bstartApp As Boolean
                            On Error Resume Next
                            Set objWord = GetObject(, "Word.Application")
                            If Err Then
                                bstartApp = True
                                Set objWord = CreateObject("Word.Application")
                            End If
                            On Error GoTo 0

                            'Then, at the end of your code incorporate
                            If bstartApp = True Then
                            End If


This code prevented the issue. It is a quick fix until I can investigate further.