Microsoft Access Page includes tutorials, solutions, SQL, ODBC, Oracle, Access (2,97 to 2007), year 2000

• Problems of having Access 2003 or earlier installed as well as Access 2007
  If you get the following error message:
  "LinkMasterFields property setting has produced the following
  error: 'object or class does not support the set of events'"
  when using existing Access 2003 forms, or if you can no longer find any wizards this is caused by Access 2003 looking at the object library of Access 2007- which is incompatible.
  You can easily check this by creating a new module (if you do not have an existing one) and then looking at Tools/References. If Access 2003 is referencing Microsoft Access 12.0 object library it is pointing to 2007. It should be pointing to Microsoft Access 11.0 (or earllier if you are using an earlier version).
  The problem now arises that you can not browse and correct it due to not having enough rights. Goto the control panel and select add software. Then select Microsoft Office 2003 and repair. You will then have to restart your PC if you need to use Access (you just get Microsoft errors asking you to send an error report otherwise).
  THE RECOMMENDATION IS ONLY TO HAVE ONE VERSION OF ACCESS ON YOUR PC UNLESS YOU REALLY NEED TWO. You have a choice as to whether you have Access 2003 or 2007 on your PC. Training materials are available for both
• Opening Access 2007 databases with earlier versions of Access
  Access databases developed in previous versions of Access (Access 2000 & 2002-2003/ XP) can be opened in Access 2007 without having to be converted.  However, Access 2007 databases cannot be opened in earlier versions of Access until they have been converted.
  • Microsoft's help on converting an Access 2007 database to an earlier version
  • Microsoft's help on converting an earlier Access database to Access 2007
• Links to external sources of help for Access 2007
  Access 2007 has enough significant changes in interface to cause some difficulties for experienced Access database developers.  The main problem is in finding commands and icons, as many of these have been moved or clustered in initially non-intuitive places.  The links below lead to external documentation and websites with information on getting to grips with the new interface of Access 2007.
  • Florida Gulf Coast University's Access 2007 tutorials
  • Microsoft's Access 2007 help - main page
  • Microsoft's Up-to-speed with Access 2007 - online training course
  • Microsoft's Up-to-speed with Access 2007 - demo
  • Microsoft's reference page showing locations of Access 2003 commands in Access 2007
  • Microsoft's interactive Access 2003 to Access 2007 command reference guide (Adobe Flash 7.0 or later required)
  • Open Microsoft's Access Ribbon-mapping workbook (xls, 3.06 MB)

  Microsoft has developed a useful interactive command reference guide for download.  It shows Access 2003 views and when hovering the mouse pointer over commands, context-sensitive popups explain where these commands are found in the new version of Access.  This works well if open in the background whilst working with Access 2007.

  • Download Microsoft's Access 2003 to Access 2007 interactive command reference guide (exe 3.3 MB).
• What's new in Access 2003 (August 2008)
• What's new in Access XP (January 2006)
• Access 2000 for Access 97 usersincludes conversion solutions
• Whats new in Access97 and conversion problems for the Access 2 user (more comprehensive than tutorial)
• Converting from Access 97 to Access 2 (downwards conversion)
• Passing data between Microsoft Office products and ORACLE Word
• Converting from Access to Oracle Contains a useful Oracle script to rename column_names so that they are useable in Oracle
• Converting from Oracle to Access. Contains a useful Oracle script to create a delimitered file from a table or query

Useful solutions

• Resetting an autonumber

  I have just deleted all the test data from last year and want to put some real data in to the database. The automnumber keeps starting from 117 (as we had 116 test records in it). What should I do to get it to reset and start from 1?
  Since it is only an identifier and not used for anything else then it shouldnt matter what value it is. I well advise you not to bother. There are two ways otherwise- both messy. Neither work well if there is a relationship from the Autonumber field linked to a field in another table, since all relationships to that field have to be recreated and checked.
  • a) create a copy of the table with no data and append all the rows from the original. Delete the original if happy. Rename the new table back to the old
  or
  • b) add a new autoumber field and make that the primary field instead. Delete the original autonumber field if happy, rename the new field to the old field name. You will have to delete relationships and recreate and check lookup fields are still working.

• property value is too large

  This error occurs when there are too many fields in the table. This can be as little as 50 or 60- it depends on the data types, more often it is when there are more than 100 fields. memo fields in particular can reduce a workable number. The solution is to split the table in two (making sure you replicate the primary key in each table)- you then have a 1:1 relationship between the tables. The repurcussions of this error are that all of a sudden, one cannot make changes to the data, nor add new data.
  One often gets too many fields when one is setting up a table for a large questionaire- consider the design when handling single value answers. Do you really want a y/n field for each possible value (this makes the table very wide), or would it be better to have one field holding the answer? For multiple choice answers, consider having another table holding each selected answer (ie 3 answers mean 3 records)

• Case sensitive search

  Access searches are not case sensitive (only the FindRecord macro and binnocular search). To find lower case letters use the asc function to check the ASCII value.
  a has ASCII value 97, z 122, A 65, and Z 90. Thus to find g, test for asc(103), G for asc(71), or asc(field)=asc('G').
  However, you will now get Data type mismatch in criteria, if you use left rather than mid with length 1, if so enter the criterion for asc(left(field,2)) as between 71 and 71 (if testing for G in the second letter) which seems to keep it happy! You should really only test for one letter so asc(mid(field,2,1)) should be tested against asc('G')

• Splitting a name field

  The name of a person has been entered in one field. A module is required to split the name field into the separate fields:lastname, initials, title (splitname module)

• Splitting an address field

  The address has been entered in one field with each address line separated by carriage return (chr(13) ). A module is needed it split it into separate fields (split address module)

• Splitting a field using ADO

  Module demonstrates retrieving, updating the table to show number of components in that field and inserting new rows in another table (changing "wide" table into a "long" table). ADO is useful when an update cannot be done by a query ( Use of ADO to retrieve and update a table using SQL)

• Time and Date fields

  Using a date field that includes time can cause several problems. It may be a good idea to split the field into two- one to hold the date and the other the time
  • never call a field "date". You can lose your data unexpectantly
  • to query to find records for a particular date you may have to use the criterion
    between [datefield]&" 00:00" and [datefield]&" 23:59"
  • you cannot use a calendar control directly to filter records (get object does not support this property or method).
  • If you want to use a calendar control to find records, create two invisible unbound fields on the form which hold [calendarfield]&" 00:00" and [calendarfield]&"23:59" and create a button to
    
    apply filter criterion on these two fields
    or to open a query
    [datefield] Between [forms]![calendar]![when1] And [forms]![calendar]![when2]
    or to open a form based on the query
    stDocName = "student support date"
    stLinkCriteria = "[StartTime] between " & "#" & Me![when1] & "# and #" & Me![when2] & "#"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

• Combo box showing unique values

  To have a list or combo box just show the values relevent to another field on the form rather than all the values, set the row source to Table/Query and row source in the properties of the list box to :
  SELECT DISTINCT [CAR].[MAKE], [CAR].[MODEL] FROM [CAR] where make=forms!car![make];

• Importing Web form answers

  You have created a web form to send answers back as email, and then want to store the answers in a database, or you have a file containing tagged data (eg from a reference search to Web of Science or Medline). Problems:
  • each record of data is held on more than one line
  • there are a variable number of boxes filled in, so variable number of lines for each record
  • mixture of text and numeric data
  • comments have linefeeds in
  • Because Access puts data on each newline into a new record then the easiest solution is to edit the email first to remove all the newlines. To do this replace ^p by ~, then replace the first tag by ^ptag. I also had problems with quotes so removed all single quotes. See example of original email, edited email and the code to load it into Access(two examples updated Nov 2000)

• Leading zeros required

  Student identification codes do not show the leading zeros This is because the identification code was set to long byte and should now be set to text (length 7). Numbers do not display leading zeros so a code in the form 0001234 will look like 1234. To solve this problem
  • change the data type of the field from number to text
  • create an update query to put back the leading zeroes (take a backup of the table first!). The first query puts a single 0 in front of student codes that are length 6 ie 123456 will be converted to 0123456. Be careful- Microsoft sticks quotes in for you- which is disastrous since it will change student codes into the the text 00student_code- so do be careful to put square brackets around all field names
    field: [student_code]
    update to:'0'&[student_code]
    criterion:len([student_code]=6
    
    run the query again to stick two zeros into length 5 codes
    field: [student_code]
    update to:'00'&[student_code]
    criterion:len([student_code]=5
    
    etc

• creating a sequence number

  This is particularly useful when you are using Oracle and cannot use an autocounter as a default value:
  create a module function (we will be updating the system_our_ref field in the one row our_refs table which holds the next value):

  
  Function next_system_ourref()
      Dim Mydb As Database, Mytable As Recordset
      Set Mydb = DBEngine.Workspaces(0).Databases(0)
      Options = DB_DENYWRITE + DB_DENYREAD
      Set Mytable = Mydb.OpenRecordset("Our_refs", DB_OPEN_TABLE, Options)
      ourref = Mytable("system_our_ref")
      ourref = ourref + 1
      Mytable.Edit
      Mytable("system_our_ref") = ourref
      Mytable.Update
      Mytable.Close
      next_system_ourref = ourref
  End Function
  

  then create a macro to set the id value to next_system_ourref(). Use this macro on a before insert event on the form (you will need to refresh the record to see it)

• Sharing a database

  If the users are sharing a database on a network drive, get them to create a new folder below the folder that has been allocated to them and put the database in that. That way they have control over the security rights of the folder. They can then make sure that they have full control over the folder holding the database. The problem is that the first person who opens the database creates a file of type .ldb which the others can not write to unless the others have the correct access

• Dreamweaver Problem

  You may want to have a staff list with a photo available for each record. You may get problems pulling in OLE and URL field types into a Dreamweaver ASP page. Basically images get converted to some random text and URL fields appear as plain text but they are not active URLs, ie they have lost their behind the scenes hyperlink information and become unclickable. To have OLE links and/or URLs pulled into a HTML table in an ASP page that remain usable, follow the notes at: http://www.macromedia.com/software/ultradev/resources/access/prebuilt_solutions.html for the sample Online employee database. Basically the photo field in Access is a "Text" field containing, for example, the username of each member of staff. You create your Access ODBC DSN connection as normal and in Dreamweaver use this DSN connection to create a recordset pulling in the relevant fields from the table containing, amongst other things, the photo "Text" field.
  In Dreamweaver you add the Recordset fields to your ASP page and then modify the ASP code for the Photo field by preceeding it with the URL location of your images folder and adding a .jpg suffix. Obviously you then have to ensure that each image's suffix is .jpg and that each image file name is the same as the text used in the Photo field in the Access table.
  You can take this one step further and use an HTML <a>..</a> tag to display a small "bullet" point image in the Photo field (instead of the actual staff image) that was linked to the relevant staff image file. This way the images were not shown by default. This kept the table in the ASP page tidy (no actual images being displayed resulting in tall rows) and kept the ASP page size down.

  The version that comes with W2K results in some problems within Dreamweaver when opening a Recordset and drilling down to see the fields in a table or query - Dreamweaver displays error messages and has occasionally crashed. However, if the DSN in the Recordset uses the EXcel ODBC driver instead of the Access ODBC driver it worked fine.

• Crosstab Query

  Crosstab (Access 97) uses a self-join to provide a table of courses (with course as both row and column) and the number of students attending. The self-join enables one to show the combination of courses that the students are taking (David Atiyah Maths Dept 1 June 2000). To list the grades of the students for each subject, use student as row heading, subject as column heading and grade as value (first/ min/ max)

• Converting grid references to postcodes using Quickaddress

  Module to read a grid reference and convert to a postcode, or to assign all grid references postcodes

Installation and Corruption Problems

• How to create a data source to access Oracle data
• Installing Access 97 and Access 2000 on same machine
• Missing wizards not installed in Access 97 and Access 2000

• Installing VBA Help files in Office 2000.

  Each Office application has a set of VBA Help files that are separate from that application's Help files. The VBA Help files are available only from the VBA IDE window by pulling down the Help menu as you would in the Access window.
  However, if you installed Microsoft Office using the standard installation settings, Office did not install the VBA help files. If you want access to these files, you must specify them during the install process. You will probably not need the CD.
  • select Start, Settings, Control Panel, then double-click the Add/Remove Programs icon.
  • Click the Install/Uninstall tab or the Change tab (depends on the version of Windows) and double-click your version of Microsoft Office 2000.
  • In the first panel of the install program, click the Add or Remove Feature button.
  • click the plus sign next to the Office Tools item. You will find Visual Basic Help at the bottom of the list of tools.
  • Click the small triangle, select Run From My Computer or Install On First Use
  • click Update Now, and exit
  If you selected Install On First Use, Office won't install the files until you attempt to use them. The VBA help files will now be accessible

• Printer Problem

  There was a problem retrieving printer information for this object.
  This message may occur when you have a button to print a report and you may have several printers on the networkthat you can use (some of which may have been removed or incorrectly set up
  • check the properties of the button- check that printer information is not specifically in the code
  • open the report in design view, then do file/page setup, click on page tab and you can see whether your printer for the roster report is correctly set as default
  • from the main database window do file/ print and check the correct printer is set as the default printer (you can not do file/ page setup unless you are looking at a design or some results).
  • open an object and do file/ page setup and check the printer is set as the default printer.
  • If you look at settings/ printers from the computer start button, one of your printers should have a tick by it to show which is the default printer. Is your new printer the default printer? Are all the printers displayed indicated as being Ready
  • If you use the printer icon to print stuff then it uses the default printer- does this work?
  • if you use file/print then you can choose the printer- the default printer is shown and you can then choose the printer- can you print using the new printer? can you print using another printer?
  • The service patches for Access 2002 have addressed some printer issues, so you may need to check if you have Service Pack 2 installed. Choose About from the Help menu. If it does not say: Access 2002 SP-2, go to http://support.microsoft.com/?id=307841 and download the patch.

• Repairing a corrupt database

  To overcome a corrupted database
  • Try creating a new database and importing everything from the old database (using File/Get External data/import)
  • Try Tools/Database Utilities/Repair database
  • Sometimes one can open a corrupt Access 97 database using Access 2000
  • When you try to save the design of a table and you get the error message "Property Value is too large"- it usually indicates that you have too many columns. Access is not really very happy with more than 100 columns in a table especially when text rather than numeric. Try splitting the table.

• Viewing Access Database References

  To view the current database references:
  • Press ALT+F11 to start Visual Basic Editor.
  • On the Tools menu, click References.
  
  To add a reference to a library:
  • Under Available References, click to select the check box next to the name of the library, and then click OK.
  
  Resolving Visual Basic References in Access
  Access loads the pertinent file (for example, a type library, an object library, or a control library) for each reference, according to the information that is displayed in the References box. If Access cannot find the file, Access runs the following procedures to locate the file:
  • Access checks to see whether the referenced file is currently loaded in memory.
  • If the file is not loaded in memory, Access tries to verify that the RefLibPaths registry key exists. If the key exists, Access looks for a named value that has the same name as the reference. If there is a match, Access loads the reference from the path that the named value points to.
  • Access then searches for the referenced file in the following locations, in this order:
  • The Application folder (the location of the Msaccess.exe file).
  • The current folder that you see if you click Open on the File menu.
  • The Windows or Winnt folder where the operating system files are running.
  • The System folder under the Windows or Winnt folder.
  • The folders in the PATH environment variable that are directly accessible by the operating system.
  If Access cannot find the file, a reference error occurs.
  Resolving Reference Issues
  Creating a new, blank database and then importing objects from another database file can create reference issues if the code or ActiveX controls rely on references that are not included in a database by default. The default references for an Access 2000 database are: •
  • Visual Basic for Applications
  • • Microsoft Access 9.0 object library •
  • OLE Automation •
  • Microsoft ActiveX Data Objects (ADO) 2.1 library
  If the source is another Access 2000 database, verify that the references match. If the source is in an earlier version of Access, DAO 3.5 or earlier is probably in use; however, Access 2000 does not provide DAO 3.5 by default. Try removing the reference to the ADO 2.1 library (if it exists) and adding the reference to the DAO 3.6 object library.
  If you converted the database from an earlier version of Access, and the database contains a reference to the Utility.mda file, in most cases you can remove this reference because the functions that this reference calls are included in the default references in Access 2000. If there are references to earlier versions of DAO, you can also remove these references because DAO 3.6 can address these functions.
  

• 'You don't have the license required to use this ActiveX control'

  Comes up when using a macro on a button, which contains a filter (Access xp problem).
  A Missing flag, which you may see when you open a module in Design view and then click References on the Tools menu, indicates that the reference to the Common Dialog control on the target computer does not match the source in the database file from the development computer.
  If you distribute a database file without installing the distributable Common Dialog control, the control's reference may be flagged as Missing, or you may receive this error message if the non-distributable control is already installed on the target computer.
  Even when the database file is part of a run-time application, you may receive this error message if the non-distributable control that is already installed on the target computer is of a later version than the control that is provided by your run-time application. This issue can occur because the Setup program does not overwrite later versions of a file with an earlier version of the same file.
  Check your references against someone elses- see what it says against microsoft activex data objects library - should be in prog files\common files\system\system\ado\msdao21.tlb or something like that
  Try a patch for Windows XP and then turn off sandbox mode

• 'Action will reset current code' failure

  This may be caused by ODBC problems, or by renaming queries, but seems to a Microsoft bug. This problem was first corrected in Microsoft Office 2000 Service Pack 2. The query fails with This action will reset the current code in break mode. Do you want to stop the running code? To halt select yes. To leave the code in current state select No. The query freezes and one can only view it in SQL view and not Design view and if one then tries to run it, does nothing. Various solutions were tried
  • Close Access- no effect
  • Reboot- no effect
  • Import everything into a new database- no effect
  • Recompile code- option greyed out
  • Set connectiontimeout with regedit to 0- possible effect
  • Reinstallation of Access with later service pack (was on 9.0.4402 SR1)- did not try
  • Tools compact and repair- Access would not let me
  • To temporarily work around this problem, change the user identity that Access 2000 is set to. To do so, follow these steps:
    1 Click the Windows Start button and then click Run.
    2 In the Run dialog box, type Dcomcnfg.exe and click OK. This launches the DCOM Configuration Utility.
    3 In the list of applications, double-click Microsoft Access Application. This brings up the Properties dialog box for Access 2000.
    4 Click the Identity tab and select The Interactive User. This sets Access to run under the context of the user that is currently logged into the system.
    5 Click OK to close the Property sheet and click OK again to close the DCOM Configuration Utility.
    Breathe a sigh of relief
  • May be caused by connectiontimeout being ignored and set to 30 seconds- try setting commandtimeout

• 'Invalid Procedure Call'

  This may be cause by linking problems. Try relinking.
  It may also be a fault in a query, for example
  Asc(Mid([grooming],1,1))=asc('g')
  where the first letter of grooming was not g or was null

• 'Module not found' failure

  This can be caused by running out of disk space on the C: drive even though the database may be on a network drive. It can also be caused by compacting a database when other users are using it. This manifests itself by by possibly losing a database object, or corrupting a form. The form refuses to save, visual basic may refer to a missing form and an empty module is created which can not be deleted. The easiest way to sort this out is to create a new database and import all the objects except for the the corrupt form (it will stop importing when it finds a corupt object). Go to a backup copy and retrieve a good version of the object

• 'Application-defined or object-defined error'

  This can happen trying to look at linked tables, particularly after a conversion upgrade from an earlier version. With Oracle linked tables you also get the message 'ODBC call failed ORA 3114 not connected to Oracle. The solution is to drop all the linked tables and relink them. Merely refreshing the links will not do.

• Mailmerge date problems

  If mailmerge from Word to Access data or Excel data, the date comes out as mm/dd/yyyy regardless of how displayed in data source, and whether the date format is specified in Word merge file. The date has been set up as dd/mm/yyyy in International settings
  Solution is use mailmerge from Access, possibly using a query to format as required (the format function eg format(start_date,'d dddd m mmmm yyyy').
  The Excel solution is to change the date field to text but this has repurcussions on sorting

• Date problems

  May have problems using =Date() for a default value and in using date() in queries, forms or reports gives an error message of #name? or Function isn't available in expressions in query expression'Format ([Register Date],"ddmmyy"] = Format[Date(),"ddmmyy"])'
  • Using =Now() may work but inconvenient as includes time.
  • pop up the Debug window and check these functions work well there. Even a module may not be referenced in a query, report or form try to recompile all modules, including in forms where Access Basic may be hidden away in command buttons (look at the design of a button by pressing the three dots by the onclick event)
  • It may be caused by having a field called date- a reserved word- rename the field
  • If you receive an error message such as "Undefined Function" or "Function not available in query expressions," you probably lost some of your library references when you last upgraded. Open a module (if you have no modules, go to a form with buttons and look at the Access code, or click New on the Modules tab) and select References from the Tool menu. Check the listing in the Available References control. verify you have (the following apply to Access 97):
    Visual Basic For Applications
    Microsoft Access 8.0 Object Library
    Microsoft DAO 3.5 Object Library
    Microsoft Common Dialog Control 5.0
    Microsoft Windows Common Controls 5.0
    If you find one tagged Missing, uncheck it. From the Debug menu, choose Compile and Save All Modules. This should resolve your missing references.
  • Similar to above:
    1. On the Modules tab, click New.
    2. On the Tools menu, click References.
    3. In the References dialog box, pick any single reference that is not already selected, click to select it, note which one you selected, and then click OK.
    4. On the Tools menu, click References again.
    5. Click to clear the reference that you selected in step 4, and then click OK.
    7. Rerun the query, the form, or the report on which you saw the errors mentioned to see if the problem has gone away

• No permission

  Trying to open a database and it says "current user account doesn't have permission to convert or enable".
  If it was never secured by a workgroup administrator, and getting hold of the system.mdw file does not work, and it is the correct version of Access, try creating a new database and importing the database into it (File/get external data/import)

• LICENSE NOT FOUND ERROR IN ACCESS 97

  If you have attempted to install OEM professional version of MS Office 97 on a new machine and Word 97 and Excel97 loaded fine but Access 979 will not run. Instead it comes up with the message "can not find a license for Access 97 on this machine"
  To solve the problem: there is a file called c:windows/fonts/hatten.ttf that needs to be renamed to hatten.old. You then reinstall Office 97 and Access 97 works fine.

• The text is too long to be edited

  This happens if you are using a linked table to an Oracle database and the linked field is a clob (shown as a memo field in Access). It happens certainly if there are over 67,000 characters in the field and stops you from editing the field.
  • The easiest way to edit the field is to select the field and its primary key in a query, and copy the displayed values.
  • Paste the values into word or notepad and edit.
  • Create an update query to update the existing field- at this point if you use the normal query window it will tell you that it cant since the value is too big
  • Show the SQL window and edit the SQL (if there is double quotes in the text itself you can use single quotes)
    update table_name set large_field="lots of text" where primary_key_field=123;

• Error accessing file. Network connection may have been lost.

  or

  This action will reset the current code in break mode.

  The file is however not on the network it is on your own hard drive so this could not be the error. If usually happens when you open a form in MS Access 2000 with some record navigation vba code embedded
  • Try solving by creating a brand-new database and attempt to import objects from the OLD database, rather than trying to export to a new database. It frequently seems that it ends up being usually one, maybe a couple, of objects that are actually corrupting the entire database.
    During the import process within the new database, those objects that are outright corrupted will be flagged using a message box from Access, and it will usually attempt to continue importing any other objects that you had selected. Worst-case scenario is to import the tables and queries, and then one object or a few objects at a time, followed by a repair/compact on the new database each time. With that, it may help to do a backup on the new database every few objects or so as an insurance.
  • Although the form is stored locally, it may have a control source to some linked table. The linked table may be on a database that is on the network. Check the form's control source. Use the linked table manager to renew the links as some of them may have had the design altered
  • Office 2000 SP3 also fixes this issues Microsoft article 304548 You may receive one of these error messages when both Microsoft Access 2000 and version 6.3.91.8 of the Vbe6.dll file are installed on the computer, and either of the following conditions is true:
    • You imported forms or reports that contain code modules or standalone modules into a new Microsoft Access 2000 database by using Microsoft Access 2000.
    • • You copied and pasted forms or reports that contain code modules or standalone modules into the Microsoft Access 2000 database by using Microsoft Access 2000.
    Then, you close and reopen the database and try to view, execute, or compile the code.

Import Problems

Performance Problems

Using MSysObjects to find object creation dates

MSysObjects is a hidden table that contains detailed information about every object in your database. Knowing how to query this table can come in handy when you need to verify that a table, query, or form exists, or when it was created or last updated. If you select Options from the Tools menu and make sure System Objects is ticked, you will see an extra 8 tables on the Tables part of the main database windows, all prefixed by MSys. MSysObjects contains a number of fields, but you will probably most often refer to five key fields:
• Name (the name of the object)
• DateCreate (the date and time the object was created)
• DateUpdate (the last date and time the object was changed)
• Connect And Database (information about connections and linked tables)
• Type (a number identifying the object's type)

  Table	1
  Query	5
  Form	-32768
  Report	-32764
  Macro	-32766
  Module	-32761
  Relationships	8

For example, to generate a list of all table names and creation dates in your database, create a query based on MSysobjects with the condition type=1 and name not like MSys*.

Exporting Graphs to Word, HTML or Excel

If you want to export a graph to Microsoft Excel and it is on a report, you must copy the graph to a form first because you only see sample graph data on the Excel worksheet. You can then copy it from the form and paste it into Excel or Word. If the graph is already in a form, you need only to open the graph in design view, set the properties as described below and then follow the rest of the steps. This method applies to both Access 97 and Access 2000 (if you are using Access 2000, if you right click the appropriate report you will find you can not save a report as a form, only a form as a report so this potential facility will not speed up converting a report to a form).

• Open the report containing the graph in Design view.
• Click the graph object to select it, and then on the Edit menu, click Copy.
• Create a new form not based on any table or query in Design view.
• On the Edit menu, click Paste. The graph object appears on the form. Note that the graph displays sample data in Design view.
• Set the following properties for the graph object: Enabled: Yes, Locked: No
• View the form in Form view (not Print Preview) so the graph can calculate actual data.
• Click the graph object to select it (you can not select it if you use Print Preview), and then on the Edit menu, click Copy.
• Start Microsoft Excel or Word
• On the Edit menu, click Paste to insert the graph.

To view actual data in the chart in Design view of the form, follow these steps:

• Set the graph's Enabled property to Yes and Locked property to No.
• Close and save the form.
• Open the form in Form view.
• Click the Graph object so it has the focus.
• On the Edit menu, point to Chart Object and then click Open to start Microsoft Graph.
• Note that Microsoft Graph displays the actual data.
• On the File menu, click Update.
• On the File menu, click Exit & Return to (where is the name of your form).
• Close the form.
• Open the form in Design view. Note that the sample data has been replaced by the actual data.

Newsgroup

• There is an Access newsgroup comp.databases.ms-access that you can join, also microsoft.public.access
• Dejanews is a good place to look for past postings to newsgroups

Microsoft Access Sites to Visit for information

• Microsoft siteFree Microsoft software and search the MS Knowledgebase
• Woody's Office Watch, Web site and mailing list that tells you all you need to know about what's in, what's coming, what's hot and what's not with the Microsoft Office product suite. It's well worth subscribing to, even for the casual Office user and it's free.
• Woody's Secret Lounge is a well-laid out popular forum for asking and answering questions about all Office products
• Rogers Access library full of useful examples
• TechRepublic daily Access Tips
• Access Web A useful FAQ site for Access developers
• There is alot of useful information on Allen Brownes Home page
• Digital Dreamshop Access Developer's Page
• Kayodeok's MS Access How tos
• Password recovery software
• Access Database Repair and Recovery - provide instant, 24HR Microsoft Access database repair service, 7 days per week, worldwide. Free corrupt database analysis and report.
• Recovery utilities for corrupted Access, FoxPro, Paradox, MS SQL, Interbase and Sybase databases. Available standalone and in DBRecovery suites. From small Access databases to multi-database environments - there is a specific solution waiting. Click for a free demo
• PK Solutions (Peter Miller covers version reversal, security problems, etc.)
• Tony's Main Micrsoft Access Page
• Access Tips from ACG Soft
• Leszynski/Reddick Naming Conventions