MS Access Error 3283 Primary key already exists

Source: FIXED: Error 3283 Refresh Links (isladogs.co.uk) - and - RefreshLink fails with "Primary Key already exists" (accessforever.org) -  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
				tblDef.RefreshLink
			
				' 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
														
				Err.Clear
				On Error GoTo ErrorHandler
				
			End If
		End If
		
	End If
Next tblDef

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

MS Access Dynamic Report Sorting

I came across a request from a client who wanted the sorting from a Form to be transferred to a Report. Meaning that whatever was sorted for the Data Sheet view in a sub-form, to be transferred to the Report. This was a task that became almost impossible until I discovered a few things.

  1. You cannot sort a Report Query and expect it to sort in a Report. It will not work.
  2. You have to pass in the Forms Order By property, or update a global variable to store the sorting.
  3. There is manual VB work involved.
  4. There is also dummy Groups that are needed in the report that have to be updated when the Report is opened.

This is how I did it. I created a global variable to hold the Order By string from the Sub form. This variable was in a module, any module

Public g_strJobCostSort As String

I then wrote this code for opening the Report. Note that I had to remove certain sections of the Order String because of control names like Lookup_cboVendor, which is a dropdown in the sub-form.

    Dim strSQLSort As String
    
    If Nz(Me.subfrmJobCostItems.Form.OrderBy, "") <> "" Then
        strSQLSort = Replace(Replace(Me.subfrmJobCostItems.Form.OrderBy, "[qryJobCostItems].", ""), "[Lookup_cboVendor].[Company]", "[CompanyName]")
        g_strJobCostSort = strSQLSort
        DoEvents
    End If

    DoCmd.OpenReport "rptJobCostingCombined", acPreview, , "JobCostID=" & Me.txtJobCostID

I also created 2 buttons on the Sub-Form so the user could see the existing Sort Order and also clear it. One thing worth noting too is that the last column you sort on is actually the first column to be sorted on. It makes sense but you might need to try this is order for it to make any sense to you.

The Sorting button runs this code and the following dialog is shown.

MsgBox Replace(Replace(Me.subfrmJobCostItems.Form.OrderBy, "[qryJobCostItems].", ""), "[Lookup_cboVendor].[Company]", "[CompanyName]"), vbInformation, "Sorting"

The Clear Sorting code is this

    Me.subfrmJobCostItems.Form.OrderBy = ""
    Me.subfrmJobCostItems.Form.Requery

The Sub Report has the following Group On and Sort By properties/sections. I only added 4 Sort By sections but you can have as much as you like I guess. I noticed too that if I added a Group On but did not have a Group Header or Group Footer that it switched to a Sort by section. That's fine as it working the same way.

 This is the Code that I added to the Report_Open event in the subform. Note that I added " DESC" with a space at the beginning to make sure it was targeting the DESC sort order at the end, if there was one. If your columns have this combination, like Description, then you'll need to check that this code does not affect them. Basically this code gets all the sort columns, even if they're blank! This is why I chose to do On Error Resume Next. It's not the most glamorous way code, but it works and it will sort on the "up to" 4 sections that I added.

    Dim strSorting() As String
    strSorting = Split(g_strJobCostSort, ",")
    
    On Error Resume Next
    Me.GroupLevel(1).ControlSource = Replace(Replace(Replace(Replace(Trim(strSorting(0)), " DESC", ""), "]", ""), " ", ""), "[", "")
    Me.GroupLevel(2).ControlSource = Replace(Replace(Replace(Replace(Trim(strSorting(1)), " DESC", ""), "]", ""), " ", ""), "[", "")
    Me.GroupLevel(3).ControlSource = Replace(Replace(Replace(Replace(Trim(strSorting(2)), " DESC", ""), "]", ""), " ", ""), "[", "")
    Me.GroupLevel(4).ControlSource = Replace(Replace(Replace(Replace(Trim(strSorting(3)), " DESC", ""), "]", ""), " ", ""), "[", "")
    DoEvents
    
    If InStr(strSorting(0), "DESC") > 0 Then
        Me.GroupLevel(1).SortOrder = True
    End If
    
    If InStr(strSorting(1), "DESC") > 0 Then
        Me.GroupLevel(2).SortOrder = True
    End If
    
    If InStr(strSorting(2), "DESC") > 0 Then
        Me.GroupLevel(3).SortOrder = True
    End If
    
    If InStr(strSorting(3), "DESC") > 0 Then
        Me.GroupLevel(4).SortOrder = True
    End If

Just as a courtesy, I have add the following links that helped me get to this solution after about 8 hours of trying. 

https://stackoverflow.com/questions/18836199/access-2010-vba-to-sort-a-report-with-existing-grouping/51014170#51014170?newreg=168265e41fd044cea63b1817e09e86ea

https://stackoverflow.com/questions/40402/what-is-the-command-to-truncate-a-sql-server-log-file

https://accessexperts.com/blog/2011/07/15/dynamically-sorting-forms-and-reports/

http://allenbrowne.com/ser-33.html - Allen Browne from Perth, Australia, is a legend!

Happy Coding, Coders!

 

Get the Full Build and Version of MS Access in VBA

To get the Full "Build" of MS Access in VBA use the following in a function.

CreateObject(“Scripting.FileSystemObject”).GetFileVersion(SysCmd(acSysCmdAccessDir) & “\msaccess.exe”)

To Get the "Version" you have to look it up here from the Build Number: https://learn.microsoft.com/en-us/officeupdates/update-history-microsoft365-apps-by-date. There is currently no way to look this up in VBA. You'll need to use some HTML screen scraping to get this number from this URL.

To get the Bit you'll need to use a function like this.

Public Function IsOfficex64() As Boolean
    ' Test whether you are using the 64-bit version of Office.
    #If Win64 Then
       IsOfficex64 = True
    #Else
       IsOfficex64 = False
    #End If
End Function