msjtes40.dll error

[Danny]

Hi

I’m currently running office 365 a split access db. Few users to including myself recently received the following error:

Your database or project contains a missing or broken reference to the file ‘msjtes40.dll’ version 4.0. To ensure that your database or project works properly, you must fix this reference.

Searching online, I found the following possible solution and I was able to open the copy of my FE.
Steps to clear the missing reference:

1. Open your database: in Microsoft Access.

1. Press Alt+F11 to open the Visual Basic Editor (VBE).

1. In the VBE, go to the Tools menu.

1. Click on References.

1. In the References dialog box, scroll down and find the entry that starts with MISSING: followed by the name of the reference, in this case, MISSING: Microsoft Jet and Replication Objects Library or something similar.

1. Clear the check box: for this MISSING reference.

1. Click OK.
   
   
2. Select Debug > Compile from the VBE menu to check if the project now compiles without error.

However, I’m currently experiencing the following issues:

1. When I attempted to search for a record in the backend tbl, I get #Name?
2. When I tried opening previously created query and/or modify the criteria, I received
Record is deleted. (Error 3167)

Any ideas?

TIA

Regards,

 

[Gasman]

See this thread

What are/were you using that dll for?

 

[GPGeorge]

Hi

I’m currently running office 365 a split access db. Few users to including myself recently received the following error:

Your database or project contains a missing or broken reference to the file ‘msjtes40.dll’ version 4.0. To ensure that your database or project works properly, you must fix this reference.

Searching online, I found the following possible solution and I was able to open the copy of my FE.
Steps to clear the missing reference:

1. Open your database: in Microsoft Access.
2. Press Alt+F11 to open the Visual Basic Editor (VBE).
3. In the VBE, go to the Tools menu.
4. Click on References.
5. In the References dialog box, scroll down and find the entry that starts with MISSING: followed by the name of the reference, in this case, MISSING: Microsoft Jet and Replication Objects Library or something similar.
6. Clear the check box: for this MISSING reference.
7. Click OK.
   
   
8. Select Debug > Compile from the VBE menu to check if the project now compiles without error.

However, I’m currently experiencing the following issues:

1. When I attempted to search for a record in the backend tbl, I get #Name?
2. When I tried opening previously created query and/or modify the criteria, I received
Record is deleted. (Error 3167)

Any ideas?

TIA

Regards,

Did the code in your project compile after deselecting this reference? I didn't see where you mention completing that step?

I, too, am curious about the reason for using the Microsoft Jeft and Replications Objects Library with that version. It appears to be a JET DLL, and therefore dating back to the pre-accdb/ACE versions.
So, in addition to indicating why it was selected in the first place, can you put the whole situation into context? Is the Front End an mdb or an accdb? How long ago was the database created? Why was this dll referenced in the original implementation?

I find, for example, on my computer running Windows 11, M365 Access, that the DLL called Microsoft Jet and Replication Objects 2.6 Library, which is the closest to the reference you mention, is called msfro.dll And there is no ‘msjtes40.dll’ version 4.0 on the computer.

It makes me wonder what about the history of this particular database application and the references it uses. Are we going down memory lane a bit here?

 

[The_Doc_Man]

@Gasman provided a link to a thread dealing with problems in the named DLL file. Be aware that the particular file is old enough that it will become desupported at the end of the month (as described in the referenced thread). If you have not followed his link already, do so and read the entire link so you will see how much work has already been done on problems related to that library. Some of the posts in the linked thread give one or more workarounds. Plus there is a lot of discussion regarding the wisdom (?) of using 20-year-old libraries with O365.

Here is a link to the top of Gasman's reference.

VBA code crashes after installing KB5064081 update

Hello, After installing windows update KB5064081 on Windows 11 machine, the following piece of code crashes on Access 2016: Public Sub CreateTable(strPath As String) Dim cat As ADOX.Catalog Dim tbl As ADOX.Table Dim idx As ADOX.Index Dim flag As Boolean Set cat = New ADOX.Catalog cat.Create...

www.access-programmers.co.uk

 

[theDBguy]

Can you post a screenshot of your References window showing the missing reference name?

 

[Danny]

Thanks everybody for your feedbacks.
I’m not using it and I’m not sure how it appears in the list to begin with.

George, Did the code in your project compile after deselecting this reference?

Now you mentioned it, I might have missed the last step: Debug > Compile from the VBE menu to check if the project now compiles without error. I’ll do so when I get back to work in a couple of days and post the result.

It makes me wonder what about the history of this particular database application and the references it uses. Are we going down memory lane a bit here?

It is possible, how do I go about finding if the DB is about to exceed the max file size? The db is very old designed by someone else who no longer with us.

The attached screenshot is to show before and after of the reference file. Also the error I get when searching for record in the BE and creating queries in FE.

TIA
Regards,

 

[The_Doc_Man]

It is possible, how do I go about finding if the DB is about to exceed the max file size?

Open Windows Explorer, select to show details, and look at the size of the file. A monolithic Access app cannot exceed 2 Gb (though Windows might show that as 2147 Mb). Since you said it was a split app, each component file cannot exceed 2 GB. In actual practice, you are skating on thin ice when you exceed 1.5 Gb though the exact point of no return DOES depend on the structure of the DB. If you are above 1.5 Gb, I would strongly suggest looking into archiving older data if possible.

To get the correct file size, make a Windows Explorer copy of the DB to a safe place, then open the DB privately and perform a Compact & Repair. If that works with no errors, you can use the compacted copy for production, and its size immediately after that C&R is the actual DB size. For big, old DB files, there is some minor risk of errors, which is why you make a backup copy first, using Windows rather than Access to make the backup copy. Using the Windows copy/paste option, you move the file as a whole and Access doesn't get involved in that step. As to WHERE you move the copy, that's up to you but I would put it in a separate folder.

 

[Danny]

Hi,

theDBguy
Can you post a screenshot of your References window showing the missing reference name?

attached...

The_Doc_Man, glad you brought the topic of archiving as I was recently tasked to archive old data (2019 and older cases). Since I didn't do any archiving in the past, I was looking for some guidance: how to go about implementing archiving, best practice etc.

Using the Windows copy/paste option, you move the file as a whole...

Copy/paste both BE/FE?

TIA

Regards,

 

[The_Doc_Man]

Best practice for archiving involves deciding ahead of time what you intend to do with the archived data and how you intend to identify data eligible to be archived. Make those decisions before you do anything else.

I had set up my major projects to have flags to indicate data to be archived, but if you have a date field that shows exactly when the record was put into a closed status, or any other date indicating you are done with it, you have enough.

As to WHERE you would put it, it depends on what you will want to do with it later. There is no reason you couldn't build another back-end file with the same exact table structures as you would need for your active structure - but with different table names. For example, if the main production table is Sales then the archived data would go into a table called Arch_Sales. Then you would build the back-end so that you could link to its tables, too - always where the working tables and the archived versions have some difference.

So now you have the FE linked to the production BE file AND to the archive BE file. If both files are linked, you can run a query using that date we talked about. Now select/identify the cutoff date. Then you run two queries.

1. INSERT INTO Arch_Sales (SaleID, SaleDate, ....) SELECT SaleID, SaleDate, ... FROM Sales WHERE SaleDate >= #1-Jan-2019# ;
(NOTE: Arch_Sales is linked to the archive BE file; Sales is linked to the production BE file.)
2. DELETE * FROM Sales WHERE SaleDate >= #1-Jan-2019# ;

This is for presenting the idea. You might want to have a form with a date picker to supply the target cutoff date. The tedious part, but fortunately you only do it once, is listing the fields from the main table and the archive target table. You have the two queries, and in theory you COULD build a code sequence or a macro to run them in proper order. You COULD - but I would never do it that way because I would first want to spot-check that the right number of records was copied by the first query into the archive table.

NOTE ALSO: there is an IN-clause syntax such that you could do this WITHOUT linking the archive table, though you still would have to prepare the target file beforehand. Do a Google search with Gemini enabled for: "Access syntax for the IN clause that points to another file" and you will get an example of the INSERT INTO format for an external targeted file. The syntax for this IN clause is only a little verbose, but it is easy enough once you see how it is done.

In the case where I did my archiving, I never needed to actually process the records again but I did need to have a way to look up a server name, an action title, and the final status recorded for that combination. In that case I was allowed to EXPORT to Excel. Statuses would have been short phrases like "Implemented", "Not Done/Wrong OS", "Adjusted", ... we had about two dozen detailed statuses and over half of them were "Not done/<the reason>". In my case, I had a more complex selection criterion - that the status had to be one of the "final" statuses and the date of reaching that status had to be at least six months old. So that means your WHERE clause gets messier. But the idea is the INSERT INTO and the DELETE * FROM both use the same WHERE criteria.

And the final step: Let's say you successfully copied the data to the targeted archive file and then successfully deleted the same records that you just copied to the external file. Now get out of the database file, then copy the files. A "Windows Explorer copy/paste" is sufficient, fast, and easy. Open the files with Access, one at a time, and perform Compact & Repair. If the C&R goes foul, you have the copies to try again. If the C&R works, you can put the C&R'd files where they belong for production or archive access. Since I was a "belt & suspenders" style of admin, I always made one more copy of the files AFTER the C&R but BEFORE they went back into production status - and discarded the pre-C&R copy. Then the 2nd copy was the one that went into the "backup safety folder" as a "just in case" file.

Tedious? You bet. But good practice for data of value to the company? You know it is.