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.

Microsoft 365 Updates

If you have any Microsoft 365 App issues then check this site to see if there have been any updates https://learn.microsoft.com/en-us/officeupdates/update-history-microsoft365-apps-by-date

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!

How do you import a large MS SQL .sql file?

If you have a very large file that was created by using Generate Script (Data Only) via SQL Server Management Studio (SSMS) you will probably not be able to load it in SSMS to run it. The solution is to use the Command Prompt as follows.

sqlcmd -S <Server>\<Instance> -i <Full Path of .sql File> -I

The -I at the end is used when there is Quoted Identifiers, which is just about all the time!

Other Command Line Parameters that might be of use.

Sqlcmd            [-U login id]          [-P password]
  [-S server]            [-H hostname]          [-E trusted connection]
  [-d use database name] [-l login timeout]     [-t query timeout] 
  [-h headers]           [-s colseparator]      [-w screen width]
  [-a packetsize]        [-e echo input]        [-I Enable Quoted Identifiers]
  [-c cmdend]            [-L[c] list servers[clean output]]
  [-q "cmdline query"]   [-Q "cmdline query" and exit] 
  [-m errorlevel]        [-V severitylevel]     [-W remove trailing spaces]
  [-u unicode output]    [-r[0|1] msgs to stderr]
  [-i inputfile]         [-o outputfile]        [-z new password]
  [-f  | i:[,o:]] [-Z new password and exit] 
  [-k[1|2] remove[replace] control characters]
  [-y variable length type display width]
  [-Y fixed length type display width]
  [-p[1] print statistics[colon format]]
  [-R use client regional setting]
  [-b On error batch abort]
  [-v var = "value"...]  [-A dedicated admin connection]
  [-X[1] disable commands, startup script, environment variables [and exit]]
  [-x disable variable substitution]
  [-? show syntax summary] 

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
    Me.lstListControl.Requery

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!
        rstQueryWithParameters.MoveNext
    Loop

 

SSRS in Visual Studio 2022 (and 2019)

Thanks to Daniel Calbimonte who posted this article hereSSRS in Visual Studio – SQLServerCentral

Where are the SSRS Projects in Visual Studio?

Where is SSRS in Visual Studio 2022? Traditionally, you need to install the SSDT and check SSRS to have it installed. However, things have changed in SSRS over the years. This article explains how to install SSRS in VS 2022.

2017 and 2019 Extensions: Microsoft RDLC Report Designer and Microsoft Reporting Services Projects

 SSRS History

A long, long time ago, the SQL Server installer included an option to install the BIDS (Business Intelligence Development Studio) including Reporting Services. In SQL Server 2012, the BIDS were named SSDT. The SSDT used the version of Visual Studio. It was a VS extension to handle projects like SQL Server Projects, SSIS, SSAS, and SSRS projects.

In SQL 2014 you had to install SSDT from the website and it was no longer in the SQL Server installer. There were SSDT versions for VS 2012, 2013, 2015, and 2017. And then in VS 2019 the SSDT did not include an installer for SSRS. So that is why we have this article. To help you to install the extension for Visual Studio 2022.

The SSRS extension for VS 2022 is available here: https://marketplace.visualstudio.com/items?itemName=ProBITools.MicrosoftReportProjectsforVisualStudio2022&ssr=false#review-details

Getting started with SSRS in Visual Studio

First, I am assuming that you already have Visual Studio 2022. Secondly, in Windows Search, look for the Visual Studio Installer and open it.

Thirdly, look for the Data Storage and processing workload and check SQL Server Data Tools.

Once installed you will notice that you don’t have Reporting Service, Analysis Service, and Integration Services projects.

Why is it getting more difficult to install?

The humble opinion of this writer is that Microsoft wants us to move to Azure technologies like Synapse, Power BI, Azure Data Factory. However, in general, we can say that Microsoft tries to handle VS extension as separated projects in a generic way. I mean, all the extensions should be installed in a standard way.

Installing SSRS Projects in Visual Studio

In order to create your SSRS projects, you will need to go to extensions in the VS menu. Secondly, go to Extensions>Manage Extensions

Thirdly, in the search textbox, search for Reporting. You will see Microsoft Reporting Services Projects. Check the reporting services to install it.

Testing the installation

Firstly, in Visual Studio, go to File>New>Project

Secondly, if you are using it for the first time and you have several types of projects installed, search for reports and you will see 2 options.

  • The Report Server Project Wizard is the easiest way for newbies to create a report from scratch without too much knowledge in SSRS.
  • Report Server Project is used if you have some experience creating reports to create your own report. You could invoke the Wizard here if you need to.

Finally, specify a Report name and a location.

Conclusion

SQL Server Reporting Services is in SQL Server, but the Projects in Visual Studio are extensions. In order to install the extension, you need to install the SSDT first with the Visual Studio Installer, and then in the Visual Studio go to Extensions to install the SSRS extension.

The Analysis Services projects and Integration Services projects have a similar approach. You need to install them separately.

Resolve Windows Update Error “Code 80072EFE” on a Windows Server 2008 R2 SP1 (x64) built from the DVD ISO with release date February 23, 2011

I had to recently set up a Windows 2008 R2 VM on Hyper-V so I could test switching domains and came across this error.

Wim Matthyssen wrote this great blog to help resolve the issue. I would not have figured this out otherwise!

Resolve Windows Update Error “Code 80072EFE” on a Windows Server 2008 R2 SP1 (x64) built from the DVD ISO with release date February 23, 2011 – Wim Matthyssen (wmatthyssen.com)

 

Resolve Windows Updates error with “Code 800272EFE

 To resolve this error, follow a few simple steps.

If you’re using an even older version of the ISO, start by installing Windows Server 2008 R2 Service Pack 1 for x64-based Systems (KB976932). You can download it using this link: https://catalog.s.download.windowsupdate.com/msdownload/update/software/svpk/2011/02/windows6.1-kb976932-x64_74865ef2562006e51d7f9333b4a8d45b7a749dab.exe

 Once installed, or if you utilized the W2K8 R2 DVD ISO released on February 23, 2011, you can move on to the next step.

Then, to restore Windows Update functionality, you’ll need to install several updates. Below, you’ll find a list of these updates along with their download links from the Microsoft Update Catalog:

Download all these updates and save them in a shared folder accessible from the W2K8 R2 server, or store them locally on the server itself.

Then, install these updates in the following order: first, KB4474419; then KB4490628After installing the second update, reboot the server.

 Once the server is restarted, install the update, KB4570673, and finally, KB4534310. After the installation of the last one, reboot the server once more.

Once the server has rebooted for the second time and all the updates are installed, Windows Update should function normally again. When you begin updating, be prepared for quite a lengthy list of updates that need to be installed, and these updates will also require a few reboots.

Conclusion

To resolve the Windows Update Error “Code 80072EFE” on a Windows Server 2008 R2 SP1 (x64) server built from the DVD ISO released on February 23, 2011, it’s necessary to install several updates in a specific order.

I hope that the update steps outlined in this blog post will prove helpful in fixing this error for you.

If you have any questions or suggestions regarding this blog post, please feel free to reach out to me via my X handle (@wmatthyssen) or leave a comment. I’m more than happy to assist.

The Specified Domain Either Does Not Exist or Could Not Be Contacted

When you try to connect to join a domain on any Windows operating system or after entering a username and correct password, you may receive an error with the message “the specified domain either does not exist or could not be contacted”.

This “the specified domain does not exist or could not be contacted” error is usually due to invalid DNS settings on the workstation, as Active Directory requires domain DNS (not the router’s address) to function properly. The error can also occur because IPv6 does not work.

Go to Control Panel. Network and Sharing Center - Chang adapter settings - Properties and uncheck IPv6. Note that in Windows 11 this is the ONLY way to disable IPv6!

You're Very Welcome :-)

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

 

MMC has detected an error in a snap-in. It is recommended that you shut down and restart MMC

We found that doing the following worked, and can now edit the settings in the II6 Manager without errors (and without crashing when applying settings) every time:

  1. Stop SMTPSVC service [Display Name: Simple Mail Transfer Protocol (SMTP)]
  2. Stop IISADMIN service [Display name: IIS Admin Service]
  3. Edit "C:\Windows\System32\inetsrv\MetaBase.xml"
  4. Find: <IIsSmtpServer Location ="/LM/SmtpSvc/1"
  5. Add (Settings are alphabetical): RelayIpList=""
  6. Save file
  7. Start IISAdmin Service
  8. Start SMTPSVC service

You can now open the Internet Information Services (IIS) 6.0 Manager (InetMgr6.exe) and work like normal. The setting above relates to the Access Tab > Relay Restrictions section, but doesn't set anything in it (i.e. "all except the below list" with a blank list).

After setting this value you can change it to what you need it to be. (i.e. the value doesn't need to be 'null' for the fix to work, just so long as the variable is present in the file).

The above has been tested on a few new installs of Windows Server 2022, and has worked for us every time. If you have done an upgrade there are some other things to consider (seems like the SMTP service is not installed after upgrade so will require a backup of the settings that can be applied post reinstalling the service. But that is discussed in other topics).

PS: Remember to set the SMTPSVC service to start "automatically". By default its set to "manual".

Also Here: How do I resolve the "Snapin Error" when setting up SMTP on IIS Server? - Server Fault