Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old October 10th, 2003, 03:40 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Connersville, Indiana, USA.
Posts: 126
Thanks: 5
Thanked 0 Times in 0 Posts
Default Non-existent MsysAccessObjects Table

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!
Reply With Quote
  #2 (permalink)  
Old October 10th, 2003, 05:01 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #3 (permalink)  
Old October 10th, 2003, 07:34 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Quote:
quote:When you look at the database window...the table does not exist.
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


Reply With Quote
  #4 (permalink)  
Old October 10th, 2003, 08:54 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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

Reply With Quote
  #5 (permalink)  
Old October 10th, 2003, 09:21 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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



Reply With Quote
  #6 (permalink)  
Old October 11th, 2003, 08:25 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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



Sal
Reply With Quote
  #7 (permalink)  
Old October 12th, 2003, 07:53 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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

Reply With Quote
  #8 (permalink)  
Old October 13th, 2003, 09:10 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Connersville, Indiana, USA.
Posts: 126
Thanks: 5
Thanked 0 Times in 0 Posts
Default

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!
Reply With Quote
  #9 (permalink)  
Old October 13th, 2003, 09:43 AM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #10 (permalink)  
Old October 13th, 2003, 10:02 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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



Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
very urgent:cals table to indesign table using xsl franklinclinton XSLT 1 December 16th, 2009 03:48 PM
Populate a List Box with Table Names & Table date hewstone999 Access VBA 1 February 27th, 2008 10:10 AM
(oracle 8i)Alter Table <table> coalesce partition combo Oracle 3 October 13th, 2004 09:35 AM
Unlisted book and non-existent code mysorian Wrox Book Feedback 2 June 23rd, 2004 12:19 PM
size of table (type table is table of number) MikoMax Oracle 1 November 19th, 2003 03:11 AM



All times are GMT -4. The time now is 01:49 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.