How to run an Access Macro from Access VBA

DoCmd.RunMacro "macroName"

A word of Advice for my International Programming Brothers

Collation is the way in which data will be sorted in SQL Server.

When building SQL Server databases for American clients, be sure to define the database collation as SQL_Latin1_General_CP1_CI_AS, which means Latin characters, case insensitive, accent sensitive. This is the default setting for Windows Desktops in America. If you are developing in another country, your installation of Windows will probably have a different default language setting. If when you created the SQL Server database, you chose “default” for the collation settings, the database will have been created with a different collation setting, a non-American collation setting.

If you are developing a multi-language capable database, an international database, define your table fields as nvarchar whenever possible. Nvarchar ia a UNICODE character, which means it uses two bytes per character, as opposed to ASCII characters which are one byte in length and thus limits the number of possible ASCII characters

Access: How to Refresh Form Data or Data on a Control

Simple:

use the .Requery Method

Forms!FormName.ControlName.Requery

or if you want to refresh all controls in the form, then

Forms!FormName.Requery

User Friendly Prompt for Requesting Required Information in Access Form

I have a form which has a SAVE RECORD button, which runs a Save Record Macro. The underlying form's table has a Required field. When the Required field is not populated and the SAVE RECORD button is clicked, a popup message prompts the user for the Required field. The problem is that the popup message prompts for the field name as defined in the table and in some cases this might be confusing to the user.

In order to make a user friendly popup message, you could add a condition to the Save Record Macro which checks whether the Required field control is populated or not with: is null(Required Field). If it is true, exit the macro, else continue.

How to Run an Access Macro from Excel VBA

In excel you would need the following Sub procedure:

Sub RunMacroInAccess()

Dim Acc as Object
Set Acc = CreateObject("Access.Application")
Acc.OpenCurrentDatabase("C:\My Documents\AccessDatabase.mdb")
Acc.DoCmd.RunMacro "AccessMacroName"

End Sub

Run an Excel Macro from an Access Database

If you need to run an Excel Macro from within an Access database, place this vba code on the click event of a command button called cmdExcel_Macro. There is no need to add an Excel Reference Library in Access.

Private Sub cmdExcel_Macro_Click()

Dim excelApp As Object ' Declare Excel Application as Object
Dim workbook As Object ' Declare Excel Workbook as Object
Set excelApp = CreateObject("Excel.Application")
Set workbook = excelApp.Workbooks.Open("C:\Temp\FileName.xls") ' Full path of Excel book that has Macro
excelApp.Run "ExcelMacroName" ' Name of the Excel Macro to run
workbook.Close False
excelApp.Quit
Set excelApp = Nothing

End Sub

Loop Through Charts in Excel Worksheet

Here’s a quick piece of code that will generate message boxes for every chart in the active worksheet with the name and top position of the chart

Sub LoopThruCharts()

For i = 1 To ActiveSheet.ChartObjects.Count
ActiveSheet.ChartObjects(i).Select
MsgBox ActiveSheet.ChartObjects(i).Name
MsgBox ActiveChart.Parent.Top
Next i

End Sub

Detect and Remove Spyware

Run the free version of this program called Ad-aware on your computer and I bet you will be surprised by the amount of spyware or tracking cookies that have been installed on your computer without your knowledge. I ran this product after I started noticing that whenever I Googled something, pop-ups would appear on my screen advertising products or services related to my search terms. I thought first of all, Google does not have pop-ups, and second, how the heck is it that these pop-up screens seem to know exactly what I'm searching for.

Anyway, I have used this product on different types of operating systems and it has never created any problems.

You can find the program at this site: www.lavasoftusa.com

Using Shortcut Keys in Windows XP

When I'm typing, I hate reaching over for my mouse; it "breaks the flow of things".

Solution: Create a shortcut key for frequently used programs or websites.

Basically you right click on the file for which you want to create a shortcut key in order to access the file's properties window.

Set the cursor (mouse) in the shortcut key textbox and press any letter. If for example you press the letter "A", you will see that the textbox value has changed to Ctrl + Alt + A. In order to launch the file, typically an *.exe file, you would then press 3 keys at the same time: Ctrl + Alt + A.

The same can be done to launch a website; in this case you would first add the website to your favorites, then right click on the favorites entry and follow the process as described above.

Instant Messaging Aggregator

If you use multiple instant-messaging services, such as AIM, ICQ, MSN, and Yahoo! you might want to aggregate them into one tool. My favorite is called:Trillian. You can download it for free at www.ceruleanstudios.com

Hide System Tables on SQL Server

If you see tables on your SQL Server and have no idea where they came from, you are probably looking at the system tables, which are used by SQL Server. To hide them, you need to right click on the right click on the server (using Enterprise Manager), then Edit SQL Server Registration Properties and Uncheck Show System Databases and System Objects.

No need to update links on Excel Graphs

If you have an Excel Workbook with Graphs and you email it to someone, as soon as the other person opens the workbook, by default, Excel will ask if it should update the links. If the other person does not have access to the data you used to create the graph, meaning if the other person is not mapped to your computer or to the same network shared folder where the data resides, then the other person must decline to update the links.



If you copy and paste your Excel Graph into a new workbook, the links will carry over. However, if you press the SHIFT and CTRL key at the same time you click on Edit on the top menu bar, you will see a new option which allows copying the graph as a picture--no linked data.

SQL SERVER POLL SERVER

The other day I got an angry email from our IT Department asking why I was constantly pinging their SQL Servers. Turns out I was unknowingly Polling all SQL Servers registered in the Enterprise Manager. How? Well, I inherited a computer from a co-worker that left the company. Her computer has SQL Server installed and by default, Enterprise Manager Polls any registered SQL Servers every 10 seconds to see what services are available and inquire about the state of the server. The funny thing is that I remember turning this feature off on her computer a long time ago. But now that I'm logging on to her computer using my own Windows User Id, the settings have reverted to default. Here's what the screen looks like, you can find it in Enterprise Manager under TOOLS / OPTIONS:

Security Warning: Unsafe expressions are not blocked.

Ok this happens every time I move to a new computer and try to open an Access database, I get these annoying pop-ups saying my computer might break if I open the database.

Here’s what they look like:



At this screen, I say NO!

And then I get this screen, asking if I really want to open the file, of course I do, so I do as MS recommends and go to http://www.windowsupdate.com/ to see if I have the latest Microsoft Jet 4.0 Service Pack, and of course I already have it installed, but Microsoft makes me check anyway—whatever.



At this screen, I click YES, because… YES I really want to open the file!
And then I get this screen:
Which basically says that the file I’m trying to open might harm my computer!

I click OPEN of course because I created the database in the first place and I know it isn’t going to harm my computer.



And finally, after 3 annoying pop-ups, the database opens.


To stop getting these pop-ups, you can do the following:

Go to Tools / Macro / Security and set the level to LOW:


BUT YOU BETTER HAVE SOME GOOD UP-TO-DATE ANTIVIRUS SOFTWARE INSTALLED AND CONFIGURED TO CHECK MACROS. OTHERWISE, YOU KNOW WHAT? YOUR COMPUTER MIGHT JUST BREAK!

Get the column letter of the active cell in Excel

Sub GetColumnLetter()
Dim strCol As String

strCol = Mid(ActiveCell.Address, 2, (InStr(2, ActiveCell.Address, "$")) - 2)
MsgBox strCol

End Sub

DAO Recordset Clone Find First String

If you need to find the first record in a recordset that matches a string value, you will need to use double quotes.
The procedure below is triggered by a command button called cmdFind_PONumber.
The name of the form is irrelevant, that is why we use " Me. " to designate the currently active form.
The form has a text box called txtFind where the search criteria is entered.

Example:

Private Sub cmdFind_PONumber_Click()

Dim strPONum As String
Dim db As DAO.Database
Dim rstClone As DAO.Recordset

Set db = CurrentDb
Set rstClone = Me.RecordsetClone
strPONum = Me.txtFind

' PONumber is the name of the field in the underlying table
' strPONum comes from the active form (Me.) and a text box called txtFind

rstClone.FindFirst "PONumber = """ & strPONum & """"
If rstClone.NoMatch Then
MsgBox "PO Number NOT Found!"
Else
MsgBox "PO Number Found!"
End If

End Sub

Add Conditions to your Access Macros

This simple one step macro will show a message box only when the value in a specified text box is null or empty.

Open a new macro or use an existing one.
Go to View / Conditions
A new column called "Condition" should have appeared on the left side of your screen.

For simplicity's sake, for the Action part of the macro, I have a MsgBox Action.

For the condition part, I am going to say:
[Forms]![Put your Form Name here].[Put your Text Box name here] Is Null

Link Access to SQL Server

Instead of copying and pasting data from SQL Server into your Access tables; or instead of exporting tables from SQL Server to an Access database; or instead of importing data from SQL Server into Access; instead, why not link a table in Access to SQL Server?

Here’s how:
Step 01 – Create an ODBC connection for the SQL Server.
In Windows 2000, go to your control panel, in Administrative Tools go to Data Sources (ODBC).
Once the ODBC Data Source Administrator window opens, click on the System Tab.
Add / Select SQL Server (the very last one on the list)
On the next window, name the connection, describe it, and select the server.
Select next and configure how you will authenticate to the server.
Next, select the database you want to connect to.
Finish.
Test Data Source. If Test Successful, OK. If test not successful, go back and tweak your settings in the ODBC Data Source Administrator window.

Step 02 – Link the Access database to the SQL Server using the ODBC connection you just created.
Open Access, from the top menu, select Insert Tables, a pop up window appears, Select Link Table, on the next window, change the Files of Type to ODBC Databases ().
In the Select Data Source window select the Machine Data Source tab; select the name of the ODBC connection you created in Step 01, and finally select the table or view you want.

Trouble Shooting
If you do not see the table or view you are looking for, make sure that the username defined in SQL Server and which is being used by the ODBC connection has permissions to view the table.

If immediately after linking the table, Access wants to assign a unique identifier, you can select one, or simply ignore it by selecting OK.

Access Programmer

My new website is up, still a work in progress, check it out … Access Programmer

Access VBA DoCmd Object to open Form

The methods of the DoCmd object in Microsoft Access can be used to run actions from Visual Basic. Examples of said actions are: closing windows, opening forms and setting the value of a control.

For example to open a form call "Customers" in a normal view, as opposed to a design view or a table view, use this code:

DoCmd.OpenForm "Customers", acNormal


To open the same form but show only records for a customer called Acme Inc (the underlying table or query must have a field called [CompanyName]), use this code:

DoCmd.OpenForm "Customers", , ,"[CompanyName] = 'Acme Inc'"