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"
DoCmd.Minimize

 

 

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

Else
  rst.MoveFirst
 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
  .TypeParagraph
  .Paragraphs(1).Alignment = wdAlignParagraphRight
  .ParagraphFormat.SpaceBefore = 2
  .ParagraphFormat.SpaceAfter = 2

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

  .TypeParagraph
  .Paragraphs(1).Alignment = wdAlignParagraphLeft
  .TypeText rst![Title] & " " & rst![Forename] & " " & rst![Surname]
  .TypeParagraph
  .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
                                objWord.Quit
                            End If

 

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

 

3/5/23 - Splitting Firstname and Surname

This is a very simple function that I often use as an example of how using a logical approach to a problem is the root of programming.

We have a name where First name and Surname are combined eg/ Joe Bloggs.

We need to find what is common with any combined name and indicates where the name splits.

Locate the blank character with the function. InStr() Short for In String

Instr("joe Bloggs"," ")

Returns a value 4. The fourth character is blank.

Left$("Joe Bloggs",Instr("joe Bloggs"," ")-1)

Left$ takes the left portion of the string up to character 4. This includes the blank space so we need to subtract 1 to just capture the first name.

 

9/10/23 - MDE is 32bit

Most clients now run 64-bit versions of Office

If a client does not want to buy a full version of MSAccess I use Access Runtime.

When I recently came to install a 32 bit version of Access Runtime in a 64-bit Office environment it doesn't work. Nor can you open the old runtime version with Office 64 bit

This is why keeping the original source database is important. I recompiled original file into 64 bit

The database was also contained the data. Fortunately the runtime version contained a backup feature that created a copy of the database tables in a separate db.

I was able to copy the tables into the original database before creating the MDE.

 

10/10/23 - Setting Trusted Location on Access Runtime

My database created in Access Runtime was not able to open due to Macro warnings.

You have to add the Trusted Locations into the Registry.

The below site was very helpful in guiding me.

Trusted Locations

 

12/12/23 - Grouping on Report not appearing

In a report design a field in the underlying table or query does not appear as a Grouping option

This proved very puzzling but it turns out that if a field is a Memo field it cannot be used for Grouping

The field was from a SQL database so rather than change the source data a little trick is to use Left$() to limit the size of the field.

 

20/12/23 - Recordset Error 2147217887 SQL Database with MS Access Frontend

A frontend database contained an ADO recordset which updated or added a new record into a sql Server backend database had the error number 2147217887

It turned out to be due to a bit field in the SQL Server database that could not contain a null value.

I simply defaulted the bit field in the AddNew routine to False.

A good way to spot this error is to try an add the data manually.