Backup 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 sample code in production check back in a week and a month to see if there are any changes or comments.

Objective

I wanted to create a method of backing up a personal SQL Server database from within Access.   In a production environment you'd have tasks which ran on a regular basis, i.e. at midnight or whatever, that would do this.  But here I might update some records every few days and wanted to do this on my schedule.
Note that, as the names of the stored procedures indicate, I will be modifying these as I add more functionality such as database checks, log truncation, etc.

General SQL Server setup tips

[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.