[Clip]Export Access to MySQL

View system tables in database window

Question: In Access 2003/XP/2000/97, how can I view system tables in the database window?
Answer: Under the Tools menu, select "Options".

Hide tables as system tables in Access

Question: I've created an Access 2003/XP/2000/97 database and flagged all of the tables as hidden objects. The users have figured out how to display these hidden tables. Is there any way that I can keep my tables from being displayed in the database window?
Answer: If you've tried setting your tables as hidden objects and the users are still able to find them, you can always set your tables up as system tables. Inside of your Access database, there are several system tables that even you can't see. These tables are used internally by the Access database.

Hide tables as hidden objects

Question: In an Access 2003/XP/2000/97 database, I've created a table that I don't want others to see. How can I hide a table from appearing in the database window?
Answer: If you don't want users to see a table in an Access database, you can mark the table as a hidden object.

Open the database exclusively

Question: In Access 2003/XP/2000/97, I tried modifying some of the objects in my Access database and I received an error message stating that I needed to open the database exclusively. What does this mean and how do I open a database exclusively?
Answer: Because Access was designed to accommodate multiple users in the system at the same time, you may need to have exclusive access to the database to modify forms, tables, etc. depending on the circumstances.

Display/Unhide the database window

There are different ways that the database window can be hidden in Access 2003/XP/2000/97. As a result, there are also different methods for displaying a hidden database window. We'll go through the options from the easily method to the most difficult.

Method #1

This is the easiest and most common method of unhiding a database window.
Under the Window menu, select Unhide.

Hide the database window

There are different ways that the database window can be hidden in Access 2003/XP/2000/97. We'll go through the options from the easily method to the most difficult.

Method #1

This is the easiest and most common method of hiding a database window.
Under the Window menu, select Hide.

With this method if a user chooses to display the database window, it will remain visible even if the user closes down the database file and reopens it.

Bind combo box to primary key but display a description field in Access

Question: In Access 2003/XP/2000/97, I've set up a table that contains an autonumber field as the primary key and a description field. I want to be able to create a combo box that displays the description, but stores the primary key. How do I set up the combo box?
Answer: Quite often the primary key value can mean nothing to the user. Instead, there is a description field in the table that relays the meaning of the record. In Access you can link a combo box to the primary key, but display the description in the combo box itself.
To do this, first create a combo box object. Right-click on the combo box and select Properties from the popup menu.

DateDiff Function

In Access, the DateDiff function returns the difference between two date values, based on the interval specified.
The syntax for the DateDiff function is:

DateDiff ( interval, date1, date2, [firstdayofweek], [firstweekofyear])

interval is the interval of time to use to calculate the difference between date1 and date2. Below is a list of valid interval values.

View only records with uppercase letters for a certain field in Access

Question: In Access 2003/XP/2000/97, if I wanted to view only the records with Uppercase letters for a certain field, how would I do it?
For example, the query would ignore "Bob", but show "JOHN".
Answer: Access was built to be case-INSENSITIVE, but we can always use the ASC function to determine upper vs lower case.

Calculate and format an elapsed time value in Access

Question: In Access 2003/XP/2000/97, how do I format a time on a report so it appears as elapsed hours and minutes?
For example, 27 hours and 31 minutes appears as 27:31. I can do this for values up to 24 hours. But for anything over 24 hours, the full day(s) are dropped off.
Answer: A "elapsed time" is usually calculated by taking the difference between two dates. So, you will need to store the "from" and "to" dates separately and then calculate the elapsed time.

Convert currency into words

Question: In Access, how can I convert currency to words?
For example,

$100 should read as "one hundred exactly"

Answer: To convert currency into words, you'll need to open your Access database and create a new module.

Generate a random number between 2 user-specified values

In Access, the Rnd function allows you to generate a random number (integer value). You can specify the random number to be a value between 2 user-specified numbers.
The syntax for the Rnd function is:

Int ((upperbound - lowerbound + 1) * Rnd + lowerbound)

upperbound is the highest value that the random number can be.
lowerbound is the lowest value that the random number can be.

Report Example (Employee hours worked per day)

Question: In Access 2003/XP/2000/97, I have a database where I report employee hours daily. I need a report that displays the hours worked daily and the total of hours worked per week per employee.
Answer: We've created a sample Access database that contains the tables, queries, and reports required to demonstrate this example.

Display parameter values in a report based on a parameter query

Question: In Access 2003/XP/2000/97, how do I display parameter values in a report based on a parameter query?
Answer: To display a parameter value in a report, you will need to redesign your parameter query to return the parameter as a field in the result set.
We've created an Access sample that you can download.

Capitalize each word in a string (similar to InitCap in Oracle)

In Access, the StrConv function returns a string converted as specified.
The syntax for the StrConv function is:

StrConv ( text, conversion, LCID )

text is the string that you wish to convert.

Renumber invoices with VBA code

Question: In Access 2003/XP/2000/97, I have a database which contains a large number of records. Each week, I need to renumber the INVOICE NUMBER field in a table named DATA with a new batch of numbers.
Answer: You can renumber your invoice numbers using a custom function.
Open your Access database, click on the Modules tab and create a new Module. Paste in the following

Change AutoFormat (background style) for a form in Access

Also learn how to change AutoFormat (background style) for a form in Access 2007 | Access 2003/XP/2000/97.

Question: In Access 2003/XP/2000/97, is it possible to change a background style for a form?
Example: I have a user who wants to change the background throughout the application. When it was originally created the autoformat "Expedition" was utilized.
Answer: You can change the background style of an object using an AutoFormat. To do this, open the form in design view. In this example, we've opened the form called Customers.

Prevent the mouse wheel from scrolling through records in a form in Access

Question: In Access 2003/XP/2000/97, how do I prevent the mouse wheel from scrolling through records in a form?
Answer: You'll have to download the following MouseWheel.dll file and copy it to each computer that you wish to prevent scrolling.

Set form colors to be consistent on different computers in Access

Also learn how to set form colors to be consistent on different computers in Access 2007 | Access 2003/XP/2000/97.

Question: I want to deploy an Access 2003/XP/2000/97 application. The forms have non-default colors. When I test the application over the network the colors change to access default or desktop default. How can I force my colors to the user screen?

Set the background color of a button in Access

Also learn how to set the background color of a button in Access 2007 | Access 2003/XP/2000/97.

Question: In Access 2003/XP/2000/97, how do I set the background color of a command button?
Answer: Unfortunately, you can't change the back color of a command button. As an alternative, you could create a label and have it behave as a button.

Query to retrieve max date in Access

Also learn how to create a query to retrieve max date in Access 2007 | Access 2003/XP/2000/97.

Question: In Access 2003/XP/2000/97, how can I build a query to select the latest date among several records?
Answer: To do this, open your query in Design view. Select the field that contains the date values. In this example, we've selected the Order_Date field.

Retrieve the user name from Windows while in a database in Access

Question: In Access 2003/XP/2000/97, how can I retrieve the name of the user logged into Windows?
Answer: To retrieve the user name from Windows, create a new Module in Access.
Paste the following code into the Module:

MS Access: Populate underlying table when new entries are keyed into combo box in Accesscombo box

Also learn how to populate underlying table when new entries are keyed into combo box in Access 2007 | Access 2003/XP/2000/97.

Question: In Access 2003/XP/2000/97, I have a form that is bound to a table. I have created a combo box on this form that obtains its values from a field that resides in a different table from the one that the form is bound to. When I enter values in this combo box I want those values written back to the associated field. How do I setup the combo box so that entries in the combo box are written back to the field in the table it came from?

Access a value from a table using VBA code

Question: In Access 2003/XP/2000/97, how can I access a value directly from a table using VBA code?
Answer: To access a value directly from a table using VBA code, you first need to create a new Module. To do this, open the Database window, select the Modules tab and click on the New button.

Parse a string into two values based on the occurrence of an underscore

Question: In Access 2003/XP/2000/97, I have a table with a field that I need to separate into two different fields. The field is alpha/numeric in its contents:

Example of the field content is:
1234_ACME company

I need this field split into two fields such as:

Field1: 1234
Field2: ACME company

The Underscore (_) can be in any position in the string.
Answer: In Access, you can create custom functions to supplement what is missing in Access. To do this, click on the Modules tab in the Database window and create a new module.

Convert a date to a numeric value

Question: In Access 2003/XP/2000/97, how do you change the date into a numeric value?
For example, I want to change the date 20/02/2004 into the number 20022004. This formats the number as ddmmyyyy.
Answer: In Access, you can create custom functions to supplement what is missing in Access. To do this, click on the Modules tab in the Database window and create a new module. Then paste in the following custom function:

Format a string to display as a currency value in Access

Question: In Access 2003/XP/2000/97, I'm trying to display a currency variable in a message box, but the value is not showing correctly. For example, the message box is displaying £2.5 instead of £2.50.
How can I properly display the currency value in the message box?
Answer: You will need to use the Format function to properly display the currency value.

Export two Access tables into individual sheets in an Excel spreadsheet in Access

Question: In Access 2003/XP/2000/97, I want to set up a form with a button that a user can click. When the button is clicked, I want to export two Access tables into an Excel spreadsheet. I want each table to appear on its own sheet in the Excel file.
Answer: We've created a sample Access database that you can download that demonstrates how to export two Access tables into the same Excel file.

Create new database file and transfer tables using VBA

Also learn how to populate underlying table using a popup form when new entries are keyed into combo box in Access 2007 | Access 2003/XP/2000/97.

Question: In Access 2003/XP/2000/97, I have a combo box that is populated from a table/query. How would one add an item to a combo box via popup form if a user enters a value that is not in the list?

Examples of converting Excel formulas to Access 2003/XP/2000/97

Question: In Access 2003/XP/2000/97, I'm trying to build an expression that will do the same as the following Excel formula with the result formatted as "dd/mm/yyyy".

=VALUE(DATE(VALUE(MID(A1,4,1))+2000,1,1))+VALUE(MID(A1,5,3))-1

How can I do this?
Answer: In an Access query, the equivalent formula would be:

Connect to Oracle using VBA code

Question: I'm developing an application using Access 2003/XP/2000/97 as the front-end and Oracle as the back-end database. I've run into an scenario wherein I'd like to suppress the "ODBC Driver Connect" window prompt that asks for the username and password to connect to the Oracle database.
So I've created a form called "Login", but I need help writing the VBA code that would connect to Oracle. Can you help?
Answer: To connect to Oracle as you described above, you'll have to first create an ODBC connection to your Oracle database using the {Microsoft ODBC for Oracle} driver.

Link to an Oracle table using VBA code

Question: In Access 2003/XP/2000/97, I'd like to link to an Oracle table using VBA code. How can I do this?
Answer: The first thing you need to do is create an ODBC connection to your Oracle database using the {Microsoft ODBC for Oracle} driver.
To do this, go to the "Data Sources ODBC" icon under the Control Panel and create a new Data Source using the {Microsoft ODBC for Oracle} driver.

Call an Oracle stored procedure using VBA code

Question: I'm using Access 2003/XP/2000/97 as the front-end development tool and Oracle RDBMS as the back-end database. There are packages, procedures, and functions in the Oracle database that I'd like to call from Access. How do I execute or call an Oracle stored procedure from Access?

e.g. application_program_interface(member_id, provider_id, service_date)

Answer: To call an Oracle stored procedure, you'll have to create a query using VBA code.

Create a pass-through query to retrieve the nextval from an Oracle sequence with VBA code

Question: In Access 2003/XP/2000/97, I would like to create a pass-through query so that I can retrieve the NextVal from an Oracle sequence. I would like to then assign the NextVal to a field on an Access form.
How can I do this?
Answer: To assign the NextVal (from an Oracle sequence) to a field on a form, you'll have to create a pass-through query using VBA code.

Create a pass-through query to retrieve the nextval from an Oracle sequence

Question: I can get nextval from sequence in Oracle with this command:

select seq_name.nextval from dual;

Is there any way to get nextval from the Oracle sequence through Access 2003/XP/2000/97 when docmd.runsql("select seq_name.nextval from dual") doesn't work?
I need to insert nextval (the same value) into two tables.
Answer: To get the nextval from an Oracle sequence, you will need to use a pass-through query in Access.
To create a pass-through query, create a new query. Select Design View and click on the OK button.

Play a wav file (sound file) without using the API

Question: In Access 2003/XP/2000/97, I have some ALAW WAV files that will not play through the API in Access. Is there a way to play a WAV (sound file) without using the API?
Answer: It is possible to play wav files within your Access database without using the API. We'll demonstrate how to do this on a button's on_click event. First you will need to create a new Module and paste in the following VBA

Play a wav file (sound file) through the API when a button is clicked, based on a text box value

Question: In Access 2003/XP/2000/97, I've set up a Form that displays all of the records from the WavFiles table. The WavFiles table has a column called WavFilename that contains the name of a wav file (without the .wav extension) on our file server.
I want to create a button on the Form that allows me to play the wav file that is displayed in the WavFilename field for the current record in the Form.
How can I do this?
Answer: It is possible to play wav files within your Access database. To do this, create a new Module and paste in the following VBA

Play a wav file (sound file) through the API when a form opens, based on a text box value

Question: In Access 2003/XP/2000/97, I want to play a wav file when a form is loaded. But I only want the wav file to play if a text box contains a certain value. If the text box contains any other value when the form is loaded, I don't want to play the wav file.
Answer: It is possible to play wav files within your Access database. To do this, create a new Module and paste in the following VBA

Play a wav file (sound file) through the API

Question: In Access 2003/XP/2000/97, I have a database and I'd like to play a wav file (sound file) from a form. Is this possible?
Answer: It is possible to play wav files within your Access database.

To do this, create a new Module and paste in the following VBA code:

Re-enable the Shift key when logging into a secure database in Access

Question: In Access 2003/XP/2000/97, I've secured a database using the User-Level Security Wizard and I've also hidden the Database window in the Startup Options for the database. However, when a user logs in while holding down the Shift key, the Startup Options are not bypassed and so the Database window will not appear.
How can I re-enable the Shift key when a user logs in so that the Database window becomes visible?
Answer: Within your Access database, there is a Database Property called "AllowBypassKey". By default, your Access database sets this property to True and allows users to use the Shift key functionality when logging into a secured database.

Disable the Shift key when logging into a secure database in Access

Question: In Access 2003/XP/2000/97, I've secured a database using the User-Level Security Wizard and I've also hidden the Database window in the Startup Options for the database. However, when a user logs in while holding down the Shift key, the Startup Options are bypassed and the Database window appears.

How can I disable the Shift key when a user logs in so that the Database window remains hidden?


Answer: Within your Access database, there is a Database Property called "AllowBypassKey". By default, your Access database sets this property to True and allows users to use the Shift key functionality when logging into a secured database.

Associate an mdw security file with a database in Access

Question: In Access 2003/XP/2000/97, I've secured a database with an mdw file. I want to associate the Access database with the mdw file without "joining" the mdw file in the Workgroup Administrator. How do I do this?
Answer: You can associate an mdw file with an Access database, by setting up a Shortcut in Windows.
In our example, we'll assume that the Access database is called suppliers.mdb and the mdw file is called secure.mdw. Both of these files will reside in a directory called c:\test for the purposes of this example.
Now, create a new Shortcut object in Windows. You can do this by right-clicking and choosing New > Shortcut.

Launch the Workgroup Administrator in Access

Also learn how to launch the Workgroup Administrator in Access 2003/XP | Access 2000/97.

Question: In Access 2003/XP, how do I find and run the Workgroup Administrator?
Answer: To run the Workgroup Administrator for Access 2003/XP, you'll need to first launch Access.

Remove a database-level password from a database in Access

Question: In Access 2003/XP/2000/97, how do I remove a database-level password from a database?
Answer: To remove a database-level password, exclusively open your Access database.

Set a database-level password on a database in Access

Question: In Access 2003/XP/2000/97, how do I set a database-level password on a database?
Answer: To set a database-level password, exclusively open your Access database.

Conditionally format a field in a report in Access 2003/XP/2000/97

Question: In Access 2003/XP/2000/97, I have a database and I want to change the Background color of a field on a Report based on the value. Is this possible?
Answer: We've created a sample Access database that demonstrates how to apply a conditional format to a report in Access97.

Conditional formatting - Change the formatting of a field in a report when the field contains a NULL value in Access 2003/XP/2000/97

Question: In Access 2003/XP/2000/97, I'm trying to create a report that will highlight a field that "Is Null". How can I do this?
Answer: If you want to highlight a field that "Is Null", you can apply conditional formatting. To do this, open your Access report and highlight the field that you want to change the behavior of. In this example, we've selected the OrderDate field.

Conditional formatting - Change the formatting of a field in a report based on its value in Access 2003/XP/2000/97

Question: In Access 2003/XP/2000/97, I am trying to create a report that will have one field change colors based on the results of a query. How do I do this?
Answer: If you want to change a field in a report based on its value, you can apply conditional formatting. To do this, open your Access report and highlight the field that you want to change the behavior of. In this example, we've selected the CategoryID field.

Fill in missing data on a graph in Access 2003/XP/2000/97

Question: In Access 2003/XP/2000/97, I've created a report bar graph and each bar represents the data from a different year. There are some years where there is no data, in other words, there are no records with that year in them, so that year is skipped on the graph. How can I get the years without data to appear on the graph with no bar because there is no data?
Answer: To force the years without data to appear on the graph, you'll have to create some additional queries.
Let's take a look at an example.

Create a graph (ie: chart report) in Access 2003/XP/2000/97

Question: In Access 2003/XP/2000/97, is it possible to create a graph?
Answer: Yes, in Access you can create a graph. This is called a Chart report.
Let's walk through an example.
Open your Access database and click on the Reports tab in the Database window.

Run-time error '424' (Object Required) in Access 2003/XP/2000/97

Question: In Access 2003/XP/2000/97, I'm using VBA code to set a value on another form and I keep getting a "Run-time error '424': Object Required" error. I can't seem to figure out why it won't work. The object that I'm referencing is valid and it should work. What am I doing wrong?

Trim function will not work in a report in Access 2003/XP/2000/97

Question: In Access 2003/XP/2000/97, I've created a report and I'm trying to use the trim function in a field on the report. But when I try to, the field shows up as #Error.
How can I get the trim function to work in a report?
Answer: To use the trim function in Access, there are 2 steps that you must perform.
Let's take a look at a report to demonstrate.
In the picture below, we've tried to use the trim function on the Category Name field and it is generating a #Error for each line.

How to fix a "Compile error.in query expression" Error in Access 2003/XP/2000/97

Question: In Access 2003/XP/2000/97, I tried using the Left function in a query and I get the following error:

"Compile error.in query expression"

I've seen this function work in other Access databases. How come it does not work in mine?
Answer: This error can occur when there is a broken Reference in your Access database and you've tried to use one of the built-in Access functions such as Left, Instr, Len, Year, Mid, etc.

MS Access: Database variable "not defined" error in Access 2003/XP/2000/97

Question: I'm running an Access 2003/XP/2000/97 database with some VBA code in it.
Within the VBA code, a database object is declared as follows: