Bug: Error 3283 Primary key already exists #484
Comments
|
Thanks for reporting this! Can you try checking the option to debug VBA Errors, and let me know where the error occurs? It might help us see exactly which line is failing, which could give us some further clues...
Also make sure you have done a full export from the version you are trying to build with. (I assume you probably did, but just in case. 😄) |
|
I had not done a full export after updating to the beta version. I'm still getting the error messages on build after the full export. Working backwards on the call stack: |
|
Thanks for the screen shots! That is very helpful. It seems that the issue is related to attempting to create an (unneeded or duplicate?) primary index on the linked table. (Or linked view.)
Is this on a specific SQL table or view that you are linking to? If so, would you be able to post some details on the composition of the index, and how this is reflected in the exported source file? (You don't need to post any sensitive connection information, but the index details may be helpful in pinpointing the problem.) You mentioned that the error is logged for every linked table, so perhaps there is something similar with all of them. (Perhaps composite keys, or something like that?) I am pretty confident we should be able to resolve this, it is just a matter of pinpointing exactly where the error is thrown, and determining how to handle the situation. I have access to a SQL server, so I may be able to replicate the issue if I have enough details, or perhaps a SQL script to build a sample table. |
|
It is every table but not any of the views. Here is an excerpt from the build log. The first two tables are local Access tables. Here is the json file for the first table (connect string is cleared out) |
|
I have a primary key added to some of my views, but these don't have an error. |
|
Thanks! On the SQL server side, would it be easy to generate a build script for the table? That would have any additional attribute/index details that might be pertinent here... (That would allow me to use the same script to generate an identical table on my SQL server to see if I can reproduce the issue.)
|
|
FWIW, because as noted earlier, the error is likely from previous operation, I wonder if the problem may be with the attributes. I'm aware that when creating a new |
|
I don't know if this is helpful at all, but I ran these in the immediatewindow from the ImportLinkedTable function |
|
I commented out the On Error Resume Next in the ImportLinkedTable and pressed F5 to continue. Here is where it stopped next, with the Primary Key already exists error. In the Build sub. |
|
I uncommented the On Error Resume Next line in ImportLinkedTable and stepped through each line, checking the err.number in the immediate window. The error shows up after running tdf.RefreshLink. (I moved the arrow back to highlight that line after running that line) |
|
Could this possibly be an issue with a recent Access update (bug)? https://learn.microsoft.com/en-us/answers/questions/1486159/when-i-run-refreshlink-i-get-an-error-saying-prima |
|
I used the SQL script (with a few modifications) to add one of the tables to a SQL server, but I could not reproduce the issue in Access 2010. (The export and build worked as expected.) That does make me curious to know if this a bug in a recent update for Microsoft Access... In any case, I will add some error handling around the linking. |
This could be related to a recent Access bug, but it is helpful to trap the error if it occurs. #484
|
This is my version: Microsoft® Access® for Microsoft 365 MSO (Version 2401 Build 16.0.17231.20084) 64-bit |
|
Do you get any error if you refresh the link using the linked table manager? (In theory, that would be the same thing that we are doing when we call
Just trying to think of ways to drill down into the issue... |
* Updating clsPerformance, as some objects never restart timing, and when resetting some objects are not cleared. Fixes joyfullservice#331 * Fixing Private/Public declarations. * This isn't actually used. * Bump Version * Update API examples Removed dependency on an external function, and added an example for building from source. * Refine some dialect-specific SQL string quotations Backticks only apply to MySQL, while square brackets are used with MSSQL and Access. joyfullservice#442 * Allow wrapping of long names in performance class Extending the performance class to allow the wrapping of long names used for categories or operations. (Not really needed within this project, but could potentially be helpful in the future with translations.) joyfullservice#441 * Update based on feedback from @joyfullservice. * Resolve conflict with upstream file Putting the comma after the argument seems to be the preferred industry-standard approach, based on ChatGPT and Bard. * Add option to pass path to build API You can now specify the source files path when you initiate a build through the API. This allows automated builds to be run even if a copy of the database does not yet exist. (Such as after checking out a project in an automated CI workflow.) joyfullservice#430 * The logic for checking of existence of git files wasn't always working as expected due to searching the current directory rather than using the export folder. * Add a check when loading XML and verify it was successfully parsed. This avoid generating a bad export where the data are not actually exported due to invalid XML being generated by Application.ExportXML. Unfortunately, if a table contains any characters that aren't valid for XML document, it won't try to escape them and include them as literals. Even if they were escaped, they might not be accepted anyway. XML specifications forbids having any characters in 0x01-0x31 range so if a table data contains such characters, this can cause the XML export to fail. In this case, tab delimited will have to be used instead. However, the previous version was simply silently exporting as if everything is hunky-dory when it's not. Hence, the error. * The export log was littered with bunch of warnings about unclosed blocks. This seems to be due to not closing it when evaluating the UseTheme. Even if we skipped it, we still need to remove it from m_colBlocks to balance everything out. * Fix a subscript out of range error where the tokens advance beyond the end of the string but the function GetNextTokenID returns 0, which then fails within FormatSQL function since there is no member at index 0. It's not clear why this only fails every second time a query is exported but it is the case where if it fails, exporting it next time will not yield the error. Do it 3rd time, then it fails. * Add more types of queries that should not be formatted by SQL formatter because they are a variant of pass-through queries. * The AutoClose may run after the form has closed (e.g. if the user is quick to close it) which may result in an error about object members not available. Since the form is closed, there's no point in setting the timer interval. To avoid the error when debugging, we add a IsLoaded check and skip it if it's not loaded. * Fix issue with LogUnhandledErrors and simplify use. (joyfullservice#449) * Add option to SplitLayoutFromVBA This option (on by default) will save the VBA code from forms and reports as a related .cls file. (Still under development.) joyfullservice#378 Also removed the "Strip out Publish Option" from the options form. I have never heard of a case where this needs to be changed, and it frees up space for the new option we are adding without cluttering the form. * Refactor code module export to shared function This logic will be shared when exporting code modules from forms and reports. * Support "|" in performance log entry names Refactored parsing the key from the performance item so that we are not dependent upon a unique delimiter. The timing value is always a number, so we can be confident that the first pipe character is the delimiter. The text after that can be anything, including pipe characters. joyfullservice#450 * Adjust indenting (minor change) * Convert Sanitize module to class In some cases sanitizing a source file actually creates two distinct outputs. A layout file and a code file. Rather than making the sanitize function more complicated with byref outputs and non-obvious side effects, I am taking the approach of a more explicit object-oriented route where the code is easier to understand and maintain. (And also allows for future enhancements such as SQL extraction for query definition files.) * Refactor sanitizing to use class Updating the existing code to use the new class. * Refactor class variables * Refactor form/report export to split VBA Export is now splitting the VBA from Form and Report objects to separate files with a .cls extension. Moving on to the code that will stitch these files back together before import. * Rename Sanitize class to SourceParser This better reflects the expanded role of the class. * Refactor for name change * Verify ribbon active state when the add-in loads Ensure that the ribbon is active when installing or activating the add-in. See joyfullservice#451 * Don't auto split layout/VBA for existing projects For existing projects in git repositories, form and report layouts should not be automatically split from the VBA code classes. There is another process that will allow us to split the files while preserving history in both files, but this involves a couple commits and requires a clean branch. For existing projects, this is a manual upgrade (option changes). For new projects, it can happen by default. * Move print settings processing to clsSourceParser This keeps the LoadComponentFromText function cleaner and easier to read. * Move source reading function This is used in several areas, and allows us to maintain the source file encoding determination in a single location. * Rework merging source content before import Cleaning this up to avoid reading and writing the file additional times while merging content from different sources. (Print settings, VBA code) * Add support to overlay VBA code after import For some (rare) situations, it is necessary to push the VBA code directly using VBE to preserve certain extended characters that may be corrupted in a regular round-trip export/import cycle. * Code cleanup and minor tweaks * Fix bugs in build logic Uncovered these while testing. * Check for diff tool before comparing objects * Implement correction according to rubberduck (joyfullservice#453) replace VBA commands: format with format$ trim with trim$ * Add wiki page for Split Files Describes the process in a little more detail. * Add change hook for options Used for special processing when certain options change. * Automate splitting forms and reports Adds a link and some code automation to split forms and reports in existing projects to layout and class files. * Rename function Git.Installed sounds better than Git.GitInstalled, and will almost always be called in the context of the git class. * Fixes joyfullservice#354 and Fixes joyfullservice#452 (joyfullservice#454) From @hecon5: Bump version minor number because it's not clear that the index will allow round trip from prior types in all cases; it worked on my machine, but that may not always be the case. The date types for the index are handled natively by modJsonConverter and should import/export correctly regardless of user's date / time zone or date encoding on machines. * Add performance timing to ISO date parsing See joyfullservice#354 * Add high-performance wrapper functions Avoids the use of RegEx when it is not necessary to parse a standard date format. joyfullservice#354 * Fix copy-paste oversight joyfullservice#354 * Update error handling Refactored a number of locations to use the new syntax for On Error Resume Next, and added code to clear expected errors. * Use faster date parsing for date only values * Add Split Files utility to ribbon (Advanced Tools) Also added an informational message box when the split is complete. * Rename as new files * Restore original files * Split layout from VBA in testing database Separates the VBA code from the layout definition in the source files. (Applying to testing database now, will apply to main project soon.) * Adjust version number I am using the minor version number to represent releases from the main branch, and the build number to continuously increment during the development cycle. * Revert the ConvDateUTC and ConvTimeUTC functions to always parse the "Fast" way first and revert otherwise. this allows the optimization to be used everywhere with no code changes. Ensure that millisecond accuracy is kept for otherse using the function. No Speed impact is noted on my end to doing this. * Pass by ref so we don't need to build more memory use. Optimize Offset string building to only do math when it's required and fix whitespace. * Cache the format types instead of needing to build them every time. * Bump Version * Verify consistent naming and byref passing of strings * Implement dialect in SQL formatting This was previously only partially implemented. joyfullservice#457 * Add support for ! boundary character This character is used in Microsoft Access in a query when referring directly to a control on a form, and should be treated similar to a period as a separator between elements. joyfullservice#457 * Add SQL formatting query to testing database This query demonstrates that we can properly parse and format expressions that refer to controls. joyfullservice#457 * Solve rare edge case with SQL IN clause Just in case a user has an embedded unquoted path in a string, the colon will be treated as a non-spaced boundary character during formatting. (For Microsoft Access SQL only) Fixes joyfullservice#447 * Addresses joyfullservice#459 (joyfullservice#460) Addresses joyfullservice#459 * Allow sort of operationames with leading spaces (joyfullservice#463) If a operationname has a leading space (like " MyOperation" ) the function "SortItemsByTime" fails. Now sorting will success. * Update comment After removing string padding in the previous commit. * Adjust detection of system tables Switching to just using a bit flag check to solve joyfullservice#462 * Log warning for UNC path access errors Failing to convert a path to UNC may not prevent the operation from completing, but it should be handled and logged. Fixes joyfullservice#461 * Refactor date conversion for DB Properties Save custom date properties in ISO (UTC) format in source files, without converting other property types like strings that may parse as dates. joyfullservice#459 * Turn off date ISO conversion by default This is only used in the index and certain database properties. joyfullservice#459 * Turn on date ISO conversion before reading index These dates need to be converted to local dates for internal processing. joyfullservice#459 * Add saved date property to testing database Verifies that the round trip conversion of saved date properties is working correctly. (The dates are stored as UTC in source files, but converted to local dates when imported into the database properties.) joyfullservice#459 * Add dates stored as text to testing database One stored as a regular date string, and the other as an ISO8601 string. (Neither should convert when reading or writing to JSON.) joyfullservice#459 * Add default git files if dbs in repository root If you use the default options of no special export folder path defined, the project may likely be in the repository root. Add the default .gitignore and .gitattributes files if they are missing. (This would be the default setup for most projects.) * Add note about Access 2007 support See joyfullservice#464 * Add test for Public Creatable class instance This is an undocumented property value that is sometimes used in the wild. Currently when you build from source, PublicCreatable (5) classes are converted to PublicNotCreatable (2). This instancing property can be set in VBA, and we want the imported class to match what was exported. This test currently fails, but will pass when the add-in is updated to support this property. * Support for PublicCreatable instancing for classes This (technically undocumented) technique allows class objects to be created by external projects without using factory methods. This approach was used in some of my projects, so it was important for me to see this property correctly set when the application was built from source. * Check VCS version before export Checks the VCS version before export to warn the user if we are running an export with an older version of VCS than was last used on this project. joyfullservice#465 * Check VCS version on build Check the VCS version before building, and warn if the project version is greater than the installed version. joyfullservice#465 * Reset error break mode after loading options The ability to break and debug VBA errors is dependent on an option value that is saved with each project. The on error directive should be reset after loading the project options to ensure that we can successfully break on errors. * Include name prefix with VBA code overlay Testing this on a machine using the Unicode BETA option in Windows. * Resolve build path during options upgrade We may not have a database open when upgrading options. Fall back to the path used to load the project options to determine the source file path. joyfullservice#467 * Add some documentation for merge build Taking some time to document the intended behavior of the merge build functionality as we work through some bugs. joyfullservice#471, joyfullservice#81 * Update Merge-Build.md Expand table of expected behavior. * Remove git integration for getting modified source I don't think this is actually being used in the wild, and it simplifies the process to have only a single code path for detecting changed source files. * Add missing database objects on merge If a source file exists, but no matching database object exists, we should merge the source file into the database. joyfullservice#471 * Fix SQL export of non-formatted queries Pass-through queries are now exported as SQL again. * Refactor source modified date for multiple files Some types of components, such as tables, forms, reports, queries, and shared images may use multiple source files to represent a single database component. We may need to check all of the related source files to accurately determine the latest modification date. * Log performance of clearing files by extension * Refactor components to provide file extension list Parent functionality such as determining the most recent file modified, getting the last modified date, and checking for alternate source files is better done by having the class provide the list of file extensions that might be used by the class, and having single external functions perform these tasks. (Avoids some redundant code.) * Add multi-file support to file properties hash This will allow us to more accurately detect changes in non-primary source files. (Such as a change in a shared image.) * Simplify component class Removing three functions that are not uniquely specific to each component type and are handled by external functions now that we have exposed the source code file extensions. * Remove Upgrade function on IDbComponent We don't need to try to support mixes of various versions of export files. Use the same version of VCS to build a fresh copy of the project, then export with the latest VCS to upgrade source file. * Move logic to clear legacy files Moving this to the Export function. * Rework processing of conflicts & orphaned objects Refactored the detection and processing of source conflicts and orphaned source files & database objects to better handle source file types that involve multiple files. joyfullservice#473 joyfullservice#471 joyfullservice#472 * Compare source contents of related files When checking for changes in source files, we need to check all the related source files for each component, not just the primary source file. * Adapt export comparison to support multiple files Further changes to compare all related source files. * Update testing database Updated to latest version of VCS. * Include class instancing in code module hash Class modules have an instancing property that needs to be checked for changes along with the VBA code to ensure that the database object matches the last export. A module will now be flagged as changed if the instancing property is changed. * Trap any XML import errors * Add alternate XML format function for big files Large XML files may cause memory errors with XSLT operations. Adding an alternate approach to simply replace the leading tabs with two spaces. This should allow the add-in to export even extremely large table data files as formatted XML. joyfullservice#389, fixes joyfullservice#474 * Remove format version from custom groups Any recent version of export file should be using the new format, and we don't need to carry this conversion forward into v4. * Fix issue with orphaned file detection Need to pass a dictionary, not a collection to the CompareToIndex function. * Move testing code to testing module * Require hash on index update Any update to the index is now required to provide a hash to match the source file. joyfullservice#472 * Save schema filter rules as collection Saving each filter line as a single element in a collection makes a much more readable section in the options.json file, especially when the rules become more complex. Previously this was saved as a combined string value which makes it harder to read changes to individual rules. * Support AfterBuild hooks in add-in project Made a tweak so we can use the RunAfterBuild hook in the add-in project to verify (load) the resources immediately after building from source. This will help prevent accidentally deploying the add-in without the needed resource records, as happened in joyfullservice#477. * Rename as new files * Restore original files * Split forms from VBA code in add-in project Going forward, this will allow us to edit the VBA code without affecting the layout definition files in forms. * Add region support type double (joyfullservice#481) Co-authored-by: Festiis <festim.nuredini@axami.se> * Add some additional comments to code changes Clarifies why we are using the Val() function when parsing ISO dates. * Add initial support for CommandBar popup menus This is still a work in progress, but has the basic functionality of exporting and importing custom CommandBars. * Add error handling to linked table refresh This could be related to a recent Access bug, but it is helpful to trap the error if it occurs. joyfullservice#484 --------- Co-authored-by: Hecon5 <54177882+hecon5@users.noreply.github.com> Co-authored-by: joyfullservice <joyfullservice@users.noreply.github.com> Co-authored-by: bclothier <bgclothier@gmail.com> Co-authored-by: Tanarri <Tanarri@users.noreply.github.com> Co-authored-by: Festim Nuredini <44016065+Festiis@users.noreply.github.com> Co-authored-by: Festiis <festim.nuredini@axami.se>
|
No, I do not get any errors when refreshing the links via the linked table manager. |
|
So, walking through this process, we are creating a new tblDef object, setting the connection string and some attributes, then appending it to tblDefs collection. (Adding it to the database) ' Read json file
Set dTable = ReadJsonFile(strFile)
If Not dTable Is Nothing Then
' Link the table
Set dItem = dTable("Items")
Set dbs = CurrentDb
Set tdf = dbs.CreateTableDef(dItem("Name"))
strConnect = GetFullConnect(dItem("Connect"))
With tdf
.Connect = strConnect
.SourceTableName = dItem("SourceTableName")
.Attributes = SafeAttributes(dItem("Attributes"))
End With
dbs.TableDefs.Append tdfAfter adding it to the database, we are then verifying that the connection string hasn't changed from what we used when creating the table. (See #192) ' Verify that the connection matches the source file. (Issue #192)
If tdf.Connect <> strConnect Then
tdf.Connect = strConnect
tdf.RefreshLink
End If
dbs.TableDefs.RefreshIn this case, the connection string has changed, so it is being set again to the value read in the source file. It is at that point that you hit the error refreshing the link. (Updating the connection using the applied connection string.) Perhaps you could do a little testing to compare the actual connection string after appending the tabledef to the one we are attempting to set, and even try manually setting the connection string and refreshing the link to see if we can isolate the error to perhaps something in the connection string...
|
|
I am using a trusted connection. The difference between the connect string stored in the variable strConnect and the .connect property of the table after it is appended is the UID field. |
|
Okay, it doesn't seem like that should make a difference... 🤔 Do you get any errors when setting the connection string and refreshing the link manually through VBA in the immediate window? If you don't see any errors when manually updating the connection string, it would be interesting to see what would happen if you refresh the tblDefs before updating the connection string. Change this: ' Verify that the connection matches the source file. (Issue #192)
If tdf.Connect <> strConnect Then
tdf.Connect = strConnect
tdf.RefreshLink
End If
dbs.TableDefs.RefreshTo this: ' Verify that the connection matches the source file. (Issue #192)
dbs.TableDefs.Refresh
If tdf.Connect <> strConnect Then
tdf.Connect = strConnect
tdf.RefreshLink
' Add a second dbs.TableDefs.Refresh here if needed.
End If |
|
Yes, running Currentdb("AP_Contract").RefreshLink causes error 3283 |
|
What if you link that table manually in another test database, then try the Another thing that might be worth trying is running a SQL Server Profile trace against the SQL server while triggering the error. Sometimes there are additional messages or clues that I can pick up that way. |
|
I created a new database and manually linked the table. Refreshing the link in the Linked Table Manager works without error, but running CurrentDb("AP_Contract").RefreshLink in the immediate window causes error 3283. I've never done a SQL Server Profile trace before. There is a lot going on in 0.15 seconds! I found the log where I am the one connected, but can't make sense of any of it. |
|
Very interesting. The SQL trace looks pretty similar to what I am seeing on my server. I am still rather perplexed as to why we get an error when using the I have never encountered this before on my systems, and can't seem to reproduce it, so I am not sure how to troubleshoot this going forward... 🤔 |
|
FWIW: I have found that it's usually more reliable to delete & re-create a tabledef than executing |
If the connection string is updated (corrected) after adding a linked table, sometimes it can throw error 3283. After some discussion in #484, we determined to just log and clear the error.
joyfullservice
added
the
pending resolved
Reviewing the logic flow here, the tabledef has already been created from scratch. This was just a second check to make sure the resulting connection string still matches what we gave it when creating the linked table. (Most of the time this isn't an issue, but it can sometimes lead to unexpected results in rare situations. See #192) If the connection string doesn't match, and we attempt to reset it and refresh the link and it still throws an error, my hunch is that we can probably just proceed with the build and not worry about it. It should be a very rare scenario with several specific factors for this to occur, so I don't know that it is a big enough issue to warrant anything beyond adding the note to the log file. |
Started having issues just yesterday when building from source. I am getting an error messages in the build log for every linked SQL Server table. The linked views do not cause an error.
From Log:
ERROR: Unhandled error, likely before
On ErrordirectiveError 3283: Primary key already exists. Source: .Unknown.LogUnhandledErrors
I had version v3.4.23 installed and would get an overflow message and the build would stop. I just updated to the latest beta version this morning. I am still getting the error messages in the log for every table, but the build completes and the tables relink.
The text was updated successfully, but these errors were encountered: