http://www.songhaysystem.com/kb/subject/acc97 The Funky Knowledge Base at songhaysystem.com. The subject of this channel is Access 97. Wed, 19 May 2004 04:38:57 GMT http://www.songhaysystem.com/kb/number/-253764156/subject/acc97 Article Excerpt: Enumerated constants are not supported in Access 97. These were introduced in Visual Basic 5.0. Wed, 19 May 2004 04:38:57 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/-2027653601/subject/acc97 Article Excerpt: Public Sub basRenLinkTbls() ' 'This Sub removes the 'dbo_' prefix from linked Tables. ' Dim DAOTblDef As DAO.TableDef With Application For Each DAOTblDef In CurrentDb.TableDefs If VBA.InStr(1, DAOTblDef.Na… Mon, 13 Dec 1999 06:23:25 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/-1374649060/subject/acc97 Article Excerpt: Public Sub basMessagingLink(Optional IsAddressBook = False) Dim tdfDAO As TableDef Dim strConn As String g_strMsg = "Are you sure?" & vbCrLf _ & "TIP: Outlook Express should not " _ & "be your default MAPI client " _ … Thu, 09 Sep 1999 23:02:14 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/157/subject/acc97 Article Excerpt: Private Sub basReadTextFile() ' 'This sub reads a text file line by line. 'It updates the progress meter as it reads the file. 'The LOF() function is used as the known value 'the progress meter refers to. The lineCounter variable '… Thu, 09 Sep 1999 22:45:46 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/-199004323/subject/acc97 Article Excerpt: When an Access .MDB is linked to a table from a remote database (e.g. SQL MS Server) that is known to be valid (not dropped or locked) and the rows in the table are full of "#Error" strings then try to SELECT * from the table via an Access Pass-Through Qu… Wed, 08 Sep 1999 05:06:05 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/140818643/subject/acc97 Article Excerpt: Public Sub basRenLinkTbls() ' 'This procedure renames all tables starting with "dbo_". ' Dim DAOTblDef As DAO.TableDef With Application For Each DAOTblDef In CurrentDb.TableDefs If VBA.InStr(1, DAOTblDef.Name, _ … Wed, 08 Sep 1999 05:21:49 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/-1342914425/subject/acc97 Article Excerpt: When you use one Access .MDB file to link to multiple remote (SQL Server) databases from the Database Window, you will need multiple ODBC entries for each database you want to link to. This implies that, if you want to link to a table in database DB1 on … Sat, 31 Jul 1999 16:02:41 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/901212257/subject/acc97 Article Excerpt: Using Alt-key combinations for the Tab Control may not work all the time in Access 97. What I have noticed is when the access keys are pressed, the focus does not change but the application "beeps." This problem is intermittent. It appears to work correct… Thu, 29 Apr 1999 01:39:44 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/901830774/subject/acc97 Article Excerpt: Quite often the return value supplied by the Application object's SysCmd() Method is not needed. Instead of wasting a variable to store these values, use the Call keyword. Recall that when the Call keyword is used on a function (or a method that returns a… Sun, 25 Apr 1999 06:12:14 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/26900681/subject/acc97 Article Excerpt: Linked SQL Server Tables in Microsoft Access 97 represent the SQL definition of the table defined in the remote database. This implies that if a column is defined with type char(10), Access will show in Datasheet View 10 characters per row in the column. … Sun, 28 Mar 1999 06:53:53 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/920624328/subject/acc97 Article Excerpt: If the .RecordCount property of DAO Recordset is equal to 1 and opens without error, then most likely this Recordset is based on Parameters. In order to get an accurate count you will have to resort to using .MoveLast and .MoveFirst in the following manne… Fri, 05 Mar 1999 17:07:10 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/916184155/subject/acc97 Article Excerpt: According to the Microsoft Knowledge Base article Q101316 ("ACC1x: 'Maximum Size Reached' Error Attempting to Import File"), Access 1.0 databases had a definite maximum size of 128 megabytes (MB), or 131,072,000 bytes. Access 1.1 databases can reach up to… Thu, 14 Jan 1999 04:32:11 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/891156366/subject/acc97 Article Excerpt: ' 'This code replaces the use of DLOOKUP and other 'domain lookup functions (e.g. DFIRST or DLAST) not 'available in VB. ' Dim strSQL As String, rsDAO As Recordset Dim varReturn strSQL = "SELECT Subject FROM tblKBSubjects" Set rs… Tue, 12 Jan 1999 06:19:51 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/-462494696/subject/acc97 Article Excerpt: Microsoft does a great job warning users that Replication Design Masters will be significantly larger in size than MDB files not in a Replication set. What is not underscored (at runtime) is the fact that the Tools > Replication > Synchronize Now... comma… Tue, 01 Dec 1998 01:24:13 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/1449895993/subject/acc97 Article Excerpt: I have encountered Insert and Delete errors after replication synchronization when the replicable objects include two tables in a one-to-many relationship. Delete errors seem to appear when only one record in the "many-side" table, relating to one record … Tue, 01 Dec 1998 01:37:51 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/912442279/subject/acc97 Article Excerpt: The following error message may occur after selecting Tools > Replication > Create Replica...: "Microsoft Access can't convert the database to a Design Master. Cannot make the MSysCmdBars object in Tables container replicable." This problem is most likely… Mon, 30 Nov 1998 16:21:11 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/910149149/subject/acc97 Article Excerpt: Every application that I build means to reflect the three-tier abstract model within Microsoft's software design guidelines. One of those tiers is Data Services. With Access 2000 and ADO in mind, I have distilled the core data services of a DAO (native IS… Mon, 30 Nov 1998 16:38:59 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/909887455/subject/acc97 Article Excerpt: It was difficult for me to unify my understanding of my user-defined classes with the idea of a Form as a class. I used to draw a blank when I saw that Access calls a form code module a "Class Module." But now I have an understanding: The code behind for… Mon, 30 Nov 1998 16:47:07 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/908580227/subject/acc97 Article Excerpt: The TabStrip Control in the Microsoft Windows Common Controls 6.0 library (MSCOMCTL.OCX) is not as "robust" as the Tab Control native to Access. The greatest difference appears to be that the VB TabStrip Control is not a container. This means that at desi… Thu, 05 Nov 1998 21:25:35 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/909943719/subject/acc97 Article Excerpt: When a DoCmd Action is called from VBA code but cancelled by user a runtime error occurs. This is error 2501. I usually trap this VBA.Err and Resume to the exit line label in my error handling structure. Sun, 01 Nov 1998 18:12:45 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/450133044/subject/acc97 Article Excerpt: Access 97 online Help explains in detail the difference between Value and Text for data bound controls. This difference may be very important in the case of Combo Boxes where text displayed in the control is not the value stored in the bound column (Contr… Sun, 01 Nov 1998 06:13:55 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/894430404/subject/acc97 Article Excerpt: The ItemData(<index>) property in Visual Basic is an array of long integers corresponding with the List(<index>) property of the combo box or list box. For an unbound list or combo box control in Access, ItemData(<index>) returns the first column of the q… Sun, 01 Nov 1998 06:25:31 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/909695763/subject/acc97 Article Excerpt: The Like operator is case sensitive when Option Compare Database is used (in my locale). This of course means that if strTest = "foo" then (strTest Like "fo*") = True and (strTest Like "Fo*") = False Thu, 29 Oct 1998 21:16:25 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/901129341/subject/acc97 Article Excerpt: Private Sub Form_BeforeUpdate(Cancel As Integer) Dim strMsg As String strMsg = "Are you sure you want to change this data?" If VBA.MsgBox(strMsg, vbYesNo + vbDefaultButton2 _ + vbQuestion) = vbNo Then Me.Undo Exit Su… Thu, 29 Oct 1998 21:34:45 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/909252811/subject/acc97 Article Excerpt: The Microsoft KB details how to attach to a Microsoft Outlook folder in "ACC97: Sample Procedure to Attach a Microsoft Outlook Folder" (Article ID Q161174). I found dealing with MSIE4's Outlook Express a different matter. Since I could not get the prope… Sat, 24 Oct 1998 18:25:45 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/895269295/subject/acc97 Article Excerpt: Use the Show method to open VB forms. This is quite different from the robust functionality available in Access' OpenForm method of the DoCmd object. There is only one optional parameter in the Show method, Owner. Note: Using the Show or Hide method on a… Tue, 20 Oct 1998 21:25:49 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/1041516736/subject/acc97 Article Excerpt: DAO: Data Access Object A DAO is an automation interface for Accessing the Jet Database engine. In fact Access is an IDE/GUI for Jet. DAO is described by a containment hierarchy beginning with the DBEngine object. The DAO object hierarchy is shown on pag… Thu, 01 Oct 1998 20:42:36 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/-1738372698/subject/acc97 Article Excerpt: The "Default Instance" of a Form The Application object refers to all of the forms in an Access .MDB via the Forms collection. This collection contains class modules that happen to be "automatically instanced" when the .MDB file is loaded. The Object Br… Thu, 01 Oct 1998 20:42:17 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/-443345831/subject/acc97 Article Excerpt: Declaring a Collection Variable A user-defined collection is a new instance of a collection object. Its "instancing" requires the New keyword. The abbreviated way of declaring a new collection object has the form: Dim <my collection object variable>… Thu, 01 Oct 1998 20:42:08 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/892253368/subject/acc97 Article Excerpt: To promote scalability and portability of custom code within the VBA/VB world, Access macros should be replaced by code modules whenever possible. Macros can be replaced by code modules except in the following cases: An AutoExec macro can be used when th… Thu, 01 Oct 1998 20:41:57 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/891118918/subject/acc97 Article Excerpt: Assigning Objects to Variables Assign objects to a variable by using the Set statement. Generic Object Types One advanced VBA programming technique is the use of generic object types. There are two types of generic objects specified by the declaration… Thu, 01 Oct 1998 20:41:40 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/133/subject/acc97 Article Excerpt: Private | Public Use the Private and Public keywords to declare variables. This can be done at the module level only. At the module level, the keyword Private replaces keyword Dim. The old keyword Global is completely replaced by Public. VBA Naming Con… Thu, 01 Oct 1998 20:41:27 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/-1877307985/subject/acc97 Article Excerpt: In order to distinguish the BeforeUpdate event from the Change event, think of the BeforeUpdate event and AfterUpdate event in DAO transaction terms. The BeforeUpdate event fires just before a transaction is committed to the database. It marks the time wh… Fri, 18 Sep 1998 23:39:29 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/904774793/subject/acc97 Article Excerpt: If you find yourself using a lot of Boolean variables to flag states and you do not feel the need to build a class, consider using the various Tag properties for controls (including Form objects). Wed, 02 Sep 1998 22:23:12 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2012529411/subject/acc97 Article Excerpt: Functions of type Variant, by default, return Empty not Null. Variant-type functions are useful when used to update rows in a table because these functions can return Null. However Null must be specified as the default value of such functions. If the func… Fri, 07 Aug 1998 15:42:46 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/897660496/subject/acc97 Article Excerpt: This problem may not always be caused by a duplicate value trying to enter a unique index. There may also be a one-to-many relationship defined in the Relationships window where a change to the index on the one side of the relationship causes a key violat… Fri, 07 Aug 1998 15:50:51 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/901745356/subject/acc97 Article Excerpt: If Filter by Form does not work in the Run-Time version of Access 97 then please see MSKB article Q172090 ("ODE97: Filter-By-Form Not Available in Run-Time Applications") for more information. Wed, 29 Jul 1998 20:50:54 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/901549424/subject/acc97 Article Excerpt: Filter by Form (applying the filter): Filter Event; FilterType = acFilterByForm = 0 Apply Filter Event; ApplyType = acApplyFilter = 1 Current Event During this Apply Filter event, Access changes the FilterOn property to True. This implies tha… Wed, 29 Jul 1998 20:48:50 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/901432393/subject/acc97 Article Excerpt: Replace the IsLoaded() function that was available from Microsoft since Access 2.x with the Visible property of the default instance of an Access 97 form. For example, if the form frmMain has a module and is open then Form_frmMain.Visible = True Sup… Wed, 29 Jul 1998 00:18:33 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/899143924/subject/acc97 Article Excerpt: If the default instance of a form is not found, this most likely means that it does not have a Class Module. For example, suppose you have two forms in a database, frmMain and fsubOther, where frmMain has a module and fsubOther does not. Also suppose that… Tue, 30 Jun 1998 01:02:51 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/899168609/subject/acc97 Article Excerpt: The Column Headings in the Datasheet View of a form or subform are equal to the Name properties of their respective Controls. By default, the Name properties of bound controls are their respective Field Names from the underlying table or query. (The Capti… Tue, 30 Jun 1998 01:15:11 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/898280413/subject/acc97 Article Excerpt: Suppose you have a function bound to a field in a query or form. This function performs calculations and then returns the number as a string. This will be the case when you want to use the Format() function in the bound function or return custom text mess… Fri, 19 Jun 1998 18:32:12 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/196/subject/acc97 Article Excerpt: For attached tables in native Jet format (Access attached tables), referential integrity rules are enforced in the originating .MDB file. You cannot "re-establish" a relationship between linked tables that already have referential integrity defined elsewh… Thu, 18 Jun 1998 04:04:05 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/197/subject/acc97 Article Excerpt: Assuming Microsoft never changes its knowledge base cataloging system, refer to Q163997. Thu, 18 Jun 1998 04:01:30 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/360101780/subject/acc97 Article Excerpt: The note under Microsoft Visual Basic for Applications entitled "Review of the Data Type Summary: Floating Point Numbers" concludes that 99 billion is the highest decimal number that can be stored in an Access (DAO) database. This is true only under the f… Sat, 13 Jun 1998 06:21:26 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/897583026/subject/acc97 Article Excerpt: The FileDateTime(<path string>) function returns a Variant of sub-type Date that is the time stamp of the file specified in <path string>. Thu, 11 Jun 1998 16:40:21 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/25904967/subject/acc97 Article Excerpt: When you convert an Access database to a design master with a replicable table that has data type AutoNumber with the NewValue property set to Increment, this property will be changed to Random. The random number will range in value from -2,147,483,648 to… Tue, 12 May 1998 19:54:41 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/889221288/subject/acc97 Article Excerpt: All replication synchronization data errors are stored in a hidden system table called MSysErrors. It has column (or field name) called RowGUID that can be used in joins with the table generating the error. The join between the MSysErrors and the table i… Fri, 06 Mar 1998 22:03:16 GMT Bryan Wilhite