MS Access Error 3283 Primary key already exists

Source: FIXED: Error 3283 Refresh Links ( - and - RefreshLink fails with "Primary Key already exists" ( -  Thanks!

I came across error 3283 - Primary key already exists after the MS Office system had been updated to version 2312 Build 16.0.17126.20190 for Monthly Enterprise  Channel.

The error happens when calling RefreshLink as shown in the code sample below. Disclaimer: This worked for me, but your solution might be different. The solution "for now" is to use On Error Resume Next and deal with the error afterwards as the table does in fact relink just fine. Microsoft say that a fix will not be released until March 12th 2024 in version 2401. So you will need to implement this work around until it's resolved.

For Each tblDef In tblDefs
	If tblDef.Connect <> "" Then
		If InStr(1, tblDef.Connect, "ODBC") > 0 Then
			'SQL Server Database
			If (tblDef.Connect <> strSQLConnectionString) Or blnForceReconnect Then
				tblDef.Connect = strSQLConnectionString
				' Error 3283 when refreshing links using code caused by MS Office Update 2312 Build 16.0.1716.20190
				' A fix will be applied by Microsoft on March 12th 2024 Office Updates for build 2401.
				On Error Resume Next
				' This is required if the SQL View is to be update-able
				If tblDef.Name = "vw_Example" Then
					CurrentDb.Execute "CREATE UNIQUE INDEX __uniqueindex ON " & tblDef.Name & " (ID ASC)"
				End If
				On Error GoTo ErrorHandler
			End If
		End If
	End If
Next tblDef

Thanks to Isladogs on Access and Access Forever for this post.

Microsoft 365 Updates

If you have any Microsoft 365 App issues then check this site to see if there have been any updates

More than likely there has been an update and you might have to roll back the update to the last version or 2 previous versions ago.

One recent example was a January 4th, 2024 update (20126) that broke MS Access when tables are connected to SQL Server. The connection string was missing a semi-colon. An update was released on January 9th, 2024 (20132), but it still did not fix the issue (You can open a linked SQL table but you can't go to the last record.) so users had to go back to the December 12th, 2023 (20108) update!

MS Access Query/Recordset with Parameters

This is how to use a query in code with parameters for a Control as well as manipulate it for a Recordset. Note that you need to leave the Controls Row Source blank so that you don't see the Parameter prompts!

    Dim qryDef As DAO.QueryDef
    Set qryDef = CurrentDb.QueryDefs("qryQueryWithParameters")
    qryDef.Parameters("[ParameterA]") = intAID
    qryDef.Parameters("[ParameterB]") = intBID
    Set Me.lstListControl.Recordset = qryDef.OpenRecordset

You can declare the Parameters in the Query itself. I did this using square brackets around them and did the same in the query Criteria as well. This way the query does not think you're using literal Text as it will try and put "double quotes" around them if you don't use [square brackets]!

You can also manipulate the same query in a Recordset in order to iterate the row if you need to do that!

    Dim rstQueryWithParameters As DAO.Recordset
    Set rstQueryWithParameters = Me.lstListControl.Recordset
    Do Until rstQueryWithParameters.EOF
        ' Write iterative Code!


Dynamically hide a column in MS Access

This is how to dynamically hide a column in MS Access.

Private Sub txtBudgetAmountLocked_Click()
    If Not IsUserReadOnly Or Not IsProjectClosed(g_lngProjectID) Then
        If Me.Parent.chkBudgetLocked = 0 Then
            Me.txtBudgetAmount.ColumnHidden = False
            Me.txtBudgetAmountLocked.ColumnHidden = True
            If DLookup("HasHeaders", "qryProjectReport", "ID=" & Me.Parent.txtID) = "Yes" Then
                Me.Parent.cmdShowTotals.Visible = True
            End If
        End If
    End If
End Sub


Is MS Access Compiled

Public Function IsACCDE() As Boolean
    ' This property exists only in compiled DBs (.mde, .accde)!
    On Error Resume Next
    IsACCDE = (CurrentDb.Properties("MDE") = "T")
End Function

This code returns True if MS Access has been saved as ACCDE and False it it has not. This is different than the built in IsCompilled() function that return True or False based on the actual code being compiled or not.

Why can't Excel open a file when run from task scheduler?

From here:

1. Open Component Services (Start -> Run, type in dcomcnfg)
2. Drill down to Component Services -> Computers -> My Computer and click on DCOM Config
3. Right-click on Microsoft Excel Application and choose Properties
4. In the Identity tab select This User and enter the ID and password of an interactive user account (domain or local) and click Ok
Keeping it as the interactive user or the launching doesn't work with the task scheduler unfortunately, even when setting the task up to run under an account that has admin access to the machine.


MS Access Subreport Header on subsequent pages

From here:
In the Subreport do the following and you will have the Report header on subsequent pages.

1. Click on "Add a Group"
2. Select "Expression"
3. When the Expression Window appears, type in "=0" and click OK
4. Make this group only have a header section
5. Click on the group's header section
6. Go to Property Sheet and make sure "Repeat Section" is set to "Yes"