Saturday, October 29, 2005

Importing Objects into MS Access

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:
http://www.microsoft.com/partner/referral/ (http://www.microsoft.com/partner/referral/)
For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site: http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS
For more info:
http://support.microsoft.com/?kbid=298174

Thursday, October 20, 2005

Inside Microsoft Access Date/Time Fields

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:

Print format((#15:45# + #12:02#)*24,"#.0" & " Hours"
27.8 Hours

Add two intervals and display as a decimal number of minutes:

Print format((#15:45# + #12:02#)*1440,"#") & " Minutes"
1667 Minutes
Note The ".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
Note Lines 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:

Print #7/7/93 6:45PM# - #7/3/93 8:15PM# & " Days"
3.9375 Days

Display the elapsed time as a decimal number of hours:

Print (#7/7/93 6:45PM# - #7/3/93 8:15PM#)*24 & " Hours"
94.5 Hours

Display the elapsed time as a decimal number of minutes:

Print (#7/7/93 6:45PM# - #7/3/93 8:15PM#)*1440 & " Minutes"
5670 Minutes

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
Note When 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
:

Thursday, October 13, 2005

How to Display Line Numbers on Subform Records

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

RS.FindFirst "[" & KeyName & "] = " & KeyValue

' Find using date data type key value.

Case dbDate

RS.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"

' Find using text data type key value.

Case dbText

RS.FindFirst "[" & KeyName & "] = '" & KeyValue & "'"

Case Else

MsgBox "ERROR: Invalid key field data type!"

Exit Function

End Select

' Loop backward, counting the lines.

Do Until RS.BOF

CountLines = CountLines + 1

RS.MovePrevious

Loop

Bye_GetLineNumber:

' Return the result.

GetLineNumber = CountLines

Exit Function

Err_GetLineNumber:

CountLines = 0

Resume Bye_GetLineNumber

End Function

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.
Reference URL: http://support.microsoft.com/?kbid=210340


Wednesday, October 12, 2005

Importing Data from and External Database

Reference URL: http://archive.baarns.com/access/faq/ad_quer.asp

There are three ways to refer to data sources physically located outside an Access database in a SQL statement:

  1. Use linked tables
  2. Use the IN clause
  3. 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

End Sub

VBA Error Codes and messages

Visual Basic Error Codes and Messages


 
3     Return without GoSub
5     Invalid procedure call
6     Overflow
7     Out of memory
9     Subscript out of range
10    This array is fixed or temporarily locked
11    Division by zero
13    Type mismatch
14    Out of string space
16    Expression Too Complex
17    Can't perform requested operation
18    User interrupt occurred
20    Resume without error
28    Out of stack space
35    Sub, Function, or Property not defined
47    Too many DLL application clients
48    Error in loading DLL
49    Bad DLL calling convention
51    Internal error
52    Bad file name or number
53    File not found
54    Bad file mode
55    File already open
57    Device I/O error
58    File already exists
59    Bad record length
61    Disk full
62    Input past end of file
63    Bad record number
67    Too many files
68    Device unavailable
70    Permission denied
71    Disk not ready
74    Can't rename with different drive
75    Path/File access error
76    Path not found
91    Object variable or With block variable not set
92    For loop not initialized
93    Invalid pattern string
94    Invalid use of Null
 
260   No timer available
280   DDE channel not fully closed; awaiting response from foreign application
281   No More DDE channels
282   No foreign application responded to a DDE initiate
283   Multiple applications responded to a DDE initiate
284   DDE channel locked
285   Foreign application won't perform DDE method or operation
286   Timeout while waiting for DDE response
287   User pressed Escape key during DDE operation
288   Destination is busy
289   Data not provided in DDE operation
290   Data in wrong format
291   Foreign application quit
292   DDE conversation closed or changed
293   DDE method invoked with no channel open
294   Invalid DDE Link format
295   Message queue filled; DDE message lost
296   PasteLink already performed on this control
297   Cant set LinkMode; invalid Link Topic
298   System DLL 'dll'' could not be loaded (Error 298)
 
320   Can't use character device names in file names: 'item'
321   Invalid file format
325   Invalid format in resource file
326   Resource with identifier item not found
335   Could not access system registry
336   Object server not correctly registered
337   Object server not found
338   Object server did not correctly run
340   Control array element item doesn't exist
341   Invalid control array index
342   Not enough room to allocate control array 'item'
343   Object not an array
344   Must specify index for object array
345   Reached limit: cannot create any more controls for this form
360   Object already loaded
361   Can't load or unload this object
362   Can't unload controls created at design time
363   Custom control 'item' not found
364   Object was unloaded
365   Unable to unload within this context
366   No MDI form available to load
380   Invalid property value
381   Invalid property array index
382   'Item' property cannot be set at run time
383   'Item' property is read-only
384   A form cant be moved or sized while minimized or maximized
385   Must specify index when using property array
386   'Item' property not available at run time
387   'Item' property can't be set on this control
388   Can't set Visible property from a parent menu
389   Invalid key
390   No Defined Value
391   Name not available
393   'Item' property cannot be read at run time
394   'Item' property is write-only
395   Cant use separator bar as menu name
396   'Item' property cannot be set within a page
397   Cant set Visible property for top level menus while they are merged
 
400   Form already displayed; can't show modally
401   Can't show non-modal form when modal form is displayed
402   Must close or hide topmost modal form first
403   MDI forms cannot be shown modally
404   MDI child forms cannot be shown modally
419   Permission to use object denied
423   Property or method not found
424   Object required
426   Only one MDI Form allowed
427   Invalid object type; Menu control required
428   Popup menu must have at least one submenu
429   OLE Automation server cannot create object
430   Class doesnt support OLE Automation
432   File name or class name not found during OLE Automation operation
438   Object doesn't support this property or method
440   OLE Automation error
443   OLE Automation object does not have a default value
445   Object doesn't support this action
446   Object doesn't support named arguments
447   Object doesn't support current locale setting
448   Named argument not found
449   Argument not optional
450   Wrong number of arguments
451   Object not a collection
452   Invalid ordinal
453   Specified DLL function not found
456   Get and Put cannot be used with arrays in Variants
457   This key is already associated with an element of this collection
460   Invalid Clipboard format
461   Specified format doesnt match format of data
480   Can't create AutoRedraw image
481   Invalid picture
482   Printer error
483   Printer driver does not support specified property
484   Problem getting printer information from the system. Make sure the printer is set up correctly
485   Invalid picture type
 
520   Cant empty Clipboard
521   Cant open Clipboard
 
2055  Expression not valid: .
 
2390  Can't change the data type or field size of this field.  It is part of one or more relationships.
 
2420  Syntax error in number.
2421  Syntax error in date.
2422  Syntax error in string.
2423  Invalid use of '.', '!', or '()'.
2424  Unknown name.
2425  Unknown function name.
2426  Function isn't available in expressions.
2427  Object has no value.
2428  Invalid arguments used with domain function
2429  In operator without ()
2430  Between operator without And
2431  Syntax error (missing operator).
2432  Syntax error (comma).
2433  Syntax error.
2434  Syntax error (missing operator).
2435  Extra ).
2436  Missing ), ], or Item.
2437  Invalid use of vertical bars.
2438  Syntax error.
2439  Wrong number of arguments used with function.
2442  Invalid use of parentheses.
2443  Invalid use of Is operator.
2445  Expression too complex.
2446  Out of memory during calculation.
2447  Invalid use of '.', '!', or '()'.
2448  Can't set value.
2449  Invalid method in expression.
2450  Invalid reference to form 'Item'.
2451  Invalid reference to report 'Item'.
2452  Invalid reference to Parent property.
2453  Invalid reference to control 'Item'.
2454  Invalid reference to '!Item'.
2455  Invalid reference to property 'Item'.
2456  Invalid form number reference.
2457  Invalid report number reference.
2458  Invalid control number reference.
2459  Can't refer to Parent property in Design view.
2460  Can't refer to Dynaset property in Design view.
2461  Invalid section reference.
2462  Invalid section number reference.
2463  Invalid group level reference.
2464  Invalid group level number reference.
2465  Invalid reference to field 'Item'.
2466  Invalid reference to Dynaset property.
2467  Object referred to in expression no longer exists.
2468  Invalid argument used with DatePart, DateAdd or DateDiff function.
2469  Item1 in validation rule: 'Item2'.
2470  Item in validation rule.
2471  Item in query.
2472  Item in linked master field.
2473  Item1 in 'Item2' expression.
2474  No control is active.
2475  No form is active.
2476  No report is active.
2477  Invalid subclass 'Item' referred to in TypeOf function.
 
3000  Reserved error (Item); there is no message for this error.
3001  Invalid argument.
3002  Couldn't start session.
3003  Couldn't start transaction; too many transactions already nested.
3004  Couldn't find database 'Item'.
3005  'Item' isn't a valid database name.
3006  Database 'Item' is exclusively locked.
3007  Couldn't open database 'Item'.
3008  Table 'Item' is exclusively locked.
3009  Couldn't lock table 'Item'; currently in use.
3010  Table 'Item' already exists.
3011  Couldn't find object 'Item'.
3012  Object 'Item' already exists.
3013  Couldn't rename installable ISAM file.
3014  Can't open any more tables.
3015  Item' isn't an index in this table.
3016  Field won't fit in record.
3017  Field length is too long.
3018  Couldn't find field 'Item'.
3019  Operation invalid without a current index.
3020  Update without AddNew or Edit.
3021  No current record.
3022  Can't have duplicate key; index changes were unsuccessful.
3023  AddNew or Edit already used.
3024  Couldn't find file 'Item'.
3025  Can't open any more files.
3026  Not enough space on disk.
3027  Couldn't update; database is read-only.
3028  Couldn't initialize data access because file 'SYSTEM.MDW' couldn't be opened.
3029  Not a valid account name or password.
3030  'Item' isn't a valid account name.
3031  Not a valid password.
3032  Can't perform this operation.
3033  No permission for 'Item'.
3034  Commit or Rollback without BeginTrans.
3036  Database has reached maximum size.
3037  Can't open any more tables or queries.
3039  Couldn't create index; too many indexes already defined.
3040  Disk I/O error during read.
3041  Incompatible database version.
3042  Out of MS-DOS file handles.
3043  Disk or network error.
3044  'Item' isn't a valid path.
3045  Couldn't use 'Item'; file already in use.
3046  Couldn't save; currently locked by another user.
3047  Record is too large.
3048  Can't open any more databases.
3049  Can't open database . File is corrupt or not a Microsoft Jet database.
3050  Couldn't lock file; SHARE.EXE hasn't been loaded.
3051  Couldn't open file 'Item'.
3052  MS-DOS file sharing lock count exceeded.  You need to increase the number of locks installed with SHARE.EXE.
3053  Too many client tasks.
3054  Too many Memo or Long Binary fields.
3055  Not a valid file name.
3056  Couldn't repair this database.
3057  Operation not supported on attached tables.
3058  Can't have Null value in index.
3059  Operation canceled by user.
3060  Wrong data type for parameter 'Item'.
3061  Item1 parameters were expected, but only Item2 were supplied.
3062  Duplicate output alias 'Item'.
3063  Duplicate output destination 'Item'.
3064  Can't open action query 'Item'.
3065  Can't execute a non-action query.
3066  Query must have at least one output field.
3067  Query input must contain at least one table or query.
3068  Not a valid alias name.
3069  Can't have action query 'Item' as an input.
3070  Can't bind name 'Item'.
3071  Can't evaluate expression.
3073  Operation must use an updatable query.
3074  Can't repeat table name 'Item' in FROM clause.
3075  Item1 in query expression 'Item2'.
3076  Item in criteria expression.
3077  Item in expression.
3078  Couldn't find input table or query 'Item'.
3079  Ambiguous field reference 'Item'.
3080  Joined table 'Item' not listed in FROM clause.
3081  Can't join more than one table with the same name (Item).
3082  JOIN operation 'Item' refers to a non-joined table.
3083  Can't use internal report query.
3084  Can't insert into action query.
3085  Undefined function 'Item' in expression.
3086  Couldn't delete from specified tables.
3087  Too many expressions in GROUP BY clause.
3088  Too many expressions in ORDER BY clause.
3089  Too many expressions in DISTINCT output.
3090  Resultant table may not have more than one Counter field.
3091  HAVING clause (Item) without grouping or aggregation.
3092  Can't use HAVING clause in TRANSFORM statement.
3093  ORDER BY clause (Item) conflicts with DISTINCT.
3094  ORDER BY clause (Item) conflicts with GROUP BY clause.
3095  Can't have aggregate function in expression (Item).
3096  Can't have aggregate function in WHERE clause (Item).
3097  Can't have aggregate function in ORDER BY clause (Item).
3098  Can't have aggregate function in GROUP BY clause (Item).
3099  Can't have aggregate function in JOIN operation (Item).
 
3100  Can't set field 'Item' in join key to Null.
3101  Join is broken by value(s) in fields 'Item'.
3102  Circular reference caused by 'Item'.
3103  Circular reference caused by alias 'Item' in query definition's SELECT list.
3104  Can't specify Fixed Column Heading 'Item' in a crosstab query more than once.
3105  Missing destination field name in SELECT INTO statement (Item).
3106  Missing destination field name in UPDATE statement (Item).
3107  Couldn't insert; no insert permission for table or query 'Item'.
3108  Record(s) can't be edited; no Update Data permission on .
3109  Couldn't delete; no delete permission for table or query 'Item'.
3110  Couldn't read definitions; no read definitions permission for table or query 'Item'.
3111  Couldn't create; no create permission for table or query 'Item'.
3112  Couldn't read; no read permission for table or query 'Item'.
3113  Can't update 'Item'; field not updatable.
3114  Can't include Memo or Long Binary when you select unique values (Item).
3115  Can't have Memo or Long Binary in aggregate argument (Item).
3116  Can't have Memo or Long Binary in criteria (Item) for aggregate function.
3117  Can't sort on Memo or Long Binary (Item).
3118  Can't join on Memo or Long Binary (Item).
3119  Can't group on Memo or Long Binary (Item).
3120  Can't group on fields selected with '*' (Item).
3121  Can't group on fields selected with '*'.
3122  'Item' not part of aggregate function or grouping.
3123  Can't use '*' in crosstab query.
3124  Can't input from internal report query (Item).
3125  'Item' isn't a valid name.
3126  Invalid bracketing of name 'Item'.
3127  INSERT INTO statement contains unknown field name 'Item'.
3128  Must specify tables to delete from.
3129  Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.
3130  Syntax error in DELETE statement.
3131  Syntax error in FROM clause.
3132  Syntax error in GROUP BY clause.
3133  Syntax error in HAVING clause.
3134  Syntax error in INSERT statement.
3135  Syntax error in JOIN operation.
3136  Syntax error in LEVEL clause.
3137  Missing semicolon (;) at end of SQL statement.
3138  Syntax error in ORDER BY clause.
3139  Syntax error in PARAMETER clause.
3140  Syntax error in PROCEDURE clause.
3141  Syntax error in SELECT statement.
3142  Characters found after end of SQL statement.
3143  Syntax error in TRANSFORM statement.
3144  Syntax error in UPDATE statement.
3145  Syntax error in WHERE clause.
3146  ODBCcall failed.
3147  ODBCdata buffer overflow.
3148  ODBCconnection failed.
3149  ODBCincorrect DLL.
3150  ODBCmissing DLL.
3151  ODBCconnection to 'Item' failed.
3152  ODBCincorrect driver version 'Item1'; expected version 'Item2'.
3153  ODBCincorrect server version 'Item1'; expected version 'Item2'.
3154  ODBCcouldn't find DLL 'Item'.
3155  ODBCinsert failed.
3156  ODBCdelete failed.
3157  ODBCupdate failed.
3158  Couldn't save record; currently locked by another user.
3159  Not a valid bookmark.
3160  Table isn't open.
3161  Couldn't decrypt file.
3162  Null is invalid.
3163  Couldn't insert or paste; data too long for field.
3164  Couldn't update field.
3165  Couldn't open .INF file.
3166  Missing memo file.
3167  Record is deleted.
3168  Invalid .INF file.
3169  Illegal type in expression.
3170  Couldn't find installable ISAM.
3171  Couldn't find net path or user name.
3172  Couldn't open PARADOX.NET.
3173  Couldn't open table 'MSysAccounts' in SYSTEM.MDW.
3174  Couldn't open table 'MSysGroups' in SYSTEM.MDW.
3175  Date is out of range or is in an invalid format.
3176  Couldn't open file 'Item'.
3177  Not a valid table name.
3178  Out of memory.
3179  Encountered unexpected end of file.
3180  Couldn't write to file 'Item'.
3181  Invalid range.
3182  Invalid file format.
3183  Not enough space on temporary disk.
3184  Couldn't execute query; couldn't find linked table.
3185  SELECT INTO remote database tried to produce too many fields.
3186  Couldn't save; currently locked by user 'Item2' on machine 'Item1'.
3187  Couldn't read; currently locked by user 'Item2' on machine 'Item1'.
3188  Couldn't update; currently locked by another session on this machine.
3189  Table 'Item1' is exclusively locked by user 'Item3' on machine 'Item2'.
3190  Too many fields defined.
3191  Can't define field more than once.
3192  Couldn't find output table 'Item'.
3193  No UserName.
3194  No machine name.
3196  Couldn't use 'Item'; database already in use.
3197  Data has changed; operation stopped.
3198  Couldn't start session.  Too many sessions already active.
3199  Couldn't find reference.
 
3200  Can't delete or change record. Related records exist in table 'Item' that would violate referential integrity.
3201  Can't add or change record.  Referential integrity rules require a related record in table 'Item'.
3202  Couldn't save; currently locked by user 'Item2' on machine 'Item1'.
3203  Can't specify subquery in expression (Item).
3204  Database already exists.
3205  Too many crosstab column headers (Item).
3206  Can't create a relationship between a field and itself.
3207  Operation not supported on Paradox table with no primary key.
3208  Invalid Deleted entry in [dBASE ISAM] section in INI file.
3209  Invalid Stats entry in [dBASE ISAM] section in INI file.
3210  Connect string too long.
3211  Couldn't lock table 'Item'; currently in use.
3212  Couldn't lock table 'Item1'; currently in use by user 'Item3' on machine 'Item2'.
3213  Invalid Date entry in [dBASE ISAM] section in INI file.
3214  Invalid Mark entry in [dBASE ISAM] section in INI file.
3215  Too many Btrieve tasks.
3216  Parameter 'Item' specified where a table name is required.
3217  Parameter 'Item' specified where a database name is required.
3218  Couldn't update; currently locked.
3219  Invalid Operation.
3220  Wrong Paradox sort sequence.
3221  Invalid entries in [Btrieve ISAM] section in WIN.INI.
3222  Query can't contain a Database parameter.
3223  'Item' isn't a valid parameter name.
3224  Btrievedata dictionary is corrupted.
3225  Encountered record locking deadlock while performing Btrieve operation.
3226  Errors encountered while using the Btrieve DLL.
3227  Invalid Century entry in [dBASE ISAM] section in INI file.
3228  Invalid CollatingSequence entry in [Paradox ISAM] section in INI file.
3229  Btrievecan't change field.
3230  Out-of-date Paradox lock file.
3231  ODBCfield would be too long; data truncated.
3232  ODBCcouldn't create table.
3233  ODBCincorrect driver version.
3234  ODBCremote query timeout expired.
3235  ODBCdata type not supported on server.
3236  ODBCencountered unexpected Null value.
3237  ODBCunexpected type.
3238  ODBCdata out of range.
3239  Too many active users.
3240  Btrievemissing WBTRCALL.DLL.
3241  Btrieveout of resources.
3242  Invalid reference in SELECT statement.
3243  None of the import field names match fields in the appended table.
3244  Can't import password-protected spreadsheet.
3245  Couldn't parse field names from first row of import table.
3246  Operation not supported in transactions.
3247  ODBClinked table definition has changed.
3248  Invalid NetworkAccess entry in INI file.
3249  Invalid PageTimeout entry in INI file.
3250  Couldn't build key.
3251  Feature not available.
3252  Illegal reentrancy during query execution.
3254  ODBCCan't lock all records.
3255  ODBCCan't change connect string parameter.
3256  Index file not found.
3257  Syntax error in WITH OWNERACCESS OPTION declaration.
3258  Query contains ambiguous (outer) joins.
3259  Invalid field data type.
3260  Couldn't update; currently locked by user 'Item2' on machine 'Item1'.
3261  Item
3262  Table in use.
3263  Invalid database object.
3264  No fields defined - cannot append table
3265  Name not found in this collection.
3266  Append illegal - field is part of a TableDefs collection.
3267 Property can be set only when the field is part of a Recordset object's Fields collection.
3268 Can't set this property once the object is part of a collection.
3269 Can't append. Index is part of an Indexes collection.
3270 Property not found.
3271 Invalid property value.
3272 Object isn't a collection.
3273 Method not applicable for this object.
3274 External table isn't in the expected format.
3275 Unexpected error from external database driver 'error number'.
3276 Invalid database ID.
3277 Can't have more than 10 fields in an index.
3278 Database engine has not been initialized.
3279 Database engine has already been initialized.
3280 Can't delete a field that is part of an index or is needed by the system.
3281 Can't delete this index.  It is either the current index or is used in a relationship.
3282 Can't create field or index in a table that is already defined.
3283 Primary key already exists.
3284 Index already exists.
3285 Invalid index definition.
3286 Format of memo file doesn't match specified external database format.
3287 Can't create index on the given field.
3288 Paradox index is not primary.
3289 Syntax error in CONSTRAINT clause.
3290 Syntax error in CREATE TABLE statement.
3291 Syntax error in CREATE INDEX statement.
3292 Syntax error in field definition.
3293 Syntax error in ALTER TABLE statement.
3294 Syntax error in DROP INDEX statement.
3295 Syntax error in DROP TABLE or DROP INDEX.
3296 Join expression not supported.
3297 Couldn't import table or query.  No records found, or all records contain errors.
3298 There are several tables with that name.  Please specify owner in the format 'owner.table'.
3299 ODBC Specification Conformance Error 'message'.  This error should be reported to the ODBC driver vendor.
 
3300 Can't create a relationship.
3301 Can't perform this operation; features in this version are not available in databases with older formats.
3302 Can't change a rule while the rules for this table are in use.
3303 Can't delete this field.  It's part of one or more relationships.
3304 You must enter a personal identifier (PID) consisting of at least four and no more than 20 characters and digits.
3305 Invalid connection string in pass-through query.
3306 At most one field can be returned from a subquery that doesn't use the EXISTS keyword.
3307 The number of columns in the two selected tables or queries of a union query don't match.
3308 Invalid TOP argument in select query.
3309 Property setting can't be larger than 2 KB.
3310 This property isn't supported for external data sources or for databases created in a previous version.
3311 Property specified already exists.
3312 Validation rules and default values can't be placed on system or attached (linked) tables.
3313 Can't place this validation expression on this field.
3314 Field 'name' can't contain a null value.
3315 Field 'name' can't be a zero-length string.
3316 'Table-level validation text'.
3317 One or more values entered is prohibited by the validation rule 'rule' set for 'name'.
3318 Top not allowed in delete queries.
3319 Syntax error in union query.
3320 'Error' in table-level validation expression.
3321 No database specified in connection string or IN clause.
3322 Crosstab query contains one or more invalid fixed column headings.
3323 The query cannot be used as a row source.
3324 The query is a DDL query and cannot be used as a row source.
3325 Pass-through query with ReturnsRecords property set to True did not return any records.
3326 This Recordset is not updatable.
3334 Can be present only in version 1.0 format.
3336 Btrieve: Invalid IndexDDF option in initialization setting.
3337 Invalid DataCodePage option in initialization setting.
3338 Btrieve: Xtrieve options aren't correct in initialization setting.
3339 Btrieve: Invalid IndexDeleteRenumber option in initialization setting.
3340 Query 'name' is corrupt.
3341 Current field must match join key 'name' on 'one' side of outer join.
3342 Invalid Memo or OLE object in subquery 'name'.
3344 Unknown or invalid reference 'name' in validation expression or default value in table 'name'.
3345 Unknown or invalid field reference 'name'.
3346 Number of query values and destination fields aren't the same.
3349 Numeric field overflow.
3350 Object is invalid for operation.
3351 ORDER BY expression 'expression' uses non-output fields.
3352 No destination field name in INSERT INTO statement 'statement'.
3353 Btrieve: Can't find file FIELD.DDF.
3354 At most one record can be returned by this subquery.
3355 Syntax error in default value.
3356 The database is opened by user 'name' on machine 'name'.
3357 This query is not a properly formed data-definition query.
3358 Can't open Microsoft Jet engine system database.
3359 Pass-through query must contain at least one character.
3360 Query is too complex.
3361 Unions not allowed in a subquery.
3362 Single-row update/delete affected more than one row of an attached (linked) table.  Unique index contains duplicate values.
3364 Can't use Memo or OLE object field 'name' in SELECT clause of a union query.
3365 Can't set this property for remote objects.
3366 Can't append a relation with no fields defined.
3367 Can't append.  Object already in collection.
3368 Relationship must be on the same number of fields with the same data types.
3370 Can't modify the design of table 'name'.  It's in a read-only database.
3371 Can't find table or constraint.
3372 No such index 'name' on table 'name'.
3373 Can't create relationship.  Referenced table 'name' doesn't have a primary key.
3374 The specified fields are not uniquely indexed in table 'name'.
3375 Table 'name' already has an index named 'name'.
3376 Table 'name' doesn't exist.
3377 No such relationship 'name' on table 'name'.
3378 There is already a relationship named 'name' in the current database.
3379 Can't create relationships to enforce referential integrity.  Existing data in table 'name' violates referential integrity rules in table 'name'.
3380 Field 'name' already exists in table 'name'.
3381 There is no field named 'name' in table 'name'.
3382 Size of a field 'name' is too long.
3383 Can't delete field 'name'.  It's part of one or more relationships.
3384 Can't delete a built-in property.
3385 User-defined properties don't support a Null value.
3386 Property 'name' must be set before using this method.
3388 Unknown function 'name' in validation expression or default value on 'name'.
3389 Query support unavailable.
3390 Account name already exists.
3393 Can't perform join, group, or sort.  Combined fields are too long.
3394 Can't save property; property is a schema property.
3396 Can't perform cascading operation.  Since related records exist in table 'name', referential integrity rules would be violated.
3397 Can't perform cascading operation.  There must be a related record in table 'name'.
3398 Can't perform cascading operation.  It would result in a null key in table 'name'.
3399 Can't perform cascading operation.  It would result in a duplicate key in table 'name'.
 
3400 Can't perform cascading operation.  It would result in two updates to field 'name' in table 'name'.
3401 Can't perform cascading operation.  It would cause field 'name' to become null, which is not allowed.
3402 Can't perform cascading operation.  It would cause field 'name' to become a zero-length string, which is not allowed.
3403 Can't perform cascading operation: 'validation text'.
3404 Can't perform cascading operation.  The value entered is prohibited by the validation rule 'rule' set for 'name'.
3405 Error 'error text' in validation rule.
3406 Error 'error text' in default value.
3407 The server's MSysConf table exists, but is in an incorrect format.  Contact your system administrator.
3409 Invalid field name 'name' in definition of index or relationship.
3410 Can't open this database for update.
3411 Invalid entry.  Can't perform cascading operation in table 'name' because value entered is too big for field 'name'.
3413 Can't perform cascading update on table 'name' because it is currently in use by user 'name' on machine 'name'.
3414 Can't perform cascading update on table 'name' because it is currently in use.
3415 Zero-length string is valid only in a text or Memo field.
3416 'reserved error alert'.
3417 An action query cannot be used as a row source.
3417 Can't have action query as an input.
3418 Can't open 'tablename'.  Another user has the table open using a different network control file or locking style.
3419 Can't open this Paradox 4.x or 5.x table because ParadoxNetStyle is set to 3.x in the initialization setting.
3420 Object is invalid or not set.
3421 Data type conversion error.
3422 Can't modify table structure.  Another user has the table open.
3423 You cannot use ODBC to import from, export to, or link an external Microsoft Access or ISAM database table to your database.
3424 Can't create database because Locale is invalid.
3428 The Microsoft Jet database engine has found a problem in your database.  To correct the problem, you must repair and compact the database.
3429 Incompatible installable ISAM version.
3430 While loading the Excel installable ISAM, OLE was unable to initialize.
3431 This is not an Excel 5 file.
3432 Error opening an Excel 5 file.
3433 Invalid parameter in [Excel ISAM] section of the initialization setting.
3434 Can't expand named range.
3435 Cannot delete Excel cells.
3436 Failure creating file.
3437 Excel spreadsheet is full.
3438 The data being exported does not match the format described in the SCHEMA.INI file.
3439 You attempted to attach or import a Microsoft Word mail merge file.  Although you can export such files, you cannot attach or import them.
3440 An attempt was made to import or attach to an empty text file.  To import or attach a text file, the file must contain data.
3441 Text file specification field seperator matches decimal separator or text delimiter.
3442 In the text file specification 'name', the 'name' option is invalid.
3443 In the fixed-width specification 'name', the 'name' does not specify a width.
3444 The fixed-width specification 'name' contains no column widths.
3445 Incorrect version of Jet DLL file was found.
3446 Jet VBA file not found.
3447 The Jet VBA file failed to initialize when called.
3448 OLE system function call failed.
3449 No country code found in connect string for an attached (or linked) table.
3452 You cannot make changes to the design of the database at this replica.
3453 You cannot establish or maintain a relationship to a replicable table during this operation.
3455 Cannot make the 'name' object replicable.
3456 Cannot make the 'name' object in 'name' container replicable.
3457 You cannot set the KeepLocal property for an object that is already replicable.
3458 The KeepLocal property cannot be set on a database; it can be set only on the objects in a database.
3459 Once a database has been made replicable, it cannot be made unreplicable.
3460 The operation you attempted conflicts with an existing operation involving the replica.
3461 The replication property you are attempting to set or delete is read-only and cannot be changed.
3463 Cannot find the .dll 'name'.
3464 Data type mismatch in criteria expression.
3465 The disk drive you are attempting to access is unreadable.
3472 Disk full for path 'name'.
3485 The object cannot be made replicable because the database is not replicable.
3486 You cannot add a second ReplicationID Autonumber field to a table.
3487 The database you are attempting to make replicable cannot be converted.
3488 The value specified is not a ReplicaID for any replica in the replica set.
3489 The object specified is not replicable because it is missing a necessary resource.
3490 Cannot make a new replica because the 'name' object in 'name' container could not be made replicable.
3491 The database must be opened in exclusive mode before it can be made replicable.
3492 The synchronization failed because a design change could not be applied to one of the replicas.
3499 Can't retrieve the full path information for a replica.
 
3510 The replica you are attempting to exchange with is currently being used in another exchange.
3515 The Microsoft Jet database engine could not read the system clock on your computer.
3520 The replica name or ID does not match a currently managed replica.
3521 The two replicas cannot be synchronized because there is no common point to start the synchronization.
3526 The name selected for the system object is already in use.
3531 The wait period for an event has timed-out.
3533 The system object used by a process still exists after the process has stopped.
3545 The two replicas you are attempting to synchronize are from different replica sets.
3546 The type of exchange you are attempting is not valid.
3548 GUIDs do not match or the requested GUID could not be found.
3549 The filename you provided is too long.
3550 There is no index on the GUID column.
3552 The size of the registry parameter exceeds the maximum allowed.
3553 The GUID could not be created.
3555 All valid nicknames for replicas are already in use.
3559 Failure to write because destination disk is full.
3560 The two replicas you are attempting to synchronize have the same ReplicaID.
3561 The two replicas you are attempting to synchronize both have design-master status.
3566 Network IO error.
3569 The replica has been logically removed from the replica set and is no longer available.
3572 A disk IO error occurred while reading or writing to the TEMP directory.
3574 The ReplicaID for this replica was reassigned during a move or copy procedure
3575 The disk drive you are attempting to write to is full.
3576 The database you are attempting to open is already in use by another application.
3577 Can't update replication system column.
3584 Insufficient memory to complete operation.
3592 You cannot make a password-protected database replicable or set password protection on a replicable database.
3593 Can't change a replicable database from allowing multiple data masters to allowing only a single data master.
3594 Can't change a replicable database from allowing only a single data master to allowing multiple data masters.
3595 The system tables in your replica are no longer reliable and the replica should not be used.
 
3605 Synchronizing with a non-replicated database is not allowed. The 'name' database is not replicable.
3607 The replication property you are attempting to delete is read-only and cannot be removed.
3608 Record length too long for an indexed Paradox table.
3609 No unique index found for referenced field of primary table.
3610 Same table referenced as both source and destination in make-table query.
3611 Can't execute data definition statements on attached data sources.
3612 Multi-level GROUP BY clause not allowed in a subquery.
3613 Can't create a relationship on attached (or linked) SQL tables.
3614 GUID not allowed in Find method criteria expression.
3615 Type mismatch in JOIN expression.
3616 Updating data in an attached (or linked) table is not supported by this ISAM.
3617 Deleting data in an attached (or linked) table is not supported by this ISAM.
3620 The connection for viewing your attached Excel worksheet was lost.
3621 No country code found in connect string for an attached (or linked) table.
3622 No country code found in connect string for an attached (or linked) table.
3623 Can't open replication system table 'name' because table is already in use.


Reference URL: http://www.halfile.com/vb.html

MS Access Vs FileMaker

http://www.filemaker.com/downloads/pdf/fm_access_comparison.pdf

Adding Column Field on Table imported from Xls

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"

DoCmd.RunSQL SQL

Case 2

SQL = "ALTER TABLE [Wcartons issued not shipped] ADD COLUMN Remarks Text(100)"

DoCmd.RunSQL SQL

Case 3

SQL = "ALTER TABLE [Wcartons issued not shipped] ADD COLUMN Count Long"

DoCmd.RunSQL SQL

Case 4

SQL = "ALTER TABLE [Wcartons issued not shipped] ADD COLUMN IBIN Text(50)"

DoCmd.RunSQL SQL

End Select

Next i

Else

'Exit Sub

End If

End If

Exit_CMD_ImportXlsTable_Click:

Exit Sub

Err_CMD_ImportXlsTable_Click:

MsgBox Err.Description

Resume Exit_CMD_ImportXlsTable_Click

End Sub





Exporting Table into a Backend Database

1. Create first the following function:

Function PutTableOnBackEnd(DBName As String, TblName As String, DestinationTable As String) As Boolean

'DBName should include full path and name of back end database
Dim Db As Database
'test back end

On Error Resume Next

Set Db = OpenDatabase(DBName)

If Err <> 0 Then

'failed to open back end database

Exit Function

End If

If Not Db Is Nothing Then Db.Close

'test if table is local

If IsNull(DLookup("Type", "MSysObjects", "Name='" & TblName & "' AND Type=1")) Then

'table is not local

Exit Function

End If

'put table on back end
'DoCmd.TransferDatabase acExport, "Microsoft Access", DBName, acTable, TblName, TblName

DoCmd.TransferDatabase acExport, "Microsoft Access", DBName, acTable, TblName, DestinationTable

If Err <> 0 Then GoTo Done

'link to the back end table
'DoCmd.DeleteObject acTable, TblName
'DoCmd.TransferDatabase acLink, "Microsoft Access", DBName, acTable, TblName, TblName

PutTableOnBackEnd = True 'defaults to false if it fails to get here

Done:

End Function

2. Specify the Tables to Export:

Tables to Export

TableID

TableName

Directory

BackEndDB

1

API Control Table

\\zwnwb080\groups\API_UPDATES\API\Upload

BackEnd.mdb

2

APIData Table

\\zwnwb080\groups\API_UPDATES\API\Upload

BackEnd.mdb

3

Unused ControlNo Table

\\zwnwb080\groups\API_UPDATES\API\Upload

BackEnd.mdb





3. Call the Function in a command button of a form:


Private Sub CMD_ExportTable_Click()

Dim Result As Boolean
Dim strPC As String
Dim SourceTable As String
Dim DestTable As String
Dim strDirectory As String
Dim BackEndDB As String

strPC = Me.TxtPCName

For i = 1 To 3

‘Specify the Table Names

SourceTable = DLookup("[TableName]", "Tables to Export", "[TableID] =" & i)

DestTable = strPC & "-" & SourceTable 'Here I attached the PC Name for Record Purposes

strDirectory = DLookup("[Directory]", "Tables to Export", "[TableID] =" & i)

BackEndDB = DLookup("[BackEndDB]", "Tables to Export", "[TableID] =" & i)

‘Call the Function

Result = PutTableOnBackEnd(strDirectory & "\" & BackEndDB, SourceTable, DestTable)

Debug.Print Result

MsgBox ("Finish exporting " & SourceTable)

Next i

End Sub



Saturday, October 08, 2005

Compacting Access Db at specified time

How to Compact Databases at a Scheduled Time url REFERENCE: http://support.microsoft.com/?id=158937

Thursday, October 06, 2005

INCREMENTAL NUMBERING SYSTEM

INCREMENTAL NUMBERING SYSTEM

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

RecCount = Nz(DCount("[ControlNo]", "Temp APIData", "[ControlNo]='" & Me.TxtControlNumber & "'"), 0)

‘2. If Record <> to zero then proceed to CallLoop

If RecCount <> 0 Then

GoTo CallLoop

Else

Me.TxtMultiTicketCounter.Value = 0

GoTo ExitingBasketProcedure

End If

CallLoop:

‘3. Set a Variable for the RecordSet you need to update

Set APITempDB = CurrentDb.OpenRecordset("SELECT * FROM [Temp APIData] WHERE ControlNo = '" & Me.TxtControlNumber & "'")

‘4. Initialize a counter called A

A = 1

‘5. Create a Loop Statement that will cycle through the recordset until the end of File

Do Until APITempDB.EOF

With APITempDB

.Edit

‘6. Each cycle updates ItemNo with counter A

!ItemNo = A

.Update

.MoveNext

End With

‘7. Counter A is incremented by 1

A = A + 1

Loop

‘8. Close the Recordset

APITempDB.Close

Me.TxtMultiTicketCounter.Value = RecCount

’***END Of INS Procedure

CHECKING CHARACTER FOR INTEGER OR STRING

CHECKING CHARACTER FOR INTEGER OR STRING

SCENARIO: A controlNo for inventory is formatted like:

1. For Multi Ticket = M05-00001

2. For Single Ticket = S05-00001

In order for the user not to enter a different format each character should be validated if they qualify as integer or String.

Here is the procedure in the AfterUpdate of the TxtControlNo Unbound Textbox:

The following procedure cycles through the series of characters in a textbox

'***Start of character Check for ControlNo String
’***Programmer: Noel Jerome Q. Noel

‘***Contact: noeljltd@hotmail.com

Dim X As Integer, i As Integer, intClength As Integer

Dim intSuffix As Long, NumCheck As Variant, ControlCheck As Variant

Dim RControlNo As String, StringConvert As String, StringCheck As String

intClength = Len(Me.TxtControlNumber)

X = 0

For i = 1 To intClength

RControlNo = Right(Me.TxtControlNumber.Value, intClength - X)

ControlCheck = Left(RControlNo, 1)

NumCheck = Nz(Val(ControlCheck), 0)

StringConvert = Str(NumCheck)

StringCheck = Right(StringConvert, Len(StringConvert) - 1)

If ControlCheck <> StringCheck Then

VarCheckType = "StringVar"

Else

VarCheckType = "IntegerVar"

End If

Select Case i

Case 1 'First Character should be string

If VarCheckType <> "StringVar" Then

prompt = MsgBox("You have entered a number instead of text on Character 1 of your ControlNo.", vbOKOnly, "Warning!!!")

Me.TxtControlNumber.Value = Null

Exit Sub

End If

Case 4 '4th Character should be a dash "-" string

If ControlCheck <> "-" Then

prompt = MsgBox("Character 4 of your ControlNo should be a dash '-' string. Change your ControlNo.", vbOKOnly, "Warning!!!")

Me.TxtControlNumber.Value = Null

Exit Sub

End If

Case Else 'Other Characters should be integer

If VarCheckType <> "IntegerVar" Then

prompt = MsgBox("You have entered a text instead of number on Character " & i & " of your ControlNo.", vbOKOnly, "Warning!!!")

Me.TxtControlNumber.Value = Null

Exit Sub

End If

End Select

X = X + 1

Next i