Exchange autodiscover

We’ve been having a couple of really annoying issues for awhile, but never often enough or serious enough for me to really dig into it. It’s slow today and it happened again so I was determined to figure it out. The solution was annoyingly simple.

Two issues:

1. When typing up an email to an internal user I would randomly get an error saying MailTips couldn’t be retrieved. It didn’t happen all the time though.
2. I have heard from a couple of people that when they tried to set their out of office response it wouldn’t let them. But again, it didn’t happen to everybody and was not always an issue for the two people I heard from.

It wasn’t until today when I got the MailTips error again that I realized the two problems were related.

I ran the Outlook tool to test e-mail autoconfiguration and every autodiscover test failed. I swear I ran that before we updated to Outlook 2016 from 2010 and it passed, but maybe I’m losing my mind.

Anyway, the solution was to create a cname record on the DNS (of course!) server. Works perfectly now.

Source: Setup a DNS record for Autodiscover

1.     On your remote desktop session, select Start / Programs / Administrative Tools / DNS.
2.     Right-click your domain name and select New Alias (CNAME).
3.     Under Alias Name enter: autodiscover
4.     Under Fully qualified domain name (FQDN) for target host enter: autodiscover.emailsrvr.com
5.     Click the Ok button.

Audit Trail – Access 2010 w/SQL Server backend

I don’t do these posts much anymore, but something I just got working this morning definitely warrants one.

I maintain a database here that has an Access 2010 front-end and SQL Server back-end. Since I had to rebuild the database from scratch and actually know how to maintain it I took a few database classes and got a certificate where I learned some SQL and basic database knowledge. Little did I know that I also should’ve learned visual basic. I’ve played around with Access in the past and used it for little personal databases, but I’ve never actually built and maintained one like this. I tried desperately to avoid VB since I’ve never even looked at it before and I’ve always hated programming. But there were times where I couldn’t avoid it, some things you just can’t do without it.

The major thing I’ve been working on this week is creating an Audit Trail so I have record of who, what, and when a person edits or deletes a record from the database. The first link I found was this one http://www.fontstuff.com/access/acctut21.htm. I created the table just as he described it. The first problem I had was, I’m guessing, because it isn’t an Access database – it has a SQL back-end. Again, I knew basically nothing about VB when I started this a few days ago. Running the code it wasn’t even finding the table so I had to use my google-fu to figure what wtf I needed to do. That led me to learning about connection strings.

So this is what the section of my code looks like before it actually gets to the auditing part:

Sub AuditChanges(IDField As String, UserAction As String, Optional DeletedID As String)
On Error GoTo AuditChanges_Err
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim ctl As Control
Dim datTimeCheck As Date
Dim strUserID As String
Set cnn = New ADODB.Connection
    cnn.Provider = “sqloledb”
    cnn.ConnectionString = “Driver={SQL Server Native Client 10.0};Server=[server IP Address];Database=[DB Name];Trusted_Connection=yes;”
    cnn.Open
Set rst = New ADODB.Recordset
rst.Open “SELECT * FROM tblAuditTrail”, cnn, adOpenDynamic, adLockOptimistic
datTimeCheck = Now()
strUserID = fGetUserName()

The sections in bold are what I changed or added to the original code. I already have a function in Access, that I found somewhere else online, that automatically enters the username of the person logged into the computer. That’s part of the code I use to track new entries. All the reading I did the last few days said something like that is better than using enviorn(“username”) and since I already had the function I just used it.

So getting that far and using the rest of his code exactly the same at least got it partially working. I was able to track edits with all the information I wanted. But deletes and additions were not being recorded properly. It would record when a new record was added and also deleted, but it wasn’t capturing the primary key value for some reason, which is the entire point. I really don’t understand why it wasn’t working, I downloaded his example DB and looked at it, still couldn’t figure it out.

More research led me to another forum where the person used Martin Green’s code, but had the same problems I was having. It’s the 5th post down the page, by johngachui. He changed some other things in the code that weren’t what I was interested in, so I just focused on the part about the deleted records. He added the DeletedID As String part that I added up above. I didn’t really care about recording additions either since I already do that another way so once I got the code functioning, I removed the parts about recording new records.

Here’s the functioning code as I need it, without recording additions:

Sub AuditChanges(IDField As String, UserAction As String, Optional DeletedID As String)
On Error GoTo AuditChanges_Err
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim ctl As Control
Dim datTimeCheck As Date
Dim strUserID As String
Set cnn = New ADODB.Connection
cnn.Provider = “sqloledb”
cnn.ConnectionString = “Driver={SQL Server Native Client 10.0};Server=10.55.12.4;Database=Deconfliction;Trusted_Connection=yes;”
cnn.Open
Set rst = New ADODB.Recordset
rst.Open “SELECT * FROM tblAuditTrail”, cnn, adOpenDynamic, adLockOptimistic
datTimeCheck = Now()
strUserID = fGetUserName()
Select Case UserAction
Case “EDIT”
For Each ctl In Screen.ActiveForm.Controls
If ctl.Tag = “Audit” Then
If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
With rst
.AddNew
![DateTime] = datTimeCheck
![UserName] = strUserID
![FormName] = Screen.ActiveForm.Name
![Action] = UserAction
![RecordID] = Screen.ActiveForm.Controls(IDField).Value
![FieldName] = ctl.ControlSource
![OldValue] = ctl.OldValue
![NewValue] = ctl.Value
.Update
End With
End If
End If
Next ctl
Case Else
With rst
.AddNew
![DateTime] = datTimeCheck
![UserName] = strUserID
![FormName] = Screen.ActiveForm.Name
![Action] = UserAction
![RecordID] = DeletedID
.Update
End With
End Select
AuditChanges_Exit:
On Error Resume Next
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
Exit Sub
AuditChanges_Err:
MsgBox Err.Description, vbCritical, “ERROR!”
Resume AuditChanges_Exit
End Sub

After making the changes in this code, The person in the 2nd forum also said to do things differently on the forms that call the auditchanges function and this is what I used:

Dim DelID As String

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not Me.NewRecord Then
Call AuditChanges(“cevid”, “EDIT”)
End If
End Sub

Private Sub Form_Delete(Cancel As Integer)
DelID = [cevid]
End Sub

Private Sub Form_AfterDelConfirm(Status As Integer)
If Status = acDeleteOK Then
Call AuditChanges(“cevid”, “DELETE”, DelID)
End Sub

Works great now, on the main form at least. I came across a ton of other forums where people reported issues with this not working on subforms because of the Screen.ActiveForm.Controls(IDField).Value part. I haven’t gotten that far yet. That is my next obstacle.

Sources:

• Add an Audit Trail to your Access Database
• Audit Trail – access-programmers.co.uk

Access 2010 set focus on subform control

Just a quick post because I know I’ll need this in the future. In our little database we have here in the office I was wanting a specific control in a subform to become active when someone clicks in any of the blank space on the main form. I couldn’t get the stupid thing to work until I found a forum saying you have to first reference the subform and then reference the control after it.

Not sure it’s your issue, but to set the focus to a subform control from a main form, you need both:

Me.subformcontrol.SetFocus
Me.subformcontrol.Form.somecontrol.SetFocus

I just kept trying to use the one statement on the bottom. Can’t believe I haven’t run into this until now, but there you go. It worked like a charm.

Source:
Access 2010 – Navigation Page, cannot set focus to “inside” form