MS Access Insider

testing

2006-04-24T09:27:00.000-07:00

test

How to create running totals in a query

2006-01-31T12:48:00.000-08:00

How to create a running totals query in Microsoft Access

View products that this article applies to.

Article ID	:	290136
Last Review	:	June 9, 2004
Revision	:	1.0

This article was previously published under Q290136

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access database (.mdb).

For a Microsoft Access 2000 version of this article, see 208714 (http://support.microsoft.com/kb/208714/EN-US/).

For a Microsoft Access 97 version of this article, see 138911 (http://support.microsoft.com/kb/138911/).

SUMMARY

This article demonstrates two methods that you can use to create a running totals query. A running totals query is a query in which the total for each record is a summation of that record and any previous records. This type of query is useful for displaying cumulative totals over a group of records (or over a period of time) in a graph or report.

Note You can see a demonstration of the technique that is used in this article in the sample file Qrysmp00.exe. For information about how to obtain this sample file, please see the following article in the Microsoft Knowledge Base:

207626 (http://support.microsoft.com/kb/207626/EN-US/) ACC2000: Access 2000 Sample Queries Available in Download Center

Back to the top

MORE INFORMATION

Method 1

The first method uses a DSum() function and criteria in a query to create a running sum over time. The DSum() function sums the current record and any previous records. When the query moves to the next record, the DSum() function runs again and updates the cumulative total.

The following sample query uses the Orders table from the sample database Northwind.mdb to create a running sum of the freight costs for each month in 1997. The sample data is limited to one year for performance reasons. Because the DSum() function runs once for every record in the query, it may take several seconds (depending on the speed of your computer) for the query to finish processing. To create and run this query, follow these steps:

1.	Open the sample database Northwind.mdb.
2.	Create a new select query and add the Orders table.
3.	On the View menu, click Totals.
4.	In the first column of the query design grid, type the following expression in the Field box, and make the following selections for the Total, Sort, and Show boxes: Field: AYear: DatePart("yyyy",[OrderDate]) Total: Group By Sort: Ascending Show: Yes The expression in the Field box displays and sorts the year portion of the OrderDate field.
5.	In the second column of the query design grid, type the following expression in the Field box, and make the following selections for the Total, Sort, and Show boxes: Field: AMonth: DatePart("m",[OrderDate]) Total: Group By Sort: Ascending Show: Yes The expression in the Field box sorts and displays the month portion of the Order Date field as an integer value from 1 to 12.
6.	In the third column of the query design grid, type the following expression in the Field box, and make the following selections for the Total and Show boxes. NOTE: In the following example, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this example. Field: RunTot: DSum("Freight","Orders","DatePart('m', _ [OrderDate])<=" & [AMonth] & " And DatePart('yyyy', _ [OrderDate])<=" & [AYear] & "") Total: Expression Show: Yes The expression in the Field box uses the DSum() function to sum the Freight field when the values in both the AMonth and the AYear fields are less than or equal to the current record that the query is processing.
7.	In the fourth column of the query design grid, type the following expression in the Field box, and make the following selections for the Total, Sort, and Show boxes: Field: FDate: Format([OrderDate],"mmm") Total: Group By Sort: Ascending Show: Yes The expression in the Field box displays each month in a textual format, such a Jan, Feb, Mar, and so on.
8.	In the fifth column of the query design grid, type the following expression in the Field box, and make the following selections for the Total, Criteria, and Show boxes: Field: DatePart("yyyy",[OrderDate]) Total: Where Criteria: 1997 Show: No The expression in the Field box filters the query's recordset to include data from 1997 only.
9.	Run the query. Note that the RunTot field displays the following records with a running sum: AYear AMonth RunTot FDate -------------------------------------- 1997 1 2238.98 Jan 1997 2 3840.43 Feb 1997 3 5729.24 Mar 1997 4 8668.34 Apr 1997 5 12129.74 May 1997 6 13982.39 Jun 1997 7 17729.29 Jul 1997 8 22204.73 Aug 1997 9 26565.26 Sep 1997 10 32031.38 Oct 1997 11 36192.09 Nov 1997 12 42748.64 Dec

Back to the top

Method 2

The second method uses a totals query with a DSum() function to create a running total over a group.

The following sample query uses the Orders table to sum freight costs per employee as well as to calculate a running sum of the freight. To create and run the query, follow these steps:

1.	Open the sample database Northwind.mdb.
2.	Create a new select query and add the Orders table.
3.	On the View menu, click Totals.
4.	In the first column of the query design grid, add the following field to the Field box, and make the following selections for the Total and Show boxes: Field: EmpAlias: EmployeeID Total: Group By Show: Yes This field groups data by EmployeeID.
5.	In the second column of the query design grid, add the following field to the Field box, and make the following selections for the Total and Show boxes: Field: Freight Total: Sum Show: Yes This field sums the freight data.
6.	In the third column of the query design grid, type the following expression in the Field box, and make the following selections for the Total and Show boxes. NOTE: In the following example, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this example. Field: RunTot: Format(DSum("Freight","Orders","[EmployeeID]<=" _ & [EmpAlias] & ""),"$0,000.00") Total: Expression Show: Yes The expression in the Field box uses a DSum() function to sum the Freight field when the EmployeeID is less than or equal to the current EmpAlias, and then formats the field in dollars.
7.	Run the query. Note that the RunTot field displays the following records with a running sum: Employee SumOfFreight RunTot ------------------------------------------------- Davolio, Nancy $8,836.64 $8,836.64 Fuller, Andrew $8,696.41 $17,533.05 Leverling,Janet $10,884.74 $28,417.79 Peacock, Margaret $11,346.14 $39,763.93 Buchanan, Steven $3,918.71 $43,682.64 Suyama, Michael $3,780.47 $47,463.11 King, Robert $6,665.44 $54,128.55 Callahan, Laura $7,487.88 $61,616.43 Dodsworth, Anne $3,326.26 $64,942.69

Back to the top

REFERENCES

For more information about totals queries, click Microsoft Access Help on the Help menu, type calculations in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

Back to the top

APPLIES TO

•	Microsoft Office Access 2003
•	Microsoft Access 2002 Standard Edition

Back to the top

kbhowto KB290136

Back to the top

Source: http://support.microsoft.com/?kbid=290136

Converting a Microsoft Access Database to an MDE File

2005-11-29T08:47:00.000-08:00

The situation may arise when you quickly need to secure and distribute a Microsoft Access database that also contains Microsoft Access VBA code. You will protect the database from editing by restricting the database users access to the design views, however you will need to allow the users to process their data as normal.

It may be that you haven't got the time to fully create Microsoft Access User Accounts or to set Microsoft Access Security Permissions. By knowing what can and cannot be done by using the Microsoft Access MDE file format, will allow you to make a decision and to quickly release a secure database to the users.

Microsoft Access MDE File Format

The MDE file format is a special extension of the standard Microsoft Access MDB format, and is used to distribute an application. An MDE file compiles all of the modules, removes all editable source code and compacts the database. The resulting MDE file allows the database users to perform normal database operations, however it prohibits any changes from being made to the objects that support, or the code that runs the application.

Figure1: Creating an MDE File

Benefits of an MDE File

There are various benefits to be had by creating and distributing the database as an MDE file:

Reducing the size of the database - the size of the database will be reduced due to the removal of the code, so memory usage is optimized, which will improve performance.
Allowing VBA code to run, but protection from the code being viewed or edited.
Security of objects without requiring additional Microsoft Access user-level security to be set.
Allowing database users to update data and run reporting options without having to provide the full database.

Forms and Reports in an MDE File

By converting your Microsoft Access database into an MDE file you will prevent your database users from tampering with your VBA code. When a user accesses an MDE file, they cannot:

View, modify or create Microsoft Access forms in design view.
View, modify or create Microsoft Access reports in design view.
View, modify or create Microsoft Access modules in design view.
Add, delete or change references to databases or to object libraries.
Change any VBA code - there is no source code available.
Import or export any forms, reports or code modules.

MDE File Requirements

There are some requirements to saving your Microsoft Access MDB file as a Microsoft Access MDE file, and the following conditions apply:

You must have password access to the Visual Basic code in the database.
You must save all references to other Microsoft Access databases.
You must have removed replication from a replicated database.
If you want to remove user-level security options, you must do so prior to converting the file to the MDE format.
If your database does have user-level security and you do need to preserve this, you must do:
- Be a member of the workgroup that defines the user accounts used to access the database, or that was in use when the database was created.
- Have Open/Run and Open Exclusive permissions for the database.
- Have Modify Design or Administer permissions for any tables in the database, or be the owner of any of the tables in the database.
- Have Read Design permissions for all objects in the database.

How to Convert a Microsoft Access Database to an MDE File

Once you have determined that you meet the above criteria for converting an Access database in MDB format to the MDE format, it is a simple process to make the conversion happen. You will need to perform the following actions:

If necessary, convert the database into the latest Microsoft Access version - if the file format is Access 2000 and you are working in Microsoft Access 2003 you will need to convert the database file format to Access 2003:
1. From the Tools menu, choose Database Utilities >> Convert Database >> To Access 2003 File Format.
2. Navigate to the database that needs to be converted and click OK.
3. Enter a name for the database and click Save.
4. Click OK to dismiss the message box.
5. Open the database to confirm the name change.
Choose Tools >> Database Utilities >> Make MDE File.
Select the database to be converted and click Make MDE.
Confirm that the file has been converted to MDE and click Save.
Open the new file, and confirm that the Code toolbar is disabled and that the application works as expected.

Reference URL: http://www.databasedev.co.uk/microsoft-access-mde.html

The InStrRev Function

2005-11-17T06:21:00.000-08:00

The InStrRev function returns the position of the first occurrence of one string within another. The search begins from the end of string, but the position returned counts from the beginning of the string.

The InStrRev function can return the following values:

If string1 is "" - InStrRev returns 0
If string1 is Null - InStrRev returns Null
If string2 is "" - InStrRev returns start
If string2 is Null - InStrRev returns Null
If string2 is not found - InStrRev returns 0
If string2 is found within string1 - InStrRev returns the position at which match is found
If start > Len(string1) - InStrRev returns 0

Tip: Also look at the InStr function

Syntax

InStrRev(string1,string2[,start[,compare]])

Parameter	Description
string1	Required. The string to be searched
string2	Required. The string expression to search for
start	Optional. Specifies the starting position for each search. The search begins at the last character position by default (-1)
compare	Optional. Specifies the string comparison to use. Default is 0 Can have one of the following values: 0 = vbBinaryCompare - Perform a binary comparison 1 = vbTextCompare - Perform a textual comparison

Example 1

dim txt,pos
txt="This is a beautiful day!"
pos=InStrRev(txt,"his")
document.write(pos)

Output:

Example 2

dim txt,pos
txt="This is a beautiful day!"
'textual comparison
pos=InStrRev(txt,"B",-1,1)
document.write(pos)

Output:

Example 3

dim txt,pos
txt="This is a beautiful day!"
'binary comparison
pos=InStrRev(txt,"T")
document.write(pos)

Output:

Example 4

dim txt,pos
txt="This is a beautiful day!"
'binary comparison
pos=InStrRev(txt,"t")
document.write(pos)

Output:

Reference: http://www.w3schools.com/vbscript/func_instrrev.asp

Cstr Function

2005-11-17T06:11:00.000-08:00

Returns an expression that has been converted to a Variant of subtype String.
Reference: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/vsfctcstr.asp

The Sub-Subform You've Always Wanted

2005-11-17T06:02:00.000-08:00

Reference url: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsmart03/html/sa03c1.asp

Peter Heskett

Access won't let you have a subform within a subform if the data has to be displayed in a continuous form. But necessity is the mother of invention, and Peter Heskett found a way by using senior and junior subforms. He also shares a few other tricks for handling forms.

After more than 10 years of developing business systems for clients, I thought I'd done it all. But with one new client, I found myself having to work out a new way of form handling to give them what they wanted.

My client's distribution business involves four levels of detail. First is the Consignment (Level 1), which consists of a container full of boxes, which the company has to distribute to various destinations in Spain. Each Consignment is broken down into Trips (Level 2), one Trip for each destination in the Consignment. On each Trip the company can deliver to a number of different clients who get a Receipt (Level 3). Each Receipt will include a number of Boxes (Level 4), which are priced according to box type.

Are you still with me? The traditional solution to this would be to have a Consignment form with a Trips subform, then a drill down to the next level of detail (the Receipt) with, perhaps, a pop-up for Boxes. But that wasn't going to suit this client, who wanted to manage the Trip with its Receipts and related Boxes all on one screen.

What I needed was a Trip main form with two subforms: one for the customer Receipts and the other for the Boxes to be delivered to the individual customers. The only problem (in Access 97 and 2000) is that you can't have a subform within a subform if the main subform is set to continuous forms. So I had to think of a way to do a senior and a junior subform. In addition, I wanted to be able to pass data from my main form to the subform and from the sub-subform back to the subform. Figure 1 shows the form that I ended up with.

When you click on the Box button on the form, the Box subform is revealed showing the boxes for that client Receipt (see Figure 2).

I encountered a number of problems in getting to this solution. In overcoming all of them, I used Access tools to the fullest and kept the programming as simple as possible. My solution may not be scalable if you're displaying lots of records, but the solution was quick to implement. It will also appeal to those of you who like using code behind your forms and have other things to do apart from programming.

The main problems that I had to overcome included:

Getting the client pick list to show just the clients in a particular location.
Displaying the full client name as a ToolTip-like pop-up (the client information displayed on the form isn't enough to identify the client to the user).
Determining how to get the Box subform to relate to the individual client that was selected in the Receipt subform.
Getting the Box subform to calculate costs based on standard prices attached to the individual customer records.
Getting the totals from the Box subform into the client Receipt subform.

Limiting combo boxes

It's easy enough to load a combo box at design time. It's more of a problem to change the pick list for each record at runtime depending on the values on the form. To do this you need to set the Row Source of the combo box in two places:

In the AfterUpdate event of the key field (in this case, Destination).
When you open a form with the Destination already defined, in the Form's OnCurrent event.

The code that I used behind the Trip's Current event was:

Private Sub Form_Current()
If Not IsNull(Me.DestinationID) Then
   Me.TripReceiptSub.Form.ClientRef.RowSource = _
                                   "qryClientPick"
End If
End Sub

A similar piece of code is required in the AfterUpdate event. When setting the row source in the AfterUpdate, you have to save the record before you can set the new value for the list box's RowSource:

Private Sub DestinationID_AfterUpdate()
   DoCmd.DoMenuItem acFormBar, acRecordsMenu, _
                 acSaveRecord, , acMenuVer70
   Me.TripReceiptSub.Form.ClientRef.RowSource = _
                 "qryClientPick"
End Sub

In this case, I've used the same query every time—qryClientPick (which is shown in Figure 3). The query uses the value on the form as part of its criteria ([forms]![trip]![destinationID]) to regenerate the list box based on the value in the destinationID text box.

Creating your own ToolTip

In a continuous form, unbound controls like ToolTips don't really work. Yet I wanted to display the client name on the continuous form as a kind of ToolTip when the user moves over the ClientRef text box. This required me to carry the client name as a data field on the table for Receipts. This is how I set up my ToolTip-like display:

Insert a bound text box, named Company, into the subform with a light background color (say, 14286847). Set the text box's Visible property to False.
Add a label called LabelCompany, with text to tell the user to click here to make the tip invisible.
On the mouse move event of the ClientRef, add code to display the company name text box:

Private Sub ClientRef_MouseMove
If Not IsNull(Me.ClientRef) Then
   If Me.Company.Visible = False Then
       Me.Company.Visible = True
       Me.LabelCompany.Visible = True
   End If
End If
End Sub

On the Click event of Company, add some code to switch the ToolTip off. You need to put the focus somewhere else before you can set the Visible property to False. This is the code that I used:

Private Sub Company_Click()
 Me.Notes.SetFocus
 Me.Company.Visible = False
 Me.LabelCompany.Visible = False
End Sub

Linking the sub-subform

Given the restrictions of Access, I set up a senior and a junior subform, both linked in a master-child relationship to the Level 1 Trip form.

TripReceiptSub is the senior subform and is linked to the main Trip form by TripID. TripBoxSub is the junior subform and is linked to the Trip form by the ReceiptID. The Trip form doesn't have a bound ReceiptID. Rather, when you click the Box button on the main subform, it places a value into an unbound ReceiptID field on the Main Trip form—which will drive the TripBoxSub (see Figure 4). Effectively, TripBoxSub becomes a subform of the TripReceiptSub form.

The code to do this is behind the Box button:

Private Sub btnbox_Click()
' save the record first
DoCmd.DoMenuItem acFormBar, acRecordsMenu, _
                  acSaveRecord, , acMenuVer70
Me.Parent.ReceiptID = Me.ReceiptID
Me.Parent.ClientRef = Me.ClientRef
Me.Parent.TripBoxSub.Requery
Me.Parent.TripBoxSub.Visible = True
Me.Parent.BoxHide.Visible = False
Me.Parent.TripBoxSub.SetFocus
Me.Parent.TripBoxSub.Form.Company = Me.Company

The junior subform needs to be requeried so that it will link to the new ReceiptID and display just the records for that ReceiptID. The Link Child Fields and the Link Master Fields properties of the junior subform must be set to ReceiptID.

Using data from the main record

Now that I had all my forms synchronized, I needed to use data from the record underlying the main form in my sub-subform. I got around this problem by using the ClientRef combo box on the senior subform. In the qryClientPick you may have noticed a number of extra columns for prices. These are the standard prices the individual clients usually pay.

To make these prices available, I have some unbound price text boxes on the main form. I use the Column property of the list box to set the values for these text boxes. When the user clicks the red Box button, this code transfers the prices from the selected entry in the list box to the unbound fields on the form:

On Error Resume Next
Me.Parent.PriceBig = Me.ClientRef.Column(3)
Me.Parent.PriceNorm = Me.ClientRef.Column(4)
Me.Parent.PriceSmall = Me.ClientRef.Column(5)

The On Error Resume Next is the neatest way of stopping unwanted error messages, should the system find a null value in a column and be unable to set it. Access considers the first column in a combo list as column(0), which is why PriceBig, which is in the fourth column, is referred to as Column(3), and so on.

For this to work, the properties of the Client Ref combo box should be set as shown in Figure 5. In particular, it's important that the number of columns match the number of columns in the query.

Once I had the standard prices on the main form, I picked them up in the AfterUpdate event of the BoxType text box. There are only three different types of box, so I just hard-coded the routine to retrieve the right price:

Private Sub BoxType_AfterUpdate()
On Error Resume Next
If Me.BoxType = "Big" Then
       Me.Price = Me.Parent.PriceBig
   ElseIf Me.BoxType = "Normal" Then
       Me.Price = Me.Parent.PriceNorm
   ElseIf Me.BoxType = "Small" Then
       Me.Price = Me.Parent.PriceSmall
End If
SubValue
End Sub

My SubValue routine that calculates the totals is very simple. I called a subroutine because I calculated Prices numerous times in the application:

Private Sub SubValue()
If Me.Quantity > 0 And Me.Price > 0 Then
   Me.Value = Me.Quantity * Me.Price
End If
End Sub

Updating the senior subform

But I had another problem: I needed to update my senior subform with the totals generated by the records in the junior subform. In other words, I wanted to pass data from the sub-subform back to the subform. The obvious way to do this was to take the totals in the footer of the subform and update the relevant Receipts record. But this only works if the user saves the record before updating, which doesn't always happen. So to make this happen, I needed some proper code.

First, I needed to loop through the records in the table that underlies the junior subform (Boxtbl). I only wanted the records that related to the selected ReceiptID and calculated the total, which I held in variables for later use:

Private Sub btnAct_Click()
DoCmd.DoMenuItem acFormBar, acRecordsMenu, _
                    acSaveRecord, , acMenuVer70
Dim iChk As String
Dim db As DATABASE
Dim rs As Recordset
Dim rc As Recordset
Dim iTotb as Long   ' boxes
Dim iTotv As Double   ' value

Set db = CurrentDb()
Set rc = db.OpenRecordset("Select * from Boxtbl " & _
 "Where receiptID = '" & Me.Parent.ReceiptID & " ' ")

If rc.BOF And rc.EOF Then
       ' No Records
   Else      
     iTotb = 0
     iTotv = 0
   While Not rc.EOF
      iTotb = iTotb + Nz(rc("quantity"))  ' boxes
      iTotv = iTotv + Nz(rc("value"))
      rc.MoveNext
   Wend
End If
rc.Close

With new totals calculated and held in the variables iTotb and iTotv, I update the record underlying the senior subform with this code:

Set rs = db.OpenRecordset( _
   "Select * from Receiptbl " & _
   "Where ReceiptID = '" & Me.ReceiptID & " ' ")
   rs.Edit
       rs("BoxTotal") = iTotb
       rs("ValueTotal") = iTotv
   rs.Update
   rs.Close

The final step is to requery the senior subform to pick up the new totals from the table:

Me.Parent.TripReceiptSub.Requery

DAO references

In the sample database, I've used DAO code, which will work for all versions of Access. But if you're using Access 2000, you'll need to set the module References in Access 2000 to include DAO. To do this, open a module and select Tools and then References, and scroll down and click on Microsoft DAO 3.51 or 3.6. You'll then need to close the References dialog and open it again to check where the DAO reference is positioned. The DAO needs to be third in the list if it's going to take precedence and work properly.

With this code, my client was able to do most of what they had to do on just the one form. Since doing this, I've incorporated this sub-subform method in another client system, and have plans for more. I hope you find it as useful as I do.

Download SUBSBFRM.exe

To find out more about Smart Access and Pinnacle Publishing, visit their website at http://www.pinpub.com/html/main.isx?sub=57

Note: This is not a Microsoft Corporation website. Microsoft is not responsible for its content.

This article is reproduced from the March 2003 issue of Smart Access. Copyright 2003, by Pinnacle Publishing, Inc., unless otherwise noted. All rights are reserved. Smart Access is an independently produced publication of Pinnacle Publishing, Inc. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of Pinnacle Publishing, Inc. To contact Pinnacle Publishing, Inc., please call 1-800-788-1900.

Creating autonumbered Field in DDL

2005-11-16T14:55:00.000-08:00

Here's how you can create an autonumbered field in Data Defination Language or DDL:
Sample:

CREATE TABLE tblTarget2
   (TargetID COUNTER CONSTRAINT PrimaryKey PRIMARY KEY,
   [PECID] Long,
   [BS] Long,
   [D] Long,
   [D1] Long,
   [MPS] Long,
   [D2] Long,
   PEC TEXT(8),
   Email TEXT(50))

This sample will create a table called tblTarget2 with the
TargetID field set as primary key and automated counter.

tblTargettblTarget2    TargetID  PECID  BS  D  D1  MPS  D2  PEC  Email   
 
   1  1  
 
 
 
 
 
 
  
   2  2  
 
 
 
 
 
 
  
   3  3  
 
 
 
 
 
 
  
 
  

Reference: http://support.microsoft.com/?id=116145

Using MySQL from Microsoft Access

2005-11-14T17:41:00.000-08:00

You can use Microsoft Access to exchange data with a database on MySQL. This provides very convenient mechanisms for both populating your database initially, and for keeping it up to date. If you have existing data in an Access database, an Excel spreadsheet, or a delimited text file, this can be read into Microsoft Access and exported to your database on the MySQL server. Before you can use Access with MySQL, you must first have set up a Data Source for your MySQL database. Details of how to do this can be found at Using MySQL with ODBC.

The example described here uses Microsoft Access 97, which was provided on Staff WTS version 1. Working with later versions of Access is essentially the same, but may appear slightly differently on the screen, and some menu item names have changed in later versions of Access.

Exporting a database table

This example assumes you have already created a simple database with a single table such as the following. The table has a unique primary key student_id.

More info: http://www.ucl.ac.uk/is/mysql/access/

Importing Objects into MS Access

2005-10-29T08:57:00.000-07:00

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

Inside Microsoft Access Date/Time Fields

2005-10-20T12:10:00.000-07:00

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:

How to Display Line Numbers on Subform Records

2005-10-13T12:26:00.000-07:00

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

Importing Data from and External Database

2005-10-12T15:13:00.000-07:00

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:

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

End Sub

VBA Error Codes and messages

2005-10-12T13:41:00.000-07:00

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

2005-10-12T13:03:00.000-07:00

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

Adding Column Field on Table imported from Xls

2005-10-12T10:31:00.000-07:00

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

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

2005-10-12T10:07:00.000-07:00

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

Compacting Access Db at specified time

2005-10-08T11:05:00.000-07:00

How to Compact Databases at a Scheduled Time

View products that this article applies to.

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.

url REFERENCE: http://support.microsoft.com/?id=158937

INCREMENTAL NUMBERING SYSTEM

2005-10-06T16:51:00.000-07:00

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

2005-10-06T16:39:00.000-07:00

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

Increment Values in query

2005-10-04T13:54:00.000-07:00

Reference:

global IncrementVariable as Long

function IncrementValues(i) as Long
IncrementVariable = IncrementVariable + 1
IncrementValues = IncrementVariable
end function

Note that it's necessary to pass a value to the function in order to have it count for each record. If you don't include this piece then you'll likely get a lot of 1's in every row instead of the counting result you'd like to see.

SELECT ... IncrementValues([any_field]) ...

You can futher customize this function, the following code with start over at 1 every 4 seconds, so if you run a query that numbers your table, it will start over at 1 on it's own next time you run the query (assuming it's > 4 seconds later).

global IncrementVariable as Long
global lastcall as Date

Function IncrementValues(i) As Long
If Now > (lastcall + 4 / 60 / 60 / 24) Then
lastcall = Now
IncrementVariable = 0
End If
IncrementVariable = IncrementVariable + 1
IncrementValues = IncrementVariable
End Function

And this one restarts every 4 seconds, and gives you the option of designating a starting point other than 1.

Function IncrementValues(i, Optional myBase As Long) As Integer
If Now > (lastcall + 2 / 60 / 60 / 24) Then
lastcall = Now
If myBase Then
IncrementVariable = myBase - 1
Else
IncrementVariable = 0
End If
End If
IncrementVariable = IncrementVariable + 1
IncrementValues = IncrementVariable
End Function

Finally, it's a good idea to choose data type Long instead of Integer, in case you have a lot of data... and if you're running on a P75, you might want to omit the timed restart, or do more than a 4 second delay, or it might restart at 1 in the middle of running a long slow query.

Limitations of MS Access

2005-09-21T14:36:00.000-07:00

REFERENCE: http://www.codeguru.com/forum/archive/index.php/t-235929.html
There is an article Microsoft Access 2000: Choosing between MSDE and Jet (http://www.microsoft.com/sql/techinfo/deployment/70/msdejet.asp) which lists all the limitations of Access database in the Word file (http://www.microsoft.com/sql/techinfo/deployment/70/Access2000MSDE.doc).
Here is extract:
"Appendix B: Access/Jet (.mdb) Specifications

File size (.mdb filetype) 2 GB
No. of objects in a database 32,768
Modules 1,000
Number of characters in an object name 64
Number of characters in a password 14
Number of characters in a user name or group name 20
Number of characters in a table name 64
Number of characters in a field name 64
Number of fields in a table 255
Number of open tables 2048.
Table size 1 gigabyte
Number of characters in a Text field 255
No. of characters in a Memo field (data entered via UI) 65,535
No.of char. in a Memo field (data entered programmatically) 1 gigabyte
Size of an OLE Object field 1 gigabyte
Number of indexes in a table 32
Number of fields in an index 10
Number of characters in a validation message 255
Number of characters in a validation rule 2,048
Number of characters in a table or field description 255
Number of characters in a record 2,000
Number of characters in a field property setting 255"

MS Access Programming JOB Links

2005-09-19T22:07:00.000-07:00

1. http://www.craigslist.org/nby/cpg/94461117.html
2. http://www.tech-centric.net/search/anonymous/jobdetails.aspx?job=153839
3. http://www.tech-centric.net/search/anonymous/jobdetails.aspx?job=149688
4. Dice.Com
5. http://www.tech-centric.net/search/anonymous/jobdetails.aspx?job=137667
6. http://www.google.ca/search?hl=en&q=MS+Access+Programmer+%2B+jobs&meta=
7. http://www.net-temps.com/job/suda/965/ms_access_programmer.html
8. http://www.tech-centric.net/search/anonymous/jobdetails.aspx?job=147624
9. http://denver.craigslist.org/art/97270542.html
10. MS Access programmer on Baltimore
11. Fun JObs Review
12. Cambria Palo Alto MS Access Programming job
http://www.cambria.com/services_access.asp

RITA'S REFERENCE:
13. http://www.job4you.com/vb.htm
14. Careerbuilder.com
15. Microagility
16.ALR Asociates Inc.
17. KForce.Com
18. HR System Support Analyst - Adecco Technical
19. Indeed.Com Search Result

RESUME RABBIT: http://wvw.jobseekerweekly.com/cgi-bin/submitwriter.pl?ID=144

New Links:
1. http://www.programmingfromhome.com/
2. http://www.prgjobs.com/Job.cfm/217090
3.

Customizing Autonumbers

2005-09-03T16:31:00.000-07:00

http://forums.aspfree.com/archive/t-24837/Possible-to-customize-autonumber-in-R
http://www.pcreview.co.uk/forums/showthread.php?p=6617335#post6617335
Fixing autonumbers that act weird: http://support.microsoft.com/?kbid=287756

Number of users Access can Accomodate

2005-09-01T07:54:00.000-07:00

http://www.dbforums.com/archive/index.php/t-1079077.html
I agree Access is a very easy to start with database, but here are the
limitations to what Access can do:

1. The theoretical maximum number of concurrent users in an Access database
is 255. However, this figure is just the maximum
number possible, and you are more likely to find that the maximum number of
users is about 50-100, although depending on your
design, this might be less. This should certainly be borne in mind when
starting to design a database, as it is almost guaranteed that
although the initial brief called for only 15 users, after a year of
operation there will probably be twice that using the database.
Therefore over-engineering is required.

2. Also, Access isn't configured to run as a client/server database, so it
isn't optimized for running over a network. A true client/server
application will only call the data from the server to the client that are
needed. Therefore, you will probably see some network
performance degradation.

3. Access is also limited to how much data it can store. In Access 97, this
is 1 Gigabyte per database, and in Access
2000 it is 2Gb, although you can have multiple databases used to store
information in.

I believe this info is relevant for you to decide. I strongly recommmend
that if you haven't yet started with your app design,
switch to SQL as backend, and frontend with something as VB, that gives you
more flexibility and a far better UI.

Making Table using plain VBA

2005-08-27T21:36:00.000-07:00

One of the fundamental tasks that Access users frequently perform is creating tables. By using the techniques in this column, you can add a menu item to Access that lets you create a simple table with just two mouse clicks.
http://office.microsoft.com/en-us/assistance/HA010428181033.aspx

Creating a Clock on your Form

2005-07-07T10:09:00.000-07:00

Microsoft Access Database Solutions: Create a working clock on your forms

Looking for that extra special effect to give your Microsoft Access database project a professional flair...Thought about adding a working clock to your Access forms?

To put a simple text clock on a form, create a Label called lblClock on the form, set the form's TimerInterval to 1000, and the following code behind the Timer Event.

You can also create two command buttons called cmdClockStart and cmdClockEnd and attach respective code to each to have the clock run on demand.

'***************** Code Start ***************
Private Sub Form_Timer()
Me!lblClock.Caption = Format(Now, "dddd, mmm d yyyy, hh:mm:ss AMPM")
End Sub

Private Sub cmdClockStart_Click()
Me.TimerInterval = 1000
End Sub

Private Sub cmdClockEnd_Click()
Me.TimerInterval = 0
End Sub
'***************** Code End ***************

How to eliminate white space in reports with CanShrink and code in Access 2002

2005-07-04T14:16:00.000-07:00

View products that this article applies to.

Article ID	:	299011
Last Review	:	June 23, 2005
Revision	:	5.0

This article was previously published under Q299011

For a Microsoft Access 97 version of this article, see 95390.

Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

For a Microsoft Access 2000 version of this article, see 210589.

SUMMARY

Before you print a form or a report, you can save space by conditionally reducing the size of the controls on the form or report. For example, you might want to reduce the size of text boxes that contain varied-length text, or reduce the size of text boxes bound to fields that could be empty.

Before you try to reduce the size of controls in reports, however, consider the following:

•	Overlapping controls will not shrink, even when the CanShrink property is set to Yes.
•	Controls shrink line by line (vertically). This means, for example, that if there is a group of controls placed on the left side of the page and a large control (for example, an OLE picture) on the right side of the page, the controls on the left side of the page will not shrink, unless the picture is blank and hidden.
•	Space between controls is not affected by the CanShrink or CanGrow property.
•	Controls located in the page header or page footer can shrink and grow. However, the sections do not have CanGrow and CanShrink properties. This means, you can still have blank space in a page header or page footer section (even though its controls may shrink).

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers 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 requirements.

Back to the top

MORE INFORMATION

A control containing null information automatically shrinks to nothing and disappears when its CanShrink property is set to Yes, but the space between the controls is not affected by the CanShrink property.

For example, say you have 11 controls in a column of a report, and there is .1 inch of space between each control. Even if all 11 controls contain null information and all 11 have their CanShrink property set to Yes, the space between the controls adds up to 1 inch. When you print the report, even though the 11 controls are not printed, the first control that does contain text will be printed 1 inch lower than you might expect because of all the accumulated space.

A good way to manage the size of your controls is to have as few controls as possible. This will minimize the white space between the controls.

The following example demonstrates how to print mailing labels by using a Visual Basic function to minimize the number of controls and to give you even spacing. Say you want to create mailing labels containing a name, business name, address, city, state, and zip code. You could use several different fields, but it would be better to use a single text box, as in the following step-by-step example.

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.

More info: http://support.microsoft.com/?kbid=299011

Goto Record Command in a Form

2005-06-22T12:35:00.000-07:00

This command comes very handy when you want to process the record one by one or test single records against another table.

Private Sub Form_Open(Cancel As Integer)
Dim intNPWCount As Integer

Dim strNPWGID As String
Dim FName As String

FName = Me.Form.Name
intNPWCount = Nz(DCount("[MPGID]", "MPW_Weekly_TimeTracker", "[DeptID]='" & Forms![ManpowerOTPopulationF].[ComboDeptID] & "' And [WKNO]=" & Forms![ManpowerOTPopulationF].[TxtWKNO]), 0)

If intNPWCount <> 0 Then

For A = 1 To intNPWCount

DoCmd.GoToRecord acDataForm, FName, acGoTo, A

‘Other Commands

Next A

Else

Exit Sub

End If

End Sub

How do I log users off a database and keep them off?

2005-06-14T10:54:00.000-07:00

The following provides a methodology to allow the developer to schedule and enforce periods of downtime for any number of databases.

Define a database, ie, TimeCtl. All users should have read access to the database, but must not be able to change/move/delete the database or anything within the database.
Define a table, tblLogOut on the TimeCtl database having the following fields per row.
--ApplicationName text(50) primary key
--LogoffStart date/time
--LogoffEnd datetime
--Inactive Boolean

you will have one row for every active database. The application name is the database name only without the mdb. It is not the full path name. For example, if you database is DrugCourt.mdb the applicationname on tblLogOut is DrugCourt.
LogoffStart is the date and time you want to insure that all users cannot access applicationname. LogoffEnd is the date and time the users can begin to use applicationname again.
If the inactive flag is true, the record will never be checked again. This allows you to keep a historical track of your scheduled down time.

In TimeCtl, create a module called gfunctions (global functions). Cut and paste the following function into gfunctions.

Public Function funShutDown(DatabaseName As String) As Boolean: funShutDown = False

Dim db As Database
Dim rs As Recordset
Dim strSQL As String

DatabaseName = UCase(DatabaseName)
Dim myDate As Date: myDate = Now()
strSQL = "SELECT * FROM TBLLOGOUT WHERE APPLICATIONNAME = '" _
& DatabaseName & "' AND INACTIVE = 0 AND #" & myDate & "# BETWEEN LOGOFFSTART AND LOGOFFEND"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
If rs.RecordCount = 0 Then
GoTo OutShutDown
Else
funShutDown = True
End If

OutShutDown:
rs.Close
Set rs = Nothing
Set db = Nothing
Exit Function

End Function

WATCH OUT FOR WORD WRAP.

On each database that is now represented on tblogout, establish a reference to the timecontrol database. This will allow you to call funShutDown from that database.
Link tbllogout to your database. Again, we don’t care if the user can see this table as long as he can’t do anything else but see information.

In your application database create an unbound form with one textbox. Place the following code on the open form event.

Private Sub Form_Open(Cancel As Integer)

If funShutdown(UCase("drug court")) = True Then
Application.Quit
End If

End Sub

This insures that a user who attempts to log on during the scheduled down time will fail to do so.

Set the forms timer interval to 300000 (5 minutes). The following is the forms timer event.

Private Sub Form_Timer()

If funShutdown("drug court") = True Then
Application.Quit
End If

End Sub

In the startup routine of your database, open this form hidden.

Every 5 minutes, your database will test if it should shut down. If down time is scheduled it will shut down within 5 minutes of the downtime start time.

Reference: http://www.tek-tips.com/faqs.cfm?fid=2145

Hide a table in Database Window

2005-06-02T09:46:00.000-07:00

(Q) Can I hide a table so that it doesn't appear in the Database window?

(A) If you want to hide a table in the database windows so that other users can't select it, preface the table's name with 'Usys'. For example, if your table's name is employee, change it to UsysEmployee. This makes your table into a system object which access does not display. To see this table later, choose view, options, show system objects.

More Reference: The Access Web

2005-05-12T09:28:00.000-07:00

Upper Case Function in access: The upper case function of MS Access is done by issuing the prefix command UCase before the field you want to change into upper case.

Removing Filter of a Form through VBA

2005-05-06T08:27:00.000-07:00

Procedure for Preset Filters:

Private Sub CMDRemoveFilter_Click()
Me.Filter = ""
End Sub

Explanation:
Double quote removes the filter.

Filtering a Form through VBA

2005-05-02T14:01:00.000-07:00

Procedure for Preset Filters:

Private Sub CMDNullSalaries_Click()

Dim strNull As String

strNull = "(([Salary Query Form].[HR Salary] Is Null))"

Me.Form.Filter = strNull

Me.FilterOn = True

End Sub

Explanation:

Form “Salary Query Form” will display only the Record of whose “HR Salary” field contains nothing.

Procedure for filters passed from a Combo List:

Private Sub CMDFilter_Click()

On Error GoTo Err_CMDFilter_Click

Dim vFilter As String

vFilter = Forms![Salary Query Form]![DeptFilterCombo]

Me.Filter = "DeptID = '" & vFilter & "'"

Me.FilterOn = True

Exit_CMDFilter_Click:

Exit Sub

Err_CMDFilter_Click:

MsgBox Err.Description

Resume Exit_CMDFilter_Click

End Sub

Explanation:

1. vFilter variable is declared.

2. vFilter variable takes the value from Forms![Salary Query Form]![DeptFilterCombo]

3. DeptID Field will then be filtered with the value from vFilter variable.

NOTE: If the variable contains string, then vFilter should be enclosed by single quote.

"DeptID = '" & vFilter & "'"

4. Declare Me.FilterOn as True. This is to activate filter.

2005-04-29T10:41:00.000-07:00

Passing Value from Access to Excel Range. The following blog will share to you how the File location directory will be passed to a Range in an Excel Spreadsheet.

Passing Value from Access to Excel Range

2005-04-29T07:45:00.000-07:00

Here is a simple way of passing a value from MS Access to an Excel File that is being opened by MS Access.

Situation: I have an MS Access Database form(A) which exports a query(B) to a certain directory as an excel file. This query is going to be used by an Excel Spreadsheet(C) which is preformatted for calculation and reporting. Now, as I open the preformatted Excel Spreadsheet(C) from Access(A) I would like to pass the directory location where I saved the query as xls (B) to SpreadSheet(C). This directory will be then used by Excel to know where to automatically pick-up the xls query(B) so that it can create pivot tables and other reports from (B).

(A) MS Access Form: This form has fields with directory location assigned as Me.DirLoc
(B) Query exported as XLS
(C) Preformatted XLS with loaded Macro to auto-fetch (B)

Private Sub CmdRunRegenerator_Click()
Dim DistDir
Dim ReportCreator As String

ReportCreator = Me.Regenerator
DestDir = Me.DIRLoc
Dim objXL As Object, x
On Error Resume Next
Set objXL = CreateObject("Excel.Application")
With objXL.Application
.Visible = True
'Open the Workbook
.Workbooks.Open DestDir & "\" & ReportCreator
'Include CARMA in menu, run AutoOpen
.ActiveWorkbook.RunAutoMacros xlAutoOpen
.ActiveWorkbook.Sheets("Sheet1").Range("D9").Value = Me.DIRLoc
x = .Run("RegenerateEmpFile")
.ActiveWorkbook.Save
.ActiveWorkbook.Close
objXL.Quit
'AccountsViewEngine
'Auto_Open
End With
Set objXL = Nothing
UpdateSource
ClearInputOutputFiles
MsgBox ("Update completed and imported xls file cleared.")
End Sub

NOTE: The line highlighted in red is the command to pass the value of the Directory location in the MS Access form to the spreadsheet being opened.

Access Data Definition Language - Creating and Altering Tables On The Fly

2005-04-15T08:14:00.000-07:00

One of the first things an Access developer learns is how to write SQL Queries. A simple request for data might look something like this:
SELECT FullName, Address, Phone
FROM tblEmployee ORDER BY FullName
This type of query uses Data Manipulation Language (DML). While this example does not perform any great manipulation of the data, some very clever calculations are possible. The SQL language is powerful indeed.
However, once you have gotten a handle on DML, you will want to explore the world of DDL, Data Definition Language. This too is part of the SQL language and can be run from an Access query, or through VBA code, but unlike DML, these commands do not return a result set. DDL is used to create and alter database objects, such as tables. First, let's see how this is done and then consider some scenarios where this technique will save you time and in some cases, a lot of work.

The process to create a DDL query is a little different from what you might be used to. Begin in the normal way, by choosing New Query from the Query window, but when prompted, do not add any tables. You are then presented with the QBE (query by example) grid with no tables. Select Data Definition from the Query | SQL Specific menu to continue.

Selecting Data Definition will take you to a very plain, Notepad-like interface where you can paste or type in your DDL SQL script. Save the query and select Run from the Query menu to execute the script. When you run this kind of query, Microsoft Access displays the following warning:

PROBLEM:An employee table needs to have their salary updated based from an imported excel file containing the Global ID of the Employees with matching Salary changes. When importing the Excel spreadsheet it automatically set the data type of the Global ID field into numbers. This creates an error when doing an update because the Access table to be updated contains a Global ID field that is preset to data type text.

SUGGESTION:Change the Global ID Data type of the imported Excel Spreadsheet into Text on the fly, before executing the update command.

SOLUTION:
1. Invoke a query. From Object Window click Queries, Click New, Design View, OK, Close, Click Query Grid. From the Main Menu click Query, SQL Specific, Data Definition.
2. Inside the query window Type: ALTER TABLE [Source Table] ALTER COLUMN GID Text;
3. Save the query. With my example I saved it as AlterSourceTableQ
4. Call the query inside your procedure:

Private Sub CMDImportEmpFile_Click()
Dim SQL As String
DoCmd.SetWarnings False

SQL = "DELETE [Source Table].* " & _
"FROM [Source Table];"

DoCmd.RunSQL SQL

DoCmd.OpenQuery "AlterSourceTableQ"
DoCmd.OpenQuery "UpdateSourceFromImportedFileQ"
DoCmd.SetWarnings True
End Sub

To find out more....Click here

Opening Excel from MS Access thru VBA

2005-04-13T12:44:00.000-07:00

This procedure opens up the excel sheet from a given directory and run a macro from within excel.

Private Sub CmdOpenframedetailedCombiner_Click()
Dim strPath As String
Dim strFileName As String
Dim strSourceDir As String
Dim strSheetName As String
Dim xlApp As Object
Dim xlSheet As Object

strSourceDir = "\\zwnwb080\Groups3\CDMAProd\PRODUCTION SERVICE SHEETS\daily web plan\"
strFileName = "frame and detailed combiner v7 with GSM Update.xls"
strPath = strSourceDir & strFileName
strSheetName = "detailed"

Set xlApp = CreateObject("Excel.Application")
With xlApp
.Visible = True
.Workbooks.Open strPath
.Run "UpdateDetailedFromForXlsdetailedQ"
End With
End Sub

Exporting Query to Excel using VBA

2005-04-11T12:40:00.000-07:00

Before you perform the export operation you have to follow simple steps to produce the right output you want:

1. IDENTIFY Source Query.
The Query is called : ExpenseSummarySelectQ
We will assign a variable called: stDocName for ExpenseSummarySelectQ

2. IDENTIFY Output File and Directory.
The output Excel file will be called: ExpenseSummarySelect 04-11-05.xls
The directory this file will be exported is: \\zwnwb080\Expense
3. SET VARIABLES and their types.
"ExpenseSummarySelectQ" = stDocName type String
"ExpenseSummary" = FileName type String
"04-11-05" = tDate As String
"\\zwnwb080\Expense\" = FileDir As String

To combine the filename and Directory like this:
\\zwnwb080\Expense\ExpenseSummary 04-11-05.xls
You need to combine variables too:
XFile = FileDir & FileName & tDate & " .xls"

6. Compose your Procedure.
Private Sub CMDRunExpSummarySelectQ_Click()
Dim stDocName As String
Dim FileName As String
Dim XFile As String
Dim tDate As String
Dim FileDir As String

stDocName = "ExpenseSummarySelectQ"
FileName = "ExpenseSummary"
tDate = Format(Now(), "mm-dd-yy")
FileDir = "\\zwnwb080\"
xFile = FileDir & FileName & tDate & " .xls"

DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.OutputTo acOutputQuery, "ExpenseSummarySelectQ", _
acFormatXLS, xFile, False
DoCmd.Close acQuery, "ExpenseSummarySelectQ", acSaveNo
MsgBox (xFile & " in " & FileDir & " had been created. Excel Report will now be created.")

End Sub

MS Access Insider

testing

How to create running totals in a query

How to create a running totals query in Microsoft Access

On This Page

SUMMARY

MORE INFORMATION

Method 1

Method 2

REFERENCES

APPLIES TO

Keywords:

Converting a Microsoft Access Database to an MDE File

The InStrRev Function

Syntax

Example 1

Example 2

Example 3

Example 4

Reference: http://www.w3schools.com/vbscript/func_instrrev.asp

Cstr Function

The Sub-Subform You've Always Wanted

Limiting combo boxes

Creating your own ToolTip

Linking the sub-subform

Using data from the main record

Updating the senior subform

DAO references

Creating autonumbered Field in DDL

Using MySQL from Microsoft Access

Exporting a database table

Importing Objects into MS Access

SUMMARY

MORE INFORMATION

Inside Microsoft Access Date/Time Fields

Abstract

Introduction

Dates

Times

Intervals

Conclusion

16th-Century Calendar Revision

How to Display Line Numbers on Subform Records

SUMMARY

MORE INFORMATION

How to Use the GetLineNumber() Function

Importing Data from and External Database

VBA Error Codes and messages

Visual Basic Error Codes and Messages

MS Access Vs FileMaker

Adding Column Field on Table imported from Xls

Exporting Table into a Backend Database

Compacting Access Db at specified time

INCREMENTAL NUMBERING SYSTEM

CHECKING CHARACTER FOR INTEGER OR STRING

Increment Values in query

Limitations of MS Access

MS Access Programming JOB Links

Customizing Autonumbers

Number of users Access can Accomodate

Making Table using plain VBA

Creating a Clock on your Form

How to eliminate white space in reports with CanShrink and code in Access 2002

On this page

SUMMARY

MORE INFORMATION

Goto Record Command in a Form

How do I log users off a database and keep them off?

Hide a table in Database Window

Removing Filter of a Form through VBA

Filtering a Form through VBA

Procedure for Preset Filters:

Explanation:

Private Sub CMDFilter_Click()

End Sub

Explanation:

Passing Value from Access to Excel Range

Access Data Definition Language - Creating and Altering Tables On The Fly

Opening Excel from MS Access thru VBA

Exporting Query to Excel using VBA