LEARN MS-ACCESS TIPS AND TRICKS

DIR TREE DOS COMMANDS

a.p.r. pillaiJanuary 14, 2016Accesstips No comments

Introduction.

The Dir Command, originally part of the Disk Operating System, is also available in Microsoft Access VBA. While it is commonly used to check the presence of files or folders on a disk, you can also use it to generate a complete listing of all folders and files, along with their full pathnames. For example, you can retrieve paths in the format 'C:\Folder\Subfolder\Subfolder\... or C:\Folder\Subfolder\FileName'. Such a listing is useful for reviewing disk usage, organizing files, or maintaining records for future reference.

In 1996–97, during our organization’s migration from Novell NetWare to the Windows NT system, all user departments were instructed to review their server folder structures and remove any unused or unnecessary files and folders before the transition. This requirement led me to take a closer look at the DIR command. By using a combination of optional parameters, I was able to generate a complete listing of all folders and subfolders on our department’s server, accessed through the mapped server drive on a Windows 95 client machine. This listing proved invaluable in reviewing the contents and identifying obsolete folders and files for removal before migration.

You can generate a folder listing using either the DIR command or the TREE command, each producing a different style of output. Personally, I prefer the DIR command. The TREE Command creates a graphical view, displaying the hierarchical structure of folders and subfolders. In contrast, the DIR command presents each folder and subfolder on a single line, separated by backslashes. A sample of both listings.

styles is shown below for comparison:

Dir Command has several optional parameters to prepare listings in different ways depending on your requirements. Most of the time, we ignore these options because their usage is not common.

DOS Command Help.

You can get a list of all optional parameters with a simple help command parameter (/?). The Usage is as given below. First, let us open the DOS Command Window.

Right-click on the Windows Start Button and click on the Run command.
Type cmd in the Open control and click OK to open the DOS Command Prompt.
If the prompt appears as something like C:\Users\User>, then Type:
```
Cd \ then press Enter key to set the prompt to C:\>
```
Cd stands for Change Directory command. The \ is the name of the Root Folder. This will set the Disk Drive C root folder as the current.
Tip: Type the Command Exit and press the Enter key to close the DOS Window, any time you want.
Type the following command to display a list of optional parameters of the DIR command:
```
DIR /?
```

You can display the details of any DOS command and its usage in this way, by typing the command followed by /?, in the DOS Command prompt.

Display Folders in C: Drive.

Now, let us display the listing of all the folders in the C: drive on the screen.

Warning: Don't say I didn't warn you that this will be a lengthy list, and may take a few minutes to display all of them on the screen.

Tip: You may terminate the listing at any point by pressing Ctrl+C Keys.

Type the following command in the DOS Prompt and press Enter.

C:/>DIR /A:D/S/B/P

Let us take a look at each parameter given with the DIR command.

DIR Command and its few Options.

/A - Display files with specific Attributes. Specific attributes are given, separated by a colon, like /A:D D - for directories.
/S - include Sub-folders also in the listing.
/B - take a Bare-format listing and exclude summary information.
/P - display the listing Page-wise (Pause the listing when a screen full of information is displayed. Press any key to display the next page).

If you need a listing of a particular folder and its sub-folders only, then include the folder name in the command as given below:

C:/>DIR "\RADIO" /A:D/S/B/P

Saving the Directory Listing to a File.

By default, the output of a DOS command is displayed directly on the screen. If you need a printed copy, the output must first be saved to a text file. This can be done using the output redirection symbol (>) followed by the desired file name. For example, the following command saves the output to a file named FolderList.txt:

C:/>DIR "\RADIO" /A:D/S/B > FolderList.txt

Note: If you are generating a listing of all folders and subfolders/files on a disk, the process may take some time to complete and save all the details to a text file. During this period, it may appear as though the computer has hung. Be patient and wait for the DOS prompt (C:\>) to reappear.

If you wish to terminate the command before it finishes, you can press Ctrl + C.

You may open the text file FolderList.txt in any plain text editor program to take printouts.

The TYPE of Command.

You may use the following DOS command to display the contents of the text file:

Type FolderList.txt | More

Type - Displays the contents of the text file on the screen.

| (Vertical Bar) – This symbol is called the piping symbol. It directs the output of one command to another command for further processing.

For instance, when used with the Type command, the piping symbol passes its output to the More command. The More command displays the output one screen at a time, similar to using the /P parameter with the DIR command. Press any key to view the next page of output.

The TREE Command.

The TREE command displays the folder list in a hierarchical structure.

C:/>TREE | More

Display the folder structure listing page-wise.

C:/>TREE/F | More

/F parameter displays folder names followed by Filenames.

Hope you have enjoyed doing something different and useful.

Calculating Work Days from Date Range

a.p.r. pillaiDecember 15, 2015Accesstips, Functions, ms-access functions No comments

Introduction.

How to find the number of workdays (excluding Saturdays and Sundays) from a date range in Microsoft Access?

The logic is straightforward: first, determine the number of whole weeks within the specified date range. Multiplying whole weeks by 5 gives the number of workdays from entire weeks. From the remaining days, exclude Saturdays and Sundays, if any. Add the remaining days to the total workdays.

DateDiff() and DateAdd() functions are used for calculations, and the Format() function gets day-of-the-week in the three-character form to find Saturday and Sunday to exclude from the remaining days.

Find the VBA Code segments for the above steps below, and the full VBA Work_Days() Function Code at the end of this Article.

Find the number of Whole Weeks between Begin-Date and End-Date:
```
WholeWeeks = DateDiff("w", BeginDate, EndDate)
```
The WholeWeeks * 5 (7 - Saturdays & Sundays) will give the number of working days in whole weeks. Now, all that remains is to find how many working days are left in the remaining days, if any.

Find the date after all the weekdays:

DateCnt = DateAdd("ww", WholeWeeks, BeginDate)

Find the number of workdays in the remaining days by checking and excluding Saturdays and Sundays:

Do While DateCnt <= EndDate
      If Format(DateCnt, "ddd") <> "Sun" And _
        Format(DateCnt, "ddd") <> "Sat" Then
         EndDays = EndDays + 1
      End If
      DateCnt = DateAdd("d", 1, DateCnt)'increment the date by 1
    Loop

Calculate the Total Workdays:
```
Work_Days = Wholeweeks * 5 + EndDays
```

The Whole Calculation in the Work_Days Function.

The full VBA Code of the Work_Days() Function is given below:

Function Work_Days(BegDate As Variant, EndDate As Variant) As Integer

   Dim WholeWeeks As Variant
   Dim DateCnt As Variant
   Dim EndDays As Integer
         
   On Error GoTo Err_Work_Days

   BegDate = DateValue(BegDate)
   EndDate = DateValue(EndDate)
'Number of whole weeks
   WholeWeeks = DateDiff("w", BegDate, EndDate)
'Next date after whole weeks of 7 days each
   DateCnt = DateAdd("ww", WholeWeeks, BegDate)
   EndDays = 0 'to count number of days except Saturday & Sunday

   Do While DateCnt <= EndDate
      If Format(DateCnt, "ddd") <> "Sun" And _
        Format(DateCnt, "ddd") <> "Sat" Then
         EndDays = EndDays + 1
      End If
      DateCnt = DateAdd("d", 1, DateCnt)'increment the date by 1
    Loop
'Calculate total work days and return the result
   Work_Days = WholeWeeks * 5 + EndDays

Exit Function

Err_Work_Days:

    ' If either BegDate or EndDate is Null, return a zero
    ' to indicate that no workdays passed between the two dates.

    If Err.Number = 94 Then
                Work_Days = 0
    Exit Function
    Else
' If some other error occurs, provide a message.
    MsgBox "Error " & Err.Number & ": " & Err.Description
    End If

End Function

The above VBA Code was taken from the Microsoft Access Help Document.

Microsoft Access Form Move Size Action

a.p.r. pillaiDecember 09, 2015Forms, msaccess forms No comments

Introduction.

We design Access Forms that fits into the existing Application Window Width (to display/edit records), or design popup Forms with specific size without borders or scroll bars (can be moved out of the Application Window area too) or Modal type form (popup type forms with its Modal property value set to True) that must be closed, after taking suggested action on it, before you can work with other forms.

This type of form opens one over the other (when more than one form is open) on the Application Window. You must enable Overlapping Windows by selecting Office Button - - > Access Options - - > Current Database - - > Document Window Options - - > Overlapping Windows to open the forms in this way; otherwise, they will be opened in the Tabbed style in the Application Window.

The Pop-Up Forms.

Popup Forms will open on the exact location of the Application Window, from where you have saved it during design time. If you need more details on this topic, visit this Article Link: Positioning pop-up Forms.

We can open a Microsoft Access Form and move it to a particular location of the Application Window is in the resized state, if necessary, with the MoveSize Action on the Docmd Object.

The MoveSize Action.

Here, we will learn the usage of the MoveSize Action of the DoCmd Object in VBA.

View the YouTube Demo Video given below for reference. Select the 720p HD Option from the Settings for better quality viewing.

Demo Video

When the Supplier Code is selected on the Supplier List Form, the related Product List is displayed, above the Supplier Form, to the right of the main form. The width of the Product List Form is not changed, but the height of it changes, depending on the number of items on it.

Right-click to open Large Image in New Tab/Window.

We need two tables, two Queries, and the Supplier List Form from the Northwind sample database to build this trick. You need to design a Form for the Product List. A Demo Database is given at the end of this Article to download and try out, right away.

The list of Tables, Queries, and Forms required to build this database is given below.

Tables:

Suppliers
Products

Queries:

Suppliers Extended
ProductListQ

SQL Code:

SELECT Products.[Supplier IDs], Right([Product Name],Len([product name])-17) AS Product, Products.[List Price], Products.[Quantity Per Unit]
FROM Products
WHERE (((Products.[Supplier IDs].Value)=[forms]![Supplier List]![id]));

Forms:

Supplier List
Product List

Copy and Paste the following VBA Code into the Supplier List Form's VBA Module and save the Form:

Private Sub Company_Click()
Dim frm As Form, ProductForm As String, items As Integer
Dim mainFormHeight As Integer
Dim intHeader As Integer, intFooter As Integer
Dim intH As Integer, frmchild As Form, oneInchTwips As Integer

On Error GoTo Company_Click_Err

ProductForm = "Product List"
oneInchTwips = 1440 'Form's internal value conversion factor

mainFormHeight = Me.WindowHeight

For Each frm In Forms
  If frm.Name = ProductForm Then
    DoCmd.Close acForm, ProductForm
    Exit For
  End If
Next
DoCmd.OpenForm ProductForm
Forms(ProductForm).Refresh
items = DCount("*", "ProductListQ")

Set frmchild = Forms(ProductForm)
'Calc the required height of the chid-form
'based on number of items for selected supplier
intHeader = frmchild.Section(acHeader).Height
intFooter = frmchild.Section(acFooter).Height
'0.272 inch - product item row height
intH = intHeader + items * 0.272 * oneInchTwips + intFooter
intH = intH + oneInchTwips '- one inch margin from bottom
'Move and resize the height of the child form
'4.275 inches to the right from left of the Application Window
'1.25 inches - arbitrary value taken for bottom margin
DoCmd.MoveSize 4.275 * oneInchTwips, mainFormHeight - intH, , (items * 0.272 + 1.25) * oneInchTwips

Company_Click_Exit:
Exit Sub

Company_Click_Err:
MsgBox Err & ": " & Err.Description, , "Company_Click()"
Resume Company_Click_Exit

End Sub

Private Sub Form_Current()
Me.Refresh
End Sub

Note: Don't forget to change the Overlapping Windows option in the Access Option settings mentioned in paragraph two from the top.

Open Supplier List Form.
Click on the Supplier ID Field (with the Company column heading) of any record to open the Supplier products List to display, in the Resized Product List Form, and move it to its specified location.

Download the Demo Database.

Download Demo MoveSize Demo.zip

PROPER Function of Excel in Microsoft Access

a.p.r. pillaiNovember 05, 2015Functions, ms-access functions No comments

Introduction.

We have the UCase() Function in Access to convert all letters in a string to upper-case letters (UPPER() Function in Excel).

? UCase("LeARn mS-access Tips aNd tRicKs")

Result: LEARN MS-ACCESS TIPS AND TRICKS

LCase() Function (LOWER() in Excel) of Access converts all the Text parameters into lower-case.

? LCase("LeARn mS-access Tips aNd tRicKs")

Result: learn ms-access tips and tricks

The Built-in Function PROPER() of Excel converts the first letter of each word in a string of text into upper case and the rest of the text into lower case letters.

I don’t say there is no Function in Access to do that job, but its name is not PROPER.

Microsoft Access Function StrConv() can do what the PROPER() Function does in Excel.

Its usage is as given below:

? StrConv("LeARn mS-access Tips aNd tRicKs",3)

Result: Learn Ms-access Tips And Tricks

The STRCONV() Function.

The STRCONV() Function needs two parameters:

The Text value to be converted
The conversion type parameter is an integer value.
Here, parameter value 3 stands for Proper-case conversion.

I know what you are thinking: can we do other conversions also with this Function?

Yes, STRCONV(Text,1) for UCase() Function and STRCONV(Text,2) for LCase() Function

There are other usages for the STRCONV() function, with different parameters. If you are interested in exploring further, then type STRCONV in the search control of Microsoft Access Help Documents and get the details.

If you think that the name of the function is difficult to memorize, and the requirement of a second parameter is also too much work, then we can go by the Excel way and define a PROPER() Function in Access and use it wherever you want.

Here is the VBA Code:

Public Function PROPER(ByVal strText As String) As String
PROPER = StrConv(strText, 3)
End Function

Copy and Paste the above VBA Code into a Global VBA Module of your Project. If you have a Library Database, then copy and paste the code into it, so that you don't have to duplicate it in all your other Projects.

I will not be surprised if you name the function as PCase() in line with the other text conversion functions LCase() and UCase().

RUNSQL Action in MACRO and VBA

a.p.r. pillaiOctober 11, 2015Action, Macros, Queries No comments

Introduction.

Microsoft Access beginners are often confused about the difference between the RunSQL action in a Macro and the 'DoCmd.RunSQL' method in Visual Basic for Applications (VBA). Regardless of where you use it—Macro or VBA—you must provide the SQL statement of an Action Query or a Data Definition Query. These are the only types of queries supported.

If you’re unsure which queries fall under these categories, refer to the following list:

Action Query Type.

**Action Query Types**
Query Type	Statement
Append	INSERT INTO
Delete	DELETE
Make-Table	SELECT ... INTO
Update	UPDATE

Data Definition Queries.

**Data-Definition Query Types**
Query Type	Statement
Create a table	CREATE TABLE
Alter a table	ALTER TABLE
Delete a table	DROP TABLE
Create an Index	CREATE INDEX
Delete an Index	DROP INDEX

Using the SQL Statement of any other Query type in the RUNSQL Action will end up in errors. In Macro, the length of an SQL statement can be a maximum of 256 characters or fewer.

The 'DoCmd.RunSql' method of VBA can execute an SQL statement with a maximum length of 32768 characters or less.

Note: You are not allowed to give an existing Query Name as a parameter to this command. But, in VBA, you can load the SQL Statement of a predefined query into a String Variable and use it as the parameter to the DoCmd.RUNSQL command.

Example-1:

Public Function DelRecs()
'Existing Delete Query’s SQL is used in this example
Dim db As Database, qryDef As QueryDef
Dim strSQL As String

'Read and save the SQL statement from 'Query48'
'and load into strSQL string variable
Set db = CurrentDb
Set qryDef = db.QueryDefs("Query48")
strSQL = qryDef.SQL

'Execute the SQL statement after
'disabling warning messages
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

'Using DoCmd.OpenQuery Command to run a Delete Query
DoCmd.SetWarnings False
DoCmd.OpenQuery "Query48", acViewNormal
DoCmd.SetWarnings True

End Function

The RunSQL action in a Macro allows you to modify or delete information across multiple records in one step. Before executing an Action Query, Microsoft Access displays a warning message appropriate to the action (such as update or delete) and waits for the user’s response to either proceed or cancel.

Once you have thoroughly tested and perfected the Action Query, you can instruct Microsoft Access to temporarily suppress warning messages during the execution of the RunSQL action. After the query completes, you should re-enable the warning messages so that Access can continue to alert you about unexpected errors or issues as they occur.

The SetWarnings action in a Macro (or the DoCmd.SetWarnings method in VBA) is used to control system warning messages. This is particularly useful when data processing for a report involves one or more Action Query steps within a Macro.

In the Macro design example shown below, the first action is SetWarnings, with its parameter set to No, which temporarily turns off warning messages while the RunSQL action executes in the next step. The third action is another SetWarnings, this time with its parameter set to Yes, which re-enables system warnings. This ensures that Microsoft Access can once again handle and report any unexpected errors as they occur.

Right-click to open Large Image in New Tab/Window.

Example-2:

Create a Table with the Data-definition SQL

Warning: Double-check that you are not using any existing table name for this sample run.

Public Function DataDefQuery()
Dim strSQL As String

strSQL = "CREATE TABLE Books  (Title TEXT(50) NOT NULL, Author TEXT(50) NOT NULL, PublishDate DATE, Price CURRENCY)"

DoCmd.RunSQL strSQL

End Function

The OpenQuery Action in Macro and DoCmd.OpenQuery Method of VBA uses the name of a predefined Query (of any type) to open it in any of the following Views:

Design View
Datasheet View
Print Preview
Pivot Table
Pivot Chart

Hyperlink Opens Objects from Another Database

a.p.r. pillaiSeptember 30, 2015External Links, hyperlinks No comments

Introduction.

Hyperlinks in Microsoft Access provide a convenient way to open internal or external objects without the need to write Macros or VBA code. The Hyperlink Address and Hyperlink SubAddress properties are available for Label controls, Command Buttons, and Image controls.

We have already explored a few examples of using hyperlinks, and links to those articles are provided at the end of this page for reference.

If you haven’t experimented with these properties yet, let’s walk through a demo to understand how both properties can be used effectively.

The Sample Trial Run.

Create a new Blank Form.
Insert a Label control on the Form.
While the label control is still in the selected state, display its Property Sheet (F4).
Note: Hyperlink Address and Hyperlink SubAddress Properties are used for different purposes.
- Hyperlink Address: for opening external objects, like Word Documents, Excel Worksheets, .PDF files, etc.
- Hyperlink SubAddress: for opening internal objects, like Forms, Reports, Tables, Queries, and Macros.
Let us open a Report from your database using the Hyperlink SubAddress property setting.
Find the Hyperlink SubAddress property and write the following text into it:
```
Report <Your Report Name>
```
Replace <Your Report Name> text with one of your own report names (without the < > brackets). The correct format for writing a value into this property is <Object Type>, i.e., Form, Report, Table, Query, or Macro, followed by a space, followed by the actual object name.
Change the Caption Property value of the Label to Open Report.
Save the Form, with the Label control's Hyperlink SubAddress value set with the above changes.
Open the Form in normal view and click on the label control.

You will see how your report is open in Print Preview.

Opening an Excel or Word File.

Now, try opening an MS-Word Document using the other property, the Hyperlink Address setting.

Open your Form and create a second Label control on it.
Display the property sheet of the label control.
Change the Caption property value to Open Word Doc.
Set the Hyperlink Address property value with the full pathname of a Word Document on your computer, like C:\My Documents\Letter.doc
Save the Form with the changes.
Open the Form in normal view and click on the second label to open the Document in MS Word.

Note: You can open Web pages by setting the Hyperlink Address property value to a web address, say https://www.msaccesstips.com

The HyperLink Base.

If your external files, which you plan to open in Access, are all in one location C:\My Documents\ then you don’t need to duplicate it in every control but to specify the Path (C:\My Documents\) at one place: Hyperlink Base and only needs to write the document name (like Letter.doc or any other file on that location) in the Hyperlink Address property.

Right-click to open Large Image in New Tab/Window.

Let us try that:

Click on the Office Button at the top-left corner and highlight the Manage option to display Database options (Access 2007). In Access 2003 and earlier versions, you will find this option in Tools Menu.
Right-click to open Large Image in New Tab/Window.
Select Database Properties.
Select the Summary Tab.
Type C:\My Documents\ in the Hyperlink Base control (see the image given above) and click OK to save it.
Open your Form and remove the C:\My Documents\ text typed in for an earlier example, leaving the Letter.doc file name intact.
Save the Form and click on the label control to open the Word Document.
You may try to open any other document you have in that location, with only the file name changed in the label.

Opening Objects from another Microsoft Access Database.

If you have followed me so far with the above sample exercises, then with a few changes, we can do it.

First, remove the text from the Hyperlink Base control ('C:\My Documents\') and leave the control empty.
Create another Label control on your Form.
Change the Caption Property value to Ext. Database.
Set the Hyperlink Address property value to your external database path, like C:\mdbs\myDatabase.accdb.
Set the Hyperlink SubAddress property value to Report myReport. Change the report name to match yours.
Save the Form with the change.
Open the Form in normal view and click on the label control.

Your external database will open first and will show your report in print preview.

Note: If your database is secured, then it will prompt for User ID and Password. You may try opening other objects: Form, Query, Macro & Table.

Microsoft Access Tutorial Database

a.p.r. pillaiSeptember 22, 2015Accesstips, database, tutorial No comments

Introduction.

In Microsoft Access 2003 and earlier versions, the Northwind.mdb sample database was automatically installed along with the program. This database served as a valuable resource for beginners, providing a practical environment to explore and practice the features of Microsoft Access.

Users migrating from Microsoft Excel often face challenges when transitioning to Access. Many are accustomed to organizing data in worksheets and simple tables, but these lack the flexibility and power of a true database system. While Excel does provide basic database-like features—such as sorting, searching, and filtering—its capabilities are limited compared to Access. Exploring Excel’s Help documentation on database concepts can be a useful starting point for learning fundamental rules of database design and management. By applying these practices, users can structure their Excel data in ways that make it easier to link or import into Access in the future.

Starting with Microsoft Access 2007, however, the Northwind database was no longer installed by default. Instead, users can create it manually from the available sample templates. The Northwind template includes comprehensive examples and tutorial material, making it an excellent resource for learning and practicing database concepts in Access.

How to Create NorthWind.accdb database.

Open Microsoft Access 2007

On the New Database screen, you’ll see several database template categories listed on the left panel. By default, the Featuring category is selected. In the main window, the Blank Database template appears at the top, along with other templates designed for specific purposes.
On the left panel, find the Sample Template category and select it.
Click on the Northwind 2007 Template.

Right-click to open Large Image in New Tab/Window.
The database will be saved in the active folder by default. You may change the folder by clicking on the folder icon to the right of the database file name.
Click the Create Button to create the sample database in your preferred folder.

Always use the sample database as a reference point when working through issues related to table design, setting up relationships, creating queries, forms, reports, or macros. Try experimenting with trial-and-error practices on the specific task you want to accomplish, using whatever knowledge and ideas you already have. This hands-on approach gives you deeper insight into the design process and, in most cases, helps you arrive at solutions on your own.

If you still cannot resolve the issue, the experience gained through trial and error will make it much easier to explain your problem clearly and seek help from other sources.

Access Users Forums.

You can search this Website for topics that you are interested in, or post your queries and get help from experts in Microsoft Access Users' Forums on the Internet. Links to some of the popular Forum Websites are given below:

Designing About Form for MS-Access Project

a.p.r. pillaiSeptember 08, 2015Forms, Logo, Property No comments

Introduction

This is all about the Microsoft Access About Form.

Once your Microsoft Access application development process is complete, you may design a small form called the About Form for your project. The About Form typically displays your project’s logo, name, version number, copyright information, and any other details you wish to include.

Sample About Form image of Windows Live Writer Application is given below as an example:

Right-click to open Large Image in New Tab/Window.

Designing an Access About Form is straightforward. You will typically need:

Two Label controls to display the application name and the current version number.
A Textbox to show the copyright information.
An Image control (optional) to include your custom logo, giving your application a unique identity.

Designing a Simple Access About Form.

Let us design such a small About Form to know what it takes to create one with a simple Logo. Our sample About Form in design view is given below for reference.

Right-click to open Large Image in New Tab/Window.

I have designed a simple logo in MS Word, captured a screenshot of it, modified it in MS Paint, and saved it as a .bmp file.@@@

Open your Microsoft Access Application.
Create a new Blank Form.
Set the Width Property value of the Form to 3.93"
Click on the Detail Section of the Form to select it.
Set the Height property value to 1.53".
Insert an Access Image control from the Toolbox on the left side of the Form and select the Project Logo image from your computer. You may select the Picture Property of the Image control and click on the build (...) button to browse and select the logo image from your computer, if you wish to change the image later.

The Image Properties.
Change the Image property values as given below:

Ms-Access Label & Text Controls.

Insert a Label control to the right of the logo and top of the Form, and resize it to make it wide enough to write the Application name in bold letters.
Write the name of your Project in the Caption property, change the font size big enough to your liking, make it bold, and align the text to the center.
Create another Label control below the first one, with the same width as the first label, and write the Version number of your Project, make it bold, and align the text to the center.
Insert a Textbox below the earlier labels, and change its width to be as wide as the top labels.
Change the following Property values of Text-Box as given below:
MS-Access Command Button.
Create a Command Button below the textbox and position it in the center horizontally.
Make the following changes to the Command Button:

Save the Form and rename it as About or frmAbout.

Open the About Form in normal View and see how it looks on the Access Application Window.

The Property Value Changes.
As you can see, the Access About Form needs some changes to make its appearance like a real About Form. Let us do that to give it the final touches.
Make the following changes in the Form's Property Values as shown below:
Save the Form after the above changes.

View The Application About Form in Normal View.
Open the About form in Normal View.
A sample Image of the completed About Form in normal view is given below.
Right-click to open Large Image in New Tab/Window.

You must add an Option in the Customized Menu of your Project to enable the User to open the Access Application About Form if he/she wish to do so. Alternatively, you may add a Command Button on the Main Form of your Project to do that.

Appending Data from Excel to Access

a.p.r. pillaiAugust 07, 2015Utilities 2 comments

Introduction

Last week, we tried out an interesting method of writing a range of Excel data directly into a Microsoft Access Table. Each row of cells is transferred into the table as a single record. If you haven't come across that article, then you may find it here.

This is an equally interesting method. The selected Worksheet contents are appended to the Access Table with its specified columns in the SQL.

A sample image of the Worksheet data is given below:

Right-click to open Large Image in New Tab/Window.

The VBA Code

Private Sub CommandButton1_Click()
On Error GoTo CommandButton1_Click_Error
'Create Database connection Object
Set cn = CreateObject("ADODB.Connection")
'Access Database must be in the same location of the Worksheet 
dbpath = Application.ActiveWorkbook.Path & "\Database4XL.accdb"
'Get Workbook Full Pathname
dbWb = Application.ActiveWorkbook.FullName
'Get Active worksheet name
dbWs = Application.ActiveSheet.Name
'Create Data Target Connection string to open a session for data transfer
scn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbpath
'Datasheet name to add at the end of the Workbook Name, to complete the
'FROM clause of the SQL String.
dsh = "[" & dbWs & "$]"
'Open session
cn.Open scn

'Append Query SQL String
ssql = "INSERT INTO Table1 ([Desc], [Qrtr1], [Qrtr2], [Qrtr3], [Qrtr4]) "
ssql = ssql & "SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=" & dbWb & "]." & dsh

'Run SQL
cn.Execute ssql

MsgBox "Data Added to " & dbpath & " Successfully."

CommandButton1_Click_Exit:
Exit Sub

CommandButton1_Click_Error:
MsgBox Err & " : " & Err.Description, , "CommandButton1_Click()"
Resume CommandButton1_Click_Exit

End Sub

Courtesy:

The above VBA code was taken from a Forum Post on www.mrexcel.com/Forum and modified to run on the same sample data presented in the earlier article published last week.

The Code on the Worksheet VBA Module is run from the Command Button1 Click Event Procedure.

Writing Excel Data directly into Access Table

a.p.r. pillaiAugust 01, 2015Utilities No comments

Introduction.

Microsoft Access allows you to import data from external sources such as dBase, FoxPro, Excel, or even another Access database. Likewise, you can also export data from Access to these applications, making data exchange and integration seamless.

Following is a list of topics I have published earlier, either on importing, exporting, or working with external data sources from Microsoft Access:

Today, we will explore how to add a range of Excel cell data directly into an Access Table by running the VBA Code from within Excel.

The Algorithm of the program is as given below:

Create an Access Application Object and open it.
Open the target database within the Access Application.
Keep the Access Application window hidden.
Open the target table from the Database.
Take the count of Rows from one of the Excel data columns.
Open a repeating loop to write the Excel data one row at a time, from the second row onwards.
Repeat the writing action till all the rows are transferred to the Access Table.
Close the table and database, and quit the MS-Access Application.

The Excel VBA Code is run by clicking a Command Button on the Excel Sheet. A sample image of the Excel Sheet with data and a Command Button is given below:

The Target Table Structure image is given below:

The Excel VBA Code

Sub Button1_Click()
    Dim objAcc As Object
    Dim recSet As Object
    Dim DataRow As Long, EndRow As Long
    
    On Error GoTo Button1_Click_Err
    
    'Create Access Application Object
    Set objAcc = CreateObject("Access.Application")
    'Open Database in Microsoft Access window
    objAcc.OpenCurrentDatabase "F:\mdbs\Database4XL.accdb", True
    'Keep Access application window hidden
    objAcc.Visible = False
    
    'Open Access Table to add records from Excel
    Set recSet = objAcc.CurrentDb.OpenRecordset("Table1")
    'Take actual row counts of data for transfer
    EndRow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row

    With recSet
      For DataRow = 2 To EndRow
        .AddNew
        ![Desc] = Sheet1.Range("A" & DataRow).Value
        ![Qrtr1] = Sheet1.Cells.Range("B" & DataRow).Value
        ![Qrtr2] = Sheet1.Cells.Range("C" & DataRow).Value
        ![Qrtr3] = Sheet1.Cells.Range("D" & DataRow).Value
        ![Qrtr4] = Sheet1.Cells.Range("E" & DataRow).Value
        .Update
      Next
    End With
    recSet.Close
    objAcc.Quit
    Set objAcc = Nothing

Button1_Click_Exit:
Exit Sub

Button1_Click_Err:
MsgBox Err & " : " & Err.Description, , "Button1_Click()"
Resume Button1_Click_Exit
    
End Sub

Courtesy:

The non-functional raw VBA Code presented by a User at www.mrexcel.com/forum/microsoft-access was modified by me to make it functional and was originally submitted there.

The first field of the table is an ID field with the data type AutoNumber. The ID field value is automatically generated when data is inserted into the other fields.

Introduction.

DOS Command Help.

Display Folders in C: Drive.

DIR Command and its few Options.

Saving the Directory Listing to a File.

The TYPE of Command.

Introduction.

The Whole Calculation in the Work_Days Function.

Introduction.

The Pop-Up Forms.

The MoveSize Action.

Download the Demo Database.

Introduction.

The STRCONV() Function.

Here is the VBA Code:

Introduction.

Action Query Type.

Data Definition Queries.

Example-1:

Example-2:

Introduction.

Opening an Excel or Word File.

The HyperLink Base.

Opening Objects from another Microsoft Access Database.

Introduction.

How to Create NorthWind.accdb database.

Access Users Forums.

Introduction

The Image Properties.

Ms-Access Label & Text Controls.

MS-Access Command Button.

The Property Value Changes.

View The Application About Form in Normal View.

Introduction

The VBA Code

Courtesy:

Introduction.

The Algorithm of the program is as given below:

The Excel VBA Code

Courtesy:

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

Post Feed

Get new posts by email:

Search

USEFUL LINKS

Popular Posts

About Me

Blog Archive

Labels

Statcounter