May 15

Windows 8.1 PCs Connect to the Network but not the Internet

windows81This past Tuesday night, Microsoft released a security patch. On Wednesday morning, we and some of our customers encountered problems with connectivity that were quite unusual and different from past security updates. The update required servers to reboot which triggered some issues, but manageable. What was particularly troubling were multiple reports of problems with PCs using Windows 8.1.

The PCs could still connect to the network and see all the network resources, but they could not get on the Internet. Using different logins including Administrator logins didn’t make a difference. Other PCs using Windows 7 or older O/S were able to connect successfully even with the same cable as the Windows 8.1 PCs that couldn’t connect.

What Could be Wrong?

We struggled trying to see if there were issues with:

  • Hardware on the PC, network card, cable or switch
  • Software issues with the automatic Windows patches, DHCP network settings, IP addresses, Firewall, antivirus, etc.
  • Login rights and permissions

We couldn’t determine the problem or find a solution. Since the user could log into the network, permissions seemed sufficient.

From the Windows command prompt (Run cmd), we used the ipconfig command with the release and renew command lines to see if that would reset the IP address, but that didn’t make a difference either:

ipconfig /release
ipconfig /renew

Solution: Flush DNS

Finally, I posted a message to the Microsoft Access MVP group, which I’m honored to be a part of. Long time colleague, Tom Wickerath suggested flushing the DNS (Domain Name System) cache by using:

C:\> ipconfig /flushdns

We weren’t familiar with that command line option as it doesn’t appear when you enter

ipconfig ?

Well, it worked! We may never know whether the problem was caused by the Windows security patch or if it was just a coincidence. Regardless, the flushdns command reset the PCs that were affected by this problem.

Thanks for sharing your insight Tom!

Jul 10

Microsoft Access Class Not Registered Run-time Error -2147221164 (80040154)

Microsoft Access Triggers a Runtime Error ‘-2147221164 (80040154)': Class Not Registered

This error occurs in a Microsoft Access database that seems to work fine on every other machine but one. The MS Access database actually loads and runs, so the code is compiled and functional. Then it dies on some very common code such as CurrentProject.Connection for ADO to open a table or query recordset:

Microsoft Access "Class Not Registered" Run-time Error -2147221164 (80040154)

Confusion

The “Class Not Registered” is very confusing. It implies code that won’t compile or broken library references but that’s not the true cause. Is the Access database corrupt? No.

We’ve received reports for this error for years and were never able to reproduce it. We finally did and figured out why it occurs and how to fix it. Read our new paper Microsoft Access “Class Not Registered” Run-time Error ‘-2147221164 (80040154) to learn more.

Jul 01

Microsoft Access Inconsistent Compile Error for a Field Reference in a Form

Our Professional Solutions Group was recently asked to diagnose a Microsoft Access database experiencing recurring compile errors with code behind a form that looks like this:

If IsNull(Me.Comments) Then

where Comments is not a control on the form, but a field in the form’s RecordSource.

In general, this compiles and runs fine, but on seemingly random occasions while the program is running, it generates a compile error saying that that field was not found. But the field always existed on the form’s RecordSource, so why was this happening?

Solutions

There are a few ways to avoid this problem:

  • Change all the Me. to Me! which is the proper way to reference a field in VBA code, if there is no control bound to this field.
  • Create an invisible  text box that assigns its ControlSource to that field, give the text box a different name (e.g. txtComments), and reference the text box in code.
  • Deploy the database so its compiled state cannot be changed (ACCDE or MDE)

We prefer the use of the invisible text box so that we can reference the control name via the “Me.” syntax rather than “Me!”. The “Me.” syntax is verified when the code is compiled so that a typo with the control name is caught. This is preferable to a runtime error that gets triggered when the user encounters that line of code.

But Why?

Though we knew how to fix this, we were curious to understand why the compilation wasn’t consistent across users. It also didn’t fail when a specific event occurred. It seemed almost random when the compile error arose. And the form triggering the error seemed perfectly fine with a reference to a field that exists in its RecordSource.

The Real Cause for the Compile Error

Through our own research and help from our Microsoft Access MVP colleagues, we discovered that the compile error was tied to programmatically changing the RecordSource of a form. The change is not necessarily on the form where the compile error is triggered.

Microsoft Access seems to reset its internal list of field references some time after the RecordSource is modified, which triggers the compile error. This explains why some users experienced it and others did not since it depended on whether the user opened a form that changed its RecordSource. It also explained why the error didn’t occur immediately after a RecordSource was modified.

Special thanks to Dirk Goldgar for pointing this out. Hope you never encounter this!

Additional Resources for Database Compile and Field Reference Issues

For additional tips on Microsoft Access application development, visit our:
Microsoft Access Developer and VBA Programming Help Center

Sep 07

Microsoft Access Error 3045 Could Not Use Database; File Already in Use

Error Message

When you try to open a database, you may encounter this error message: Could not use [Database Name]; file already in use.

Explanation

This error occurs if another user (or instance of Access) has opened up the database exclusively and you try to open it. Maybe the other user opened the database in exclusive mode or made some modifications such as editing modules, which put the database in exclusive mode.

However, that may not be the case. This error can occur even if the other user has the database opened without opening it exclusively. What’s going on? Why would the second instance think it’s being opened exclusively?

What if the Other User isn’t Opening it Exclusively?

It turns out that another cause of this error is not immediately obvious and is related to workgroup security files. This applies to MDB files (not ADP or ACCDB formats). If two instances of Microsoft Access (DAO database engine) are using different system MDW files, a conflict may occur where the database appears to be exclusively locked by the other instance. The MDW file used by your instance of Access can be checked in VBA by examining the value of DBEngine.SystemDB.

Another copy of Access or DAO may have that database opened using another MDW file causing this conflict. This can come from another instance of MS Access, a Microsoft Access add-in or library reference, a VBA Add-in, or other program such as VB6, .NET, etc. that’s connecting to that database.

To avoid this, if you’re using workgroup security, make sure every instance is pointing to the same shared MDW file. This is often defined in the shortcut used to launch Microsoft Access, or if you are opening a different database programmatically within your VBA code.

Additional Resources

Visit our Microsoft Access Error Number and Error Description Reference site for a complete list of Microsoft Access error numbers, many with links to resources explaining and resolving them.

total-access-admin[1]If you’re interested in seeing who’s going in and out of your database in real-time, check out our Total Access Admin program. It’ll let you monitor multiple databases across your network to see when users enter and exit your database and flag instances where connections are dropped suspiciously.

Nov 28

Microsoft Access 2010 Error Number and Description Resource

As a resource for the Microsoft Access community, we’ve updated our comprehensive list of Microsoft Access Error Codes and Descriptions for Microsoft Access 2010.

There are PDF reports of the error codes and descriptions, and a database containing a with each error code and its different descriptions access MSAccess versions 2010, 2007, 2003, 2002, and 2000.

Here’s the detailed HTML page that lists every Microsoft Access 2010 error code with its description and for some of them, hyperlinks to resources that address the error and/or provide more information on the topic. Since this page is so large, please be patient while it loads.

This resources is part of our Microsoft Access Developer Help Center where you’ll find lots of other information to improve your skills. Hope this helps!