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