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.
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.