tag:blogger.com,1999:blog-81656700374982441472024-03-08T18:50:35.917+07:00Submit AccessThe World MS AccessAnonymoushttp://www.blogger.com/profile/06323881403942039163noreply@blogger.comBlogger56125tag:blogger.com,1999:blog-8165670037498244147.post-85926465982248595542013-03-12T11:48:00.001+07:002013-03-12T11:49:27.232+07:00Backup of SQL Server databases from within Microsoft Access
Sent to you by DuyTuan via Google Reader:
Backup of SQL Server databases from within Microsoft Access
via Tony's Microsoft Access Blog by Tony on 12/16/12
WARNING: Some of the following instructions may not be best practice. This is a personal database so I can afford to take some shortcuts and not do thorough research. If you do use these tips and Duy Tuanhttp://www.blogger.com/profile/08934580372824443345noreply@blogger.com1tag:blogger.com,1999:blog-8165670037498244147.post-37251740678702108042012-11-30T15:05:00.003+07:002019-08-16T13:15:28.440+07:00[Clip]Export Access to MySQL Duy Tuanhttp://www.blogger.com/profile/08934580372824443345noreply@blogger.com0tag:blogger.com,1999:blog-8165670037498244147.post-2074981766145221312012-10-11T09:03:00.000+07:002012-10-11T09:04:17.705+07:00View system tables in database windowQuestion: In Access 2003/XP/2000/97, how can I view system tables in the database window?
Answer: Under the Tools menu, select "Options".
When the Options window appears, check the "System objects" checkbox. Then click on the OK button.
This will return you back to the database window where you should now be able to see all system objects.
To learn how to hide your tables as system objects, Duy Tuanhttp://www.blogger.com/profile/08934580372824443345noreply@blogger.com0tag:blogger.com,1999:blog-8165670037498244147.post-85941207578352213742012-10-09T13:05:00.000+07:002012-10-11T09:04:31.494+07:00Hide tables as system tables in AccessQuestion: 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 Duy Tuanhttp://www.blogger.com/profile/08934580372824443345noreply@blogger.com1tag:blogger.com,1999:blog-8165670037498244147.post-62814676523572871442012-10-07T17:54:00.000+07:002012-10-07T17:54:37.516+07:00Hide tables as hidden objectsQuestion: 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.
To do this, right-click on the table that you wish to hide and select Properties from the popup menu.
When the Duy Tuanhttp://www.blogger.com/profile/08934580372824443345noreply@blogger.com0tag:blogger.com,1999:blog-8165670037498244147.post-65444920970533433772012-10-07T10:35:00.001+07:002012-10-07T10:36:25.670+07:00Open the database exclusivelyQuestion: 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 Duy Tuanhttp://www.blogger.com/profile/08934580372824443345noreply@blogger.com0tag:blogger.com,1999:blog-8165670037498244147.post-85436337463887440722012-10-07T04:01:00.001+07:002012-10-07T04:02:21.927+07:00Display/Unhide the database windowThere 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.
When the "Unhide Duy Tuanhttp://www.blogger.com/profile/08934580372824443345noreply@blogger.com0tag:blogger.com,1999:blog-8165670037498244147.post-3580496584817940782012-10-05T17:00:00.000+07:002012-10-06T18:19:35.574+07:00Hide 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 Duy Tuanhttp://www.blogger.com/profile/08934580372824443345noreply@blogger.com1tag:blogger.com,1999:blog-8165670037498244147.post-76482763608177267312012-09-05T20:45:00.001+07:002012-09-05T20:50:30.901+07:00Bind combo box to primary key but display a description field in AccessQuestion: 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 Duy Tuanhttp://www.blogger.com/profile/08934580372824443345noreply@blogger.com1tag:blogger.com,1999:blog-8165670037498244147.post-686811329301964882012-08-24T13:33:00.000+07:002012-08-24T13:33:02.806+07:00DateDiff FunctionIn 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.
IntervalDuy Tuanhttp://www.blogger.com/profile/08934580372824443345noreply@blogger.com2tag:blogger.com,1999:blog-8165670037498244147.post-9701736012693849882012-08-16T20:15:00.002+07:002012-08-16T20:16:04.494+07:00View only records with uppercase letters for a certain field in AccessQuestion: 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.
To query on case-sensitivity, we've created a function called ContainsLower. Duy Tuanhttp://www.blogger.com/profile/08934580372824443345noreply@blogger.com0tag:blogger.com,1999:blog-8165670037498244147.post-14959090434596614732012-08-16T20:13:00.001+07:002012-08-16T20:16:18.055+07:00Calculate and format an elapsed time value in AccessQuestion: 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 storeDuy Tuanhttp://www.blogger.com/profile/08934580372824443345noreply@blogger.com0tag:blogger.com,1999:blog-8165670037498244147.post-68415547478325889352012-08-06T19:17:00.000+07:002012-08-06T19:18:16.839+07:00Convert currency into wordsQuestion: 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.
Then paste into the new module the following two functions:
Function English(ByVal N As Currency) As String
Const Thousand = 1000@
Const Duy Tuanhttp://www.blogger.com/profile/08934580372824443345noreply@blogger.com0tag:blogger.com,1999:blog-8165670037498244147.post-36108201116861439062012-08-05T04:49:00.001+07:002012-08-05T04:49:31.267+07:00Generate a random number between 2 user-specified valuesIn 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.
For Example:Duy Tuanhttp://www.blogger.com/profile/08934580372824443345noreply@blogger.com0tag:blogger.com,1999:blog-8165670037498244147.post-11472708007626013152012-08-04T18:55:00.000+07:002012-08-04T18:56:27.182+07:00Report 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.
Download Access 2000 version
This database contains a Personnel Duy Tuanhttp://www.blogger.com/profile/08934580372824443345noreply@blogger.com0tag:blogger.com,1999:blog-8165670037498244147.post-50701931896463987302012-08-03T20:23:00.000+07:002012-08-03T20:23:08.156+07:00Display parameter values in a report based on a parameter queryQuestion: 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.
Download Access 2000 version
Let's take a look at this example.
We've created a Duy Tuanhttp://www.blogger.com/profile/08934580372824443345noreply@blogger.com0tag:blogger.com,1999:blog-8165670037498244147.post-27580503648390438042012-08-01T22:01:00.002+07:002012-08-01T22:03:52.204+07:00Capitalize 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.
conversion is the type of conversion to perform. The following is a list of valid parameters for conversion.
Parameter Value Description
Duy Tuanhttp://www.blogger.com/profile/08934580372824443345noreply@blogger.com0tag:blogger.com,1999:blog-8165670037498244147.post-2769679354888833032012-07-31T10:31:00.000+07:002012-08-01T08:59:56.922+07:00Renumber invoices with VBA codeQuestion: 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
code:
Function Duy Tuanhttp://www.blogger.com/profile/08934580372824443345noreply@blogger.com0tag:blogger.com,1999:blog-8165670037498244147.post-29102577143597958822012-07-24T21:57:00.000+07:002012-07-26T08:27:34.649+07:00Change 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 Duy Tuanhttp://www.blogger.com/profile/08934580372824443345noreply@blogger.com0tag:blogger.com,1999:blog-8165670037498244147.post-31596457861774327312012-07-22T09:35:00.000+07:002012-07-22T09:35:03.434+07:00Prevent the mouse wheel from scrolling through records in a form in AccessQuestion: 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.
Download MouseWheel.dll file
We recommend that you copy the MouseWheel.dll file to the following directory:
c:\Program Files\Microsoft Office\Office\
But Duy Tuanhttp://www.blogger.com/profile/08934580372824443345noreply@blogger.com1tag:blogger.com,1999:blog-8165670037498244147.post-63926655139740335942012-07-21T21:28:00.000+07:002012-07-21T21:28:51.362+07:00Set 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?
Answer: To force the colors ofDuy Tuanhttp://www.blogger.com/profile/08934580372824443345noreply@blogger.com0tag:blogger.com,1999:blog-8165670037498244147.post-45684030262272550302012-07-20T21:18:00.002+07:002012-07-20T21:18:55.502+07:00Set 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.
To do this, create a label. Right-click on the label Duy Tuanhttp://www.blogger.com/profile/08934580372824443345noreply@blogger.com0tag:blogger.com,1999:blog-8165670037498244147.post-52545370732330409812012-07-19T21:55:00.003+07:002012-07-19T21:56:29.625+07:00Query 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.
Click on the summation button in theDuy Tuanhttp://www.blogger.com/profile/08934580372824443345noreply@blogger.com0tag:blogger.com,1999:blog-8165670037498244147.post-63295536210437839132012-07-18T20:14:00.001+07:002012-07-18T20:41:25.639+07:00Retrieve the user name from Windows while in a database in AccessQuestion: 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:
Declare Function WNetGetUser Lib "mpr.dll" _
Alias "WNetGetUserA" (ByVal lpName As String, _
ByVal lpUserName As String, Duy Tuanhttp://www.blogger.com/profile/08934580372824443345noreply@blogger.com0tag:blogger.com,1999:blog-8165670037498244147.post-10545018491382822012012-07-16T21:59:00.002+07:002012-07-16T21:59:18.985+07:00MS 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 IDuy Tuanhttp://www.blogger.com/profile/08934580372824443345noreply@blogger.com0