Good afternoon all,
During the last few weeks an error has appeared and became very common. This is confusing the heck out of several of us here where I work.
We have checked to make sure the computers are set up as default to open in a "shared" mode and not "exclusive".

When someone opens up a database that another already has open, it gives an error stating that "the table MsysAccessObjects" is already open exclusively by another user. When you look at the database window...the table does not exist.
Has anyone seen this or know what is causing it????
Please help....We are going insane trying to figure this one out.

Regards,
Laura

The only thing standing between you and your goal is doubt. Quit doubting yourself and you'll be able to accomplish anything!

__________________
Regards,
Laura

The only thing standing between you and your goal is doubt. Quit doubting yourself and you'll be able to accomplish anything!

This is a system table that access uses. I think it keeps track of the objects such as tables, forms.
Somehow, this table has been corrupted.

It seems like you are going to have to import all of your objects to a new database. Try to split your database to keep your tables on one file and all other items on the other. Also, do not share the database file. Try to distribute copies to each user, this will improve performance as well.

Is your data being kept by Access or SQL Server?


Sal

Probably also worth mentioning that all of the system tables are hidden in the database window by default. To view them select Tools -> Options and on the View tab check the System Objects checkbox.

Bob

You got me curious about the contents of MsysAccessObjects since Access doesn't let you see what's really in it. ADO.NET, however, has a GetOleDbSchemaTable method of the Connection object that does. I found a C# class that implements it, and this is what the output looked like when I connected the console app to a blank Access database (i.e., contains only system tables). The class is available at:

http://my.execpc.com/~gopalan/dotnet..._metadata.html

Just a bunch of database metadata about table and column properties:

Listing Table Metadata Information ...
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
TABLE_TYPE
TABLE_GUID
DESCRIPTION
TABLE_PROPID
DATE_CREATED
DATE_MODIFIED

Listing Tables ...
MSysAccessObjects
MSysACEs
MSysObjects
MSysQueries
MSysRelationships

Listing Column Metadata Information ...
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
COLUMN_GUID
COLUMN_PROPID
ORDINAL_POSITION
COLUMN_HASDEFAULT
COLUMN_DEFAULT
COLUMN_FLAGS
IS_NULLABLE
DATA_TYPE
TYPE_GUID
CHARACTER_MAXIMUM_LENGTH
CHARACTER_OCTET_LENGTH
NUMERIC_PRECISION
NUMERIC_SCALE
DATETIME_PRECISION
CHARACTER_SET_CATALOG
CHARACTER_SET_SCHEMA
CHARACTER_SET_NAME
COLLATION_CATALOG
COLLATION_SCHEMA
COLLATION_NAME
DOMAIN_CATALOG
DOMAIN_SCHEMA
DOMAIN_NAME
DESCRIPTION

Listing Columns (TableName : ColumnName format)...
MSysAccessObjects : Data
MSysAccessObjects : ID
Press any key to continue

So it turns out, as one would expect, that MSysAccessObjects is the Access counterpart to the SQL Server 'Master' database. Difference is that in SQL Server, you can use these column values to query for schema information as in:

SELECT
      T.TABLE_NAME AS [A_Table],
      C.COLUMN_NAME AS [B_Column],
      C.IS_NULLABLE AS [C_NULLS],
      C.DATA_TYPE AS [D_Type],
      C.COLUMN_DEFAULT AS [E_DEFAULT]
FROM
      INFORMATION_SCHEMA.Tables T JOIN INFORMATION_SCHEMA.Columns C
ON T.TABLE_NAME = C.TABLE_NAME
WHERE
      T.TABLE_NAME = 'Customers'
ORDER BY
      T.TABLE_NAME, C.ORDINAL_POSITION

If I run this query against the SQL Server version of Northwind, I get the following output (sorry if this doesn't format right):

A_Table B_Column C_NULLS D_Type E_DEFAULT
Cutomers CustomerID No nchar NULL
Cutomers CompanyName No nvarchar NULL
Cutomers ContactName Yes nvarchar NULL
Cutomers ContactTitle Yes nvarchar NULL
Cutomers Address Yes nvarchar NULL
Cutomers City Yes nvarchar NULL
Cutomers Region Yes nvarchar NULL
Cutomers PostalCode Yes nvarchar NULL
Cutomers Country Yes nvarchar NULL
Cutomers Phone Yes nvarchar NULL
Cutomers Fax Yes nvarchar NULL

I don't think there is anyway to do this in Access.

Regards,

Bob

OK, I'm getting a little off topic. But you can get MsysAccessObjects to give you:

FIELD NAME FIELD TYPE SIZE DESCRIPTION
========== ========== ==== ===========
CustomerID Text 5 Unique five-character code based on customer name.
CompanyNameText 40
ContactName Text 30
ContactTitle Text 30
Address Text 60 Street or post-office box.
City Text 15
Region Text 15 State or province.
PostalCode Text 10
Country Text 15
Phone Text 24 Phone number includes country code or area code.
Fax Text 24 Phone number includes country code or area code.
CustomerThingy Text 20
========== ========== ==== ===========

With the following by calling TableInfo("Customers"):


'Code by Allen Brwone

Option Compare Database

Function TableInfo(strTableName As String)
On Error GoTo TableInfoErr
   ' Purpose: Display in the immediate window the field
   ' names, types, and sizes for any table.
   ' Argument: Name of a table in the current database.

   Dim db As DAO.Database
   Dim tdf As DAO.TableDef
   Dim fld As DAO.Field
   Set db = DBEngine(0)(0)
   Set tdf = db.TableDefs(strTableName)
   Debug.Print "FIELD NAME", "FIELD TYPE", "SIZE", "DESCRIPTION"
   Debug.Print "==========", "==========", "====", "==========="

   For Each fld In tdf.Fields
      Debug.Print fld.Name,
      Debug.Print FieldTypeName(fld.Type),
      Debug.Print fld.Size,
      Debug.Print GetDescrip(fld)
   Next
   Debug.Print "==========", "==========", "====", "==========="

TableInfoExit:
  Set db = Nothing
   Exit Function

TableInfoErr:
   Select Case Err
   Case 3265 ' Supplied table name invalid
      MsgBox strTableName & " table doesn't exist"
   Case Else
      Debug.Print "TableInfo() Error " & Err & ": " & Error
   End Select
   Resume TableInfoExit
End Function

Private Function GetDescrip(obj As Object) As String
    On Error Resume Next
    GetDescrip = obj.Properties("Description")
End Function

Private Function FieldTypeName(n As Long) As String
    'Purpose: Converts the numeric results of DAO fieldtype to text.
    'Note: fld.Type is Integer, but the constants are Long.
    Dim strReturn As String 'Name to return

    Select Case n
    Case dbBoolean
        strReturn = "Yes/No" '1
    Case dbByte
        strReturn = "Byte" '2
    Case dbInteger
        strReturn = "Integer" '3
    Case dbLong
        strReturn = "Long Integer" '4
    Case dbCurrency
        strReturn = "Currency" '5
       Case dbSingle
        strReturn = "Single" '6
    Case dbDouble
        strReturn = "Double" '7
    Case dbDate
        strReturn = "Date/Time" '8
    Case dbBinary
        strReturn = "Binary" '9
    Case dbText
        strReturn = "Text" '10
    Case dbLongBinary
        strReturn = "OLE Object" '11
    Case dbMemo
        strReturn = "Memo" '12
    Case dbGUID
        strReturn = "GUID" '15
    Case dbBigInt
        strReturn = "Big Integer" '16
    Case dbVarBinary
        strReturn = "VarBinary" '17
    Case dbChar
        strReturn = "Char" '18
    Case dbNumeric
        strReturn = "Numeric" '19
    Case dbDecimal
        strReturn = "Decimal" '20
    Case dbFloat
        strReturn = dbFloat '21
    Case dbTime
        strReturn = "Time" '22
    Case dbTimeStamp
        strReturn = "Time Stamp" '23
    Case Else
        strReturn = "Field type " & n & "unknown"
   End Select
   FieldTypeName = strReturn
End Function

Laura;
Have you tried repairing your database and cchecking if it compiles?
You never know if this could help.



Sal

You could also download and run the Jet Compacting Utility on it. This will sometimes fix a db that standard compacting won't fix.

http://office.microsoft.com/assistan...5701033&CTT=98

Good morning all,
I have tried to compact and repair the database and still get this error. We even get this on new databases. Everything is in Access XP. It is stored on a network drive. I have split my databases before but the user end still locks down the back end for some reason (Haven't figured that one out yet ) and keeps everyone else out. I did go to Tool & Options to check my settings and checked the system objects and Hidden objects so now I can see that table.
Do you think I could be missing a setting that is making that table stayed locked down instead of letting multiple people in at one time?

Our network is Novell and our computer have mostly Windows 2000, but there are still a few 98's and NT's.;)

Regards,
Laura

The only thing standing between you and your goal is doubt. Quit doubting yourself and you'll be able to accomplish anything!

To make sure the obvious is done.
Did you convert a database from previous version to 2002?
Is this happenning when a particular user opens the database, if yes that users settings may be what is causing it.
Are you using Access security?

Sal

Hi Laura,

You're the second person I've heard mention an issue similar to this on a Novell box. I havn't worked through this problem myself but this might be worth looking into.

Apparently, Novell 6 has two settings called oplocks and file caching that can interfere with the database locking scheme used by Access. You might want to check the Novell site for further explanation of these features. Maybe they even have a bug report.

Bob