This article was previously published under Q298174
Moderate: Requires basic macro, coding, and interoperability skills.
This article applies only to a Microsoft Access database (.mdb).
NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.
SUMMARY
This article describes how you can use Data Access Objects (DAO) to import all objects from one Microsoft Access database into the current Access database. In some situations, this code can be used to recover database objects from a corrupted or damaged database that can be opened but cannot be compacted successfully. This code does not import the following elements:
•
References
•
Import/Export specifications
•
Security information (user and group permissions)
The current user (typically the administrator) becomes the owner of all imported objects.
MORE INFORMATION
Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please visit the following Microsoft Web site:
Malcolm Stewart Systems Support Engineer, Microsoft Access
Created: July 7, 1993
Abstract
This article explains how Microsoft® Access® stores dates and times so that you can do date/time manipulations that are not covered by the many date/time functions in Microsoft Access.
Introduction
When you use Date/Time fields in Microsoft® Access®, you will find a wealth of functions provided to manipulate dates and times in various ways. But if you want to do something that these functions don't support, you will need an understanding of how Microsoft Access stores dates and times in order to do your own Date/Time calculations.
The Microsoft Access Date/Time data type is both simple and powerful in its implementation. Although Microsoft Access can present a date or time to you in several different formats, internally they are stored as double precision numbers.
Dates
In order to be able to store a date as a number, the date has to be converted to something other than the standard calendar format. The numeric representation of dates is called a Julian, or Serial, date. To do this, the date is converted to an offset from a fixed point in time.
In the case of Microsoft Access, day 0 is 12/30/1899, and all dates are stored as the number of days since this date. Thus 7/7/93 is stored as 34157, meaning 34,157 days since 12/30/1899. Negative numbers represent dates prior to 12/30/1899. Storing Serial dates is what makes Date/Time a powerful data type that is easy to manipulate.
Because dates are stored as numbers, you can subtract two dates to find the number of days between them, or add a number and a date together to find another date either in the future or the past. Try the following examples in the Immediate window of a module.
Find the number of days since the beginning of the year:
Print #7/7/93# - #1/1/93# 187
Find the date 45 days in the future:
Print #7/7/93# + 45 8/21/93
Find the date two weeks ago:
Print #7/7/93# - 14 6/23/93
Times
Times are also stored numerically. Since adding 1 to a date represents 1 day or 24 hours, each hour is stored as .041666..., or 1/24 of a day. In Microsoft Access all times are stored as a fraction of a day. Each hour is 1/24 of a day, each minute 1/1440, each second 1/86400. So 3:00 is stored as .125 (or 1/8 of a day), and 16:00 is stored as 0.666..., (or 2/3 of a day). Conversely, 0.2 represents 4:48 hours (1/5 of a day), and so on.
As with dates, you can add and subtract times. Try the following examples in the Immediate window of a module.
Add a time interval:
Print #1:45PM# + #3:27# 5:12:00 PM
Subtract a time interval:
Print #1:45PM# - #4:06# 9:39:00 AM
Add two intervals:
Print #6:57# + #7:45# 2:42:00 PM
Note that in the last example, the internal data is correct but the default format is not appropriate for the calculation. Microsoft Access displays the result using the 12-hour default format, instead of a 24-hour format. You can use the Format function to force the correct format, or on a form or report, you can set the Format property of the field to "Short Time" in order to display using the 24 hour format.
Add two intervals that exceed 24 hours:
Print #15:45# + #12:02# 12/31/1899 3:47:00 AM
The result should be 1 day and 3:47 hours. Internally, the interval is stored correctly. However, Access has no format to display the "1" as "1 Day", so the "1" is displayed as a date, namely 12/31/1899 (1 day after 12/30/1899).
Intervals
This brings to light an interesting feature about dates and times in Microsoft Access. Midnight is represented by .0, which means that 34,157 is the same as 34,157.0 or 7/7/93 0:00. Also, 0 represents 12/30/1899, so .125 is the same as 0.125 or 12/30/1899 3:00 a.m. Access normally suppresses displaying the time component if 0, and suppresses displaying the date component if 0, thus giving the appearance that dates and times are independent of each other.
When displaying time intervals of greater than 24 hours, the date portion is no longer 0 and Access will default to displaying a date. We can overcome this limitation of the Date/Time data type in one of two ways: The first is to treat the interval as a decimal value rather than a Date/Time value, as shown in the following examples.
Add two intervals and display as a decimal number of days:
Print format(#15:45# + #12:02#,"#.00") & " Days" 1.16 Days
Add two intervals and display as a decimal number of hours:
NoteThe ".00" and ".0" in the formats above tell Microsoft Access how many decimal places to display. The "#" in the format represents the integer portion of the number.
The second way to overcome the limitation is to use a custom function to treat the number of days as a decimal value, and the remainder as a time value. The following function will display the interval correctly:
Function DaysHoursMinutes (Interval) Dim Days As Long, Remainder As Double Days = CLng(Interval) Remainder = Interval - Days * DaysHoursMinutes = Days & IIf(Days = 1, " Day ", " Days ") & * Format(Remainder, "h:mm") & " Hours" End Function
NoteLines marked with * need to be entered as a single line in Access Basic.
Add two intervals and apply custom formatting:
Print DaysHoursMinutes(#15:45# + #12:02#) 1 Day 3:47 Hours
The same formatting principles illustrated in the previous example are also useful when applied to intervals calculated as the difference between two Date/Time values.
Calculate elapsed time as a decimal number of days:
Display the elapsed time using the DaysHoursMinutes function:
Print DaysHoursMinutes(#7/7/93 6:45PM# - #7/3/93 8:15PM#) 4 Days 1:30 Hours
NoteWhen calculating the difference between Date/Time values, Microsoft Access defaults to a numeric format rather than a Date/Time format.
Conclusion
This covers the internals of Date/Time fields in Microsoft Access. By building on these examples and using the built-in Date/Time functions, you can create powerful date, time, and interval manipulation functions for your own database.
16th-Century Calendar Revision
A side note: In the 16th century, the calendar was revised and lost about 11 days. In Microsoft Access, the conversion between serial dates and calendar dates does not take this gap into account. The only real effect of this is that (a) you can enter the 11 dates that never existed, and (b) you can't do accurate date math if the dates span this range: For more info:
This article was previously published under Q210340
Advanced: Requires expert coding, interoperability, and multiuser skills.
This article applies only to a Microsoft Access database (.mdb).
SUMMARY
This article shows you how to create and use a procedure to display the current line or row number in a subform.
NOTE: This article explains a technique demonstrated in the sample file, FrmSmp00.mdb. For information about how to obtain this sample file, please see the following article in the Microsoft Knowledge Base:
233324 (http://support.microsoft.com/kb/233324/EN-US/) Microsoft Access 2000 Sample Reports Available in Download Center
MORE INFORMATION
The following examples demonstrate how to create and use the sample function, GetLineNumber().
NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click ReferencesTools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.
on the
1.
Open the sample database Northwind.mdb.
2.
Create a module and type the following line in the Declarations section:
Option Explicit
3.Type the following procedure:
Function GetLineNumber (F As Form, KeyName As String, KeyValue)
Dim RS As DAO.Recordset Dim CountLines
On Error GoTo Err_GetLineNumber
Set RS = F.RecordsetClone
' Find the current record.
Select Case RS.Fields(KeyName).Type
' Find using numeric data type key value.
Case dbInteger, dbLong, dbCurrency, dbSingle, dbDouble, dbByte
The GetLineNumber() function requires the following three parameters:
•
The form object on which to place line numbers.
•
The name of the unique key field in the subform's underlying table. If the record source does not have a single unique key field, add a field with an AutoNumber data type to the underlying table for this purpose.
•
The current key field value.
You can use the following sample expression as the ControlSource property setting of a text box on a subform, if the subform's underlying table has a field called ID as its unique key field:
=GetLineNumber(Form,"ID",[ID])
How to Use the GetLineNumber() Function
CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.
1.Open the Order Details table in Design view, add the following field to the table, and then save the table:
Field Name: ID Data Type: AutoNumber
This field will serve as the required single unique field for the table.
2.
Open the Order Details Extended query in Design view, add the ID field from the Order Details table to the query grid, and then save the query.
3.
Open the Orders Subform form in Design view and add the following text box to the form:
Name: LineNum
ControlSource: =GetLineNumber([Form], "ID", [ID])
4.
On the View menu, click Tab Order. Drag the LineNum field from the bottom of the Custom Order list to the top, and then click OK.
5.
Save and close the Orders Subform.
6.
Open the Orders form in Form view and move to a record with multiple order line items. Note that the LineNum text box displays the record number for each product in the order.
There are three ways to refer to data sources physically located outside an Access database in a SQL statement:
Use linked tables
Use the IN clause
Use direct references to the external tables
The most efficient and easiest way to reference external tables is to use linked tables. Sometimes though the linking must be done dynamically by the user because data sources are always changing. This situation arises when polling for data for summary or collection. Another situation prohibits altogether the dynamic linking or importing of tables. This arises when users are using an Access 2.0 database in an Access 95 environment. Unless the database is converted, users cannot create new objects, thus eliminating linking or importing tables.
A simple solution to this would be prompting the user for the name and location of the datasource via a common dialog or input box. Then execute dynamic SQL which would append the data to a preexisting table.
Since a dynamic SQL statement is being executed, no objects are created.
An example is listed below:
Sub GetOutsideData()
''' Function: GetOutsideData() ''' ''' Comments: This routine prompts the user for the location of the database ‘’’ containing the data to import. A dynamic SQL statement is constructed ‘’’ building the connect string into the SQL statement. The statement is ‘’’ then executed and the data appended to a preexisting table. It is ‘’’ assumed the user the table and field names of the outside table is ‘’’ constant. ''' ''' Arguments: none ''' ''' Returns: none ''' ''' Called by: ''' ''' Keywords: APPEND QUERY ''' ''' Date Developer Action ''' ------------------------------------------------------------------------- ''' 8/09/96 Marty Wasznicky created
On Error Resume Next
Dim varReturn As Variant, db As Database Dim szdatasource As String, szSql As String, szMsg as string Dim szTitle as string, szAnswer as string
szMsg = "Enter Path to database" szTitle = "Run Data Demo" szAnswer = InputBox$(szMsg, szTitle)
Const gcErrDiskorNetwork = 3043
If Len(szAnswer) < 1 Then Exit Sub
Set db = CurrentDb()
szSql = "INSERT INTO tblTest ( PMC ) SELECT DISTINCTROW LIMS_CUST.PMC FROM _ LIMS_CUST IN " & Chr$(34) & szAnswer & Chr$(34) & ";"
db.Execute Trim$(szSql)
''' This traps the network error that occurs when the data file is ''' located on a network drive instead of the local hard disk. ''' This error only occurs in Access 7. Also this only occurs using a Novell ''' network client and the extension to your data file is ".dat"!!!!!
If Err = gcErrDiskorNetwork Then MsgBox "You place data file on local hard disk" End If
I used to get data from Finance in Excel format and they wanted me to put them into an Access Database. The imported tables always end up missing with few column fields necessary in the MS Access Reporting later on. Here is how I did it.
Private Sub CMD_ImportXlsTable_Click()
On Error GoTo Err_CMD_ImportXlsTable_Click
If IsNull(Me.FileNameCombo) Then
prompt = MsgBox("Please choose the Filename to process.", vbOKOnly, "Warning!!!")
Exit Sub
Else
ImportXlsTable
If Me.FileNameCombo = 2 Then'WIRELESS BALANCE ON HAND
DoCmd.SetWarnings False
'***This corrects the entry WNPM into WNPM-W StockRoom
DoCmd.OpenQuery "Update Wireless WNPM to WNPM-W Q", acViewNormal
'***This corrects the entry in LOC_AV field by removing the blank prefixes
DoCmd.OpenQuery "Update Imp BOH LOC_AV Q", acViewNormal
DoCmd.SetWarnings True
Else
GoTo ProcessCartons
End If
ProcessCartons:
If Me.FileNameCombo = 7 Then 'Cartons Wireless issued not shipped
'***ADD Additional Column Fields called:
'***Found, Remarks
Dim SQL As String
For i = 1 To 4
Select Case i
Case 1
SQL = "ALTER TABLE [Wcartons issued not shipped] ADD COLUMN Found YESNO"
Moderate: Requires basic macro, coding, and interoperability skills.
SUMMARY:
This article describes two methods that you can use to start to compact one or more databases automatically at a scheduled time. In the first method, you create a small database with a table, a form, and a macro. The table stores the names of the databases you want to compact. The form contains a procedure in the Timer event that starts to compact the databases whose names are in the table. The macro opens the form every time you open the database.
In the second method, you can use a scheduling utility, such as the NT Schedule Service, to open an Access database by using the /compact command line switch.
This article assumes that you are familiar with Visual Basic for Applications and with creating Access applications by using the programming tools that are provided with Access. For more information about Visual Basic for Applications, see your version of the "Building Applications with Microsoft Access" manual.
NOTE: Visual Basic for Applications is called Microsoft Access Basic in Access version 2.0. For more information about Access Basic, see the "Building Applications" manual.
SCENARIO: A certain table called API Temp Data needs to have an incremented numbering on ItemNo field. If we use autonumbering we cannot have multiple users entering at the same time in a given table. One item entered may contain number 1 by one user and the other user my have it’s entered item numbered as 2. In order to facilitate the automated numbering system starting with 1 with each user without using the autonumber of MS Acces is to create a procedure that will do the trick for you.
PROCEDURE:
The following is a portion of the procedure in the submit Record click event.
’***START Of INS Procedure
’***Programmer: Noel Jerome Q. Noel
‘***Contact: noeljltd@hotmail.com
‘1. Count the number of Records based on the ControlNo that is in the Form
