Check for Numeric Values in a String using VBA

I needed to check that the values entered into a textbox called StoreID in an Access Database where numerical and composed of 3 digits. Here is one solution: on the AfterUpdate event of the textbox, put this code:

Private Sub StoreID_AfterUpdate()
Dim intLen, intChar, i As Integer
Dim strTextBox As String

intLen = Len(StoreID) 'Len is a function that counts the amount of characters in a string
If intLen <> 3 Then ' if the length is not 3, give message box
MsgBox "The field 'Store ID' requires 3 digits from 001 to 900"
Else ' if length is 3 then assign variables and start loop for checking numerical values
intLen = Len(Me!StoreID)
'Me! always refers to the active form
strTextBox = Me!StoreID
For i = 1 To 3 'For 1 to 3
intChar = Mid(strTextBox, i, 1) 'Mid function takes middle values (expression,start value, length of selection)
If IsNumeric(intChar) = False Then
MsgBox " ' " & intChar & " ' is not a number, please enter numeric values only from 001 to 900"
Exit Sub
Else
End If
Next
End If
End Sub

Call an Access Database Procedure from another Access Database

Here is an Access VBA example of how to call an external Access Database VBA procedure from within a different access database.

Sub ForeignProcedure()

Dim appAccess As Access.Application

' Create instance of Access Application object.
Set appAccess = CreateObject("Access.Application")

' Open MyDatabaseName.mdb database in Microsoft Access window.
appAccess.OpenCurrentDatabase “C:\MyDatabaseName", False
'False so as not to open in exclusive mode
' Run Sub procedure.
appAccess.Run "ForeignProcedureName"
appAccess.Quit
Set appAccess = Nothing

MsgBox "Done!"

End Sub

Call an Access Database VBA Procedure from Excel

In order to call an Access vba procedure from an Excel spreadsheet, the first thing you need in Excel is a Reference for the Microsoft Access Object Library, as seen in the picture below ...




To see this screen, from within Excel, go to VBA (press ALT + F11), then select Tools from the top menu and then References

Public Sub ProcedureInAccess()

‘this VBA code is run in Excel
Dim acApp As Object
Dim db As Object
Set acApp = CreateObject("Access.Application")
acApp.OpenCurrentDatabase ("C:\MyDatabaseName.mdb")
Set db = acApp
acApp.Run "NameOfProcedureInAccess"
acApp.Quit
Set acApp = Nothing

End Sub

Delete Rows from Access Database Table Using VBA and SQL

Using the DoCmd property you can delete all rows in an Access Database table using Access VBA and SQL

Sub DeleteRows()

'Deletes all rows from tblName in an Access Database

DoCmd.RunSQL "DELETE tblName.* FROM tblName;"

End Sub

Access Database Programmer Forum

I recently came across a great resource for Access Database programming. It is a forum where you can post questions and receive knowledgeable answers from the forum's members. http://www.utteraccess.com/

Transaction Log for Microsoft Access Database

Here is one of the most useful pieces of code I have every come accross the Internet for programming an access database. This code will track all changes made by a user, meaning it will keep track of what data was changed, who changed it and when it was changed. This functionality is usually called a transaction log or a change log. I have copied the code directly from this page:http://www.tek-tips.com/faqs.cfm?fid=291 . The code was written by Michael Red a regular contributor at Tek-tips, which is a wonderful website that I have used many times for finding answers to many programming questions.

I have learned a few things not explained in his original code:

1) Put this code in a Module.
2) Call the basLogTrans function from the BeforeUpdate event of any form that you want to track changes.
3) Pass 3 values to the basLogTrans function: FormName, Name of Field with Unique ID, and the Unique ID.
In my implementation, I use Me as the form name, "CustomerID" as the Unique ID field, and CustomerID to pass the actual customer id value. So here is how I would call the basLogTrans function:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Call basLogTrans(Me, "CustomerId", CustomerId)

End Sub

4) Mike says the code will work only on a form formated as single form, but I see it also works on a datasheet form.
5) He alos says that Access Security needs to be enabled, but I'm not sure it's true. When I used his original code for CurrentUser(), I got back "Admin" as the user that was making changes. But what I really want to know is the NT User ID of whoever is making the changes, therefore, I use Environ("Username"). If you have problems with the Environ function, please see here how to enable the Environ Function properly.
6) The rest of the instructions are pretty clear, but just to be safe, remember to add a reference for DAO, bind the form to a table, but who's to say this won't work on a Select Query? I don't know, why don't you tell me later? Make 2 tables, one called tblHist and the other tblHistMemo. First create tblHist, with the fields indicated in code comments in the function called basAddHist, then make a copy of it, name it tblHistMemo and change the OldVal and NewVal fields to datatype Memo.

That's it, it should work easily.



OK, HERE IS THE ORIGINAL VBA CODE:
All this needs to be placed in a Module, and called from the BeforeUpdate event of a form.


Public Function basLogTrans(Frm As Form, MyKeyName As Variant, MyKey As Variant) As Boolean

'Michael Red 8/1/2002 Transaction log for Ms. Access BOUND forms.

'The following three functions and table definitions are
'(should be) sufficient to establish a 'poor mans' transaction
'log for Ms. Access bound forms.

'As noted in basAddHist, you will need to create the tables
'for History (general fields) and Memo History (history of
'JUST memo fields).

'Also, note that these functions are designed to work in
'conjunction with Ms. Access Bound forms where the view is
'set to single form. Further, the history will only capture
'the user if the db App is "secured".

'In the following, "MyKey" refers to the Record ID (Unique value)
'for the single record which the form is 'tied' to.

Dim MyDb As DAO.Database
Dim MyCtrl As Control
Dim MyMsg As String
Dim Hist As String


'In the Statement block below, basFlgValidRec is a routine which
'includes validation of all fields necessary to 'define' a record
'is valid. Commented out here, as I do not know what validation
'rules need to be checked/enforced.

' If (Not basFlgValidRec) Then
' basLogTrans = False 'Let User know of FAILURE!!
' Exit Sub
' End If


For Each MyCtrl In Frm.Controls
If (basActiveCtrl(MyCtrl)) Then 'Consider replacing w/ test for "ControlSource"
If ((MyCtrl.Value <> MyCtrl.OldValue) _
Or (IsNull(MyCtrl) And Not IsNull(MyCtrl.OldValue)) _
Or (Not IsNull(MyCtrl) And IsNull(MyCtrl.OldValue))) Then
If (MyCtrl.ControlType = dbMemo) Then
Hist = "tblHistMemo"
Else
Hist = "tblHist"
End If
Call basAddHist(Hist, Frm.Name, MyKey.Name, MyCtrl)
End If
End If
Next MyCtrl

basLogTrans = True 'Let User know sucess

End Function
Public Function basActiveCtrl(Ctl As Control) As Boolean
Select Case Ctl.ControlType

Case Is = acTextBox
basActiveCtrl = True

Case Is = acLabel
Case Is = acRectangle
Case Is = acLine
Case Is = acImage
Case Is = acCommandButton
Case Is = acOptionButton
Case Is = acCheckBox
basActiveCtrl = True
Case Is = acOptionGroup
Case Is = acBoundObjectFrame
Case Is = acListBox
basActiveCtrl = True
Case Is = acComboBox
basActiveCtrl = True
Case Is = acSubform
Case Is = acObjectFrame
Case Is = acPageBreak
Case Is = acPage
Case Is = acCustomControl
Case Is = acToggleButton
Case Is = acTabCtl

End Select
End Function
Public Function basAddHist(Hist As String, Frm As String, MyKeyName As String, MyCtrl As Control)

'tblHist
'FrmName Text 80 Name of the form where change Occured
'FldName Text 80 Field Name of the changed value
'dtChg Date/Time 8 Date/Time of Change (MACHINE value!!)
'OldVal Text 255 Field Value BEFORE change
'NewVal Text 255 Field Value after change
'UserId Text 50 User who Made Change
'MyKey Variant ?? KeyField as Indentified by Caller
'MyKeyName Text 80 'Key Field Contents

'tblHistMemo is the same structure except the "type" for the fields
'OldContents and NewContents are Memo (and therfore the length is "??")

Dim dbs As DAO.Database
Dim tblHistTable As DAO.Recordset

Set dbs = CurrentDb
Set tblHistTable = dbs.OpenRecordset(Hist, dbOpenDynaset)

With tblHistTable
.AddNew
!MyKey = Forms(Frm).Controls(MyKeyName)
!MyKeyName = MyKeyName
!frmName = Frm
!FldName = MyCtrl.ControlSource
!dtChg = Now()
' !UserId = Environ("Username") 'Orlando's Way
!UserId =CurrentUser() 'Mike's way, example of current user would be Admin
!OldVal = MyCtrl.OldValue
!NewVal = MyCtrl
.Update
End With

End Function

First and Last Access Function not supported in SQL Server

In a Microsoft Access database, in order to return the first or last record in the result set returned by a query, you can use the First and Last Functions. The syntax is simple: First(expr) or Last(expr). If you try to use these functions in SQL Server, however, they will not work. You will get a message similar to this:

Server: Msg 195, Level 15, State 10, Line 2
'First' is not a recognized function name.

In order to repeat the desired outcome in SQL Server, simply use the MIN and MAX function.

ACCESS SQL VERSION:
SELECT
ContractorID, FIRST(Contractor)
FROM
tblContractor
WHERE
ContractorID = "ABC6783"
GROUP BY
ContractorID

(T-SQL) SQL SERVER VERSION:
SELECT
ContractorID, MIN(Contractor)
FROM
tblContractor
WHERE
ContractorID = 'ABC6783'
GROUP BY
ContractorID

Loop in VBA Easy Example

There are different versions of looping in VBA. Here is an example of the Do While version.

Sub letsLoop()
Dim intCounter As Integer

intCounter = 1 'Set the counter to 1

Do While intCounter <3'Loop while value of intCounter less then 3
MsgBox intCounter 'show message box with value of intCounter
intCounter = intCounter + 1 ' increase value of intCounter by 1
Loop 'loop again

End Sub

Capture Signature and Date Stamp in Excel Worksheet

With all the Sarbanes-Oxley (SOX) requirements going around lately, there has been a big push to making staff attest that they have reviewed the various Excel reports they are sent. One way to do that is to have them sign and date the report and save the report somewhere on the network. Later I will run an Excel macro that goes and looks for everyone’s signature and produces a report of who has signed and when.

Anywho, for the signature portion, I've added a sheet in the Excel reports, the sheet is called Signature. This sheet has a check box. Clicking this check box called chkSignature, populates cells C2 and C3 with the Windows User ID and the Date.

For the user's Windows User ID, we use the method Environ("username"). And for the Date we use well, the Date function.

Here is the code behind the click event of check box chkSignature:

Private Sub chkSignature_Click()

If chkSignature.Value = True Then
Sheets("Signature").Range("C2").Value = Environ("username")
Sheets("Signature").Range("C3").Value = Date
Else
Sheets("Signature").Range("C2").Value = ""
Sheets("Signature").Range("C3").Value = ""
End If

End Sub

Automatically Maximize Access Form

To maximize an Access form automatically, use the "Maximize" method of the DoCmd Object.

Example, let's say I have a form called MainForm, on the "On Load" Event property of the form, I would use this sub procedure:

Sub MainForm_Load()

DoCmd.Maximize

End Sub

Find the Number of Series in Excel Chart

While adding a trend line to a group of charts, I needed to know how many data series where in the Graph's Series Collection. I will add an equal amount of trend lines.


Sub countseries()

MsgBox ActiveChart.SeriesCollection.Count

End Sub

Using a Combo Box in Excel

From the top menu, navigate thru: View / Toolbars / Control Toolbox



When the Control Toolbox appears, Select the Combo Box control. With your mouse, make a rectangular shape on the excel sheet.



Define the choices that will be available in the Combo Box. In this example, the names of fruits are written on Column F from row 1 thru 4.




Tell the combobox where to get it's data from. Right click on the combobox to get it's properties.




Then in the ListFillRange, enter the range of cells that hold the combox values. In this case F1:F4.



That's all there is to it, close design view and your combo box should be working fine.

Securing an Access Database

A reader from Kuwait wrote the other day...


Orlando,

I was browsing for some help on the net and just saw
your name, I had a question, could you please help me.

My question is, i have developed a database, and i
want to give it to my friend, the question is, i want
to secure the Queries, Form & Reports from being
edited or re-designed, i dont want these to be visible
whenever he uses it. he should be able to print &
update database, but he should not be able to see what
queries i have set as well he should not be able to go
to the design view to modify my forms & Reports.

Could you please help.

Regards,

Oswald
Kuwait


Oswald...

There are a couple of different ways that the above can be accomplished.

1) User Level Security. But I don't recommend it because it is difficult to maintain.

2) A combination of setting the Start Up options, tweaking the properties of the Forms, and making an mde file.

You will have to play around with option number 2 to get the desired results. But here are a few pointers.

From the menu bar, go to Tools / Start Up and un-check "Display Database Window". Next time the database starts, the database window will not be available. If you want to see it, then you would go to Window / Unhide and select the database window. If you choose to not see the database window, you would have to have some form that would serve as the main menu for your database.




Another thing that you could do is hide your database objects, but this is the least effective way, because in the Database Options there is the option to View Hidden Objects. But anyway, here is how you could hide them: Right Click on the object to arrive at the Properties window and check Hidden.




Another alternative is to allow Design Changes on Forms Only in Design View. And to not allow viewing the form any other way then you intended. Forms can be viewed as FORMS, DATASHEETS, PIVOT TABLES, PIVOT CHART. So make sure that in the Forms Format Properties you unselect the view types you do not want the user to use.







Finally, after you have implemented a combination of the tips above, make the database an MDE file. Go to Tools / Database Utilities / Make MDE file.

How to Filter Access Subforms based on a control from a Main Form

If you are reading this then you have probably noticed that filtering a Subform based on a control from the Main form is a pain in the neck.

Here's one way to filter the subform by changing the underlying form.

Sub frameTaskStatus_Click()
Dim intStat As Integer
intStat = frameTaskStatus.Value
' 1 = all
' -1= closed
' 0 = open
' 2 = new open frmCreateNewRequest

Select Case intStat
Case -1
Me!sbfrmMainRequests.SourceObject = "subfrmRequestsFiltered"
Case 0
Me!sbfrmMainRequests.SourceObject = "subfrmRequestsFiltered"
Case 1
Me!sbfrmMainRequests.SourceObject = "subfrmRequestsAll"
Case 2
Me!sbfrmMainRequests.SourceObject = "subfrmRequestsNew"
End Select

End Sub


Cases -1 and 0 (closed and open, respectively) make subfrmRequestsFiltered the underlying subform. This subform's datasource is a query that applies a filter also based on the value selected in frameTaskStatus.

Case 1 makes subfrmRequestAll the underlying subform in the Main form. subfrmRequestAll is also based on a query but it is not filtered.

Case 2 makes subfrmRequestsNew the underlying subform in the Main form. subfrmRequestsNew is a data entry only form.

How to Determine the Version or Edition of SQL Server

Open Query Analyzer, connect to any database on your server and type one of the following:

SELECT SERVERPROPERTY('ProductLevel')
SELECT @@VERSION
SELECT SERVERPROPERTY('ProductVersion')

If you are not sure whether you are running an edition of the SQL Server 2000 database engine or MSDE 2000, execute the following query:
SELECT SERVERPROPERTY('Edition')

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'"

#NUM Error when linking Excel Spreadsheet to Access Table

This happens because there is a combination of numbers and letters in one of the columns of the Excel file you want to link. This will always create a numeric field overflow error. Access uses the first 8 rows of the spreadsheet to determine the data type of the column in the linked table. Say for example that your first 8 rows in column A of your spreadsheet have the following data:
1
2
3
4
5
6
7
8
9A

If you link this dataset to an Access table, value 9A will produce a #NUM Error.

Here is some vba code that will fix this problem. The code will insert an apostrophe before the value in any cell that you have selected and in order to make the apostrophe invisible, the code will activate the next cell down the column. When the code is done running on the cells you have selected, the upper left hand corner of the cell will turn green. This means that Excel thinks that this cell has a value that is an error. Specifically, the error message says:
"The number in this cell is formatted as text or preceded by an apostrophe." Clicking on ignore error will do just fine. You can apply this “ignore error” fix to all the cells you have currently selected. If the green indicator doesn’t bother you, leave it alone, it’s harmless.

Let me know if the code helped you. Because I found some code in Microsoft’s website that was supposed to fix this problem but it didn’t. Their code deleted the first character of every cell I had selected.

Here’s my solution:
Select the cells that you want to fix and run this macro:

Sub ConvertToAlphanumeric()

Dim cell As Object

For Each cell In Selection
cell.Value = "'" & cell.Value
ActiveCell.Offset(1, 0).Activate
Next

MsgBox "done!"

End Sub

How to Create a Personal Workbook for Easy Access to VBA Macros

If you have a set of macros / vba code that you would like to have readily available every time you open Excel, put them in the personal workbook. The personal workbook will automatically open but remain hidden every time you start Excel. The macros in this workbook will always be available.

Here's how:
First, record a dummy macro. This step will create the Personal Workbook for you.
>>> Tools / Macro / Record New Macro... / Store Macro in: "Personal Macro Workbook" / OK / and STOP. Your personal workbook has been created.

Now to actually go in and input vba code, press ALT + F11 and expand the PERSONAL.XLS Project. Or unhide the personal workbook first: Window / Unhide / Personal.xls and then edit the dummy macro you just created.

Loop Trough Cells in Excel and Change the Value of an Adjacent Cell

There is an Excel spreadsheet with two columns. Column A contains customer names and column B contains the number of invoices billed. Whenever the cell in column B is empty, I want to input a Zero. If the cell in column B is not empty, then I do not want to modify the existing value.
The loop will stop when it reaches an empty cell in column A.


Sub LoopThruCells()
[A1].Select
intRow = 0
Do While ActiveCell.Offset(intRow, 0).Value <> ""
If ActiveCell.Offset(intRow, 1).Value = "" Then
ActiveCell.Offset(intRow, 1).Value = 0
Else
ActiveCell.Offset(intRow, 1).Value = ActiveCell.Offset(intRow, 1).Value
End If
intRow = intRow + 1
Loop
End Sub

Converting Null values in Access

Use the Nz Function in Access to convert a null value to whatever you desire. This function comes in handy when you are doing divisions. You know you can't divide by 0, so wrap your denominator with the Nz function like this:
Syntax: Nz([Field Name], [valueifnull])

Tips for working with Complex Access Databases used for Data Audits

Most of the development I do these days involves creating Access databases to help audit data, this involves importing data from a human resources system and from PeopleSoft, comparing the data against both systems, and finally exporting the data as EDI files for invoicing. The access databases used for these procedures generally have 30 or more queries. With this amount of queries it is hard to come back 3 months later and remember the purpose each query and the queries’ dependencies. So I have become extra careful in the names I give to the queries. Here are a couple of tips.

1) If the queries run in sequential order, precede the query name with a number such as 01, 02, 03. This will help identify when the query is run in the data audit process. My preference is to name the queries like this:
STEP-01-Import_Excel_File
STEP-02-Make_tbl_Excel_PeopleSoft_Comparison
STEP-03-Export_Results_To_Excel
With this naming convention, I can come back later and sort by the query name to get a quick idea of a query’s dependencies without having to open each query in design view. This has been invaluable in troubleshooting and improving the audit solutions.

2) If you inherit a database that uses a lot of consecutive queries and the names have already been defined and you don’t want to change the names, use the query’s Description field in the Properties Window to give the query an ordinal value. You can then sort by the query’s description field.

3) Always use the Description field in the Properties Window of the query. This can be accessed by right-clicking on the query. If you already use a naming convention similar to tip # 1, then use this field to put comments that tell you something significant or unique about the query. For example if the query connects to an external source such as another Access database, SQL Server or Excel file, here would be the perfect place to put the ODBC definition for future reference.

4) If the query creates a table, then in the description field or even in the query’s name, give the name of the table it creates.
For example: STEP-02-Make_tbl_PeopleSoft_Comparison.

5) Do not rely heavily on queries that query other queries, especially if the underlying query performs complex calculations. For example, say you need to come up with the unbilled percentage of invoices unbilled in PeopleSoft by Customer name. You probably could do this with a single query, but my advice is to first create a summary table that has all the numbers required to perform a percentage calculation by customer name. Then create a second query that performs the percentage calculation based on the summary table. This will make future troubleshooting easier and will help when someone questions your final percentage results.

Sarbanes Oxley Compliance tip for Access Database

I created an Access database for an audit procedure to compare data from PeopleSoft and our Human Resources Information System (HRIS). Based on this weekly audit process, a table is created; the table lists timecards that are unbilled or missing from PeopleSoft. And every week our Billing Accountants are supposed to review this table and update the status of the timecard. So to comply with Sarbanes Oxley, I created a form that logs the Billing Accountant’s Windows User ID when he or she changes the timecard’s status.

On the change event of the “Status” combo box, there is VBA code that updates two text boxes on the form. The first text box is called txtChangedBy and the second text box is called txtChangedDate. These two text boxes are linked to an underlying table and map to appropriate table fields. Whenever the Billing Accountant changes the timecard’s status, his Windows User ID and his computer’s system time are logged. And there you have it, a Sarbanes-Oxley compliant Access database.


Private Sub Billing_Status_Change()

Me!txtChangedBy = Environ("USERNAME")
Me!txtChangedDate = Now()

End Sub

**Please note that for the Environ function to work, Excel’s macro security level must be set to low. This means you better have some good anti-virus protection.

Loop through worksheets in Excel

Sub LoopThruSheets()
Dim wsSheet As Worksheet
Dim strName As String
On Error Resume Next
For Each wsSheet In Worksheets

' ***START CODE FOR WHATEVER OPERATION YOU NEED. ***
strName = wsSheet.name
MsgBox name
'*** END CODE FOR WHATEVER OPERATION YOU NEED. ***

Next wsSheet
On Error GoTo 0
End Sub

Testing your Access Program

A good way to test the VBA code behind your forms is to input values of the following three categories:

1) Expected Values -- these are the values in the range you ask for. If you ask for numbers between 1 and 10, the expected values would be 2 through 9.

2) Boundary Condition Values -- these are the values that lie in the boundaries of your range. The boundary condition values would be 1 and 10.

3) Out-of-Bounds values -- these are the values that fall anywhere outside the range of 1 through 10. For example, 0, "true", "false", (empty), !, @, &, *, etc.

How to determine who is logged on to an Access database

Found some more useful code for finding out who is currently using an Access database. The code worked perfectly on Access 2003.

http://support.microsoft.com/default.aspx?scid=kb;en-us;285822

Access 2003 How to Automatically log out idle users

Found very useful code for automatically logging out idle users in Access. It worked like a charm on Access 2003.

http://www.tek-tips.com/faqs.cfm?fid=1432

Know the Current Working Directory in Access

In an unbound text box on an Access form, type:
=ENVIRON(“PATH”)

Note: the ENVIRON function in Access 2003 is blocked by default. To unblock it, set the Security Level for Macros to Low.

Domain Functions in Access Forms

Some useful functions for getting quick information from tables and queries are the Domain Functions.

Say you wanted to know the earliest value in field [ORDER DATE] in table [ORDERS].
In an unbound text box in a user form, type the following:
=DMIN(“[ORDER DATE]” , “[ORDERS]”)

For the latest date, type:
=DMAX(“[ORDER DATE]” , “[ORDERS]”)

For a quick count of total records in table ORDERS, type:
=DCOUNT(“[Any Field Name in table ORDERS]” , “[ORDERS]”)

For a sum of all rows in a column, type:
=DSUM(“[Any numerical field in table ORDERS]” , “[ORDERS]”)

For the average of all rows in a column, type:
=DAVG(“[Any numerical field in table ORDERS]” , “[ORDERS]”)

Link Access to Excel

A most useful feature is the ability to Link between Excel and Access.

In order to link an Access Query to an Excel spreadsheet, an ODBC connection must first be defined:
Start / Settings / Administrative Tools / Data Sources (ODBC)
System DSN Tab /Add / Choose Microsoft Access Driver (*.mdb)
Name and Describe your data source
Click Select, choose your Access Database, Click OK until the ODBC window closes.
Your ODBC connection is defined.

Now from within Excel:
From the top menu bar, choose: Data / Get External Data / New Database Query
In the Choose Data Source window, select the ODBC recently created and click OK.
Now all the tables and Queries available in the database will appear.
Make your selection and drill down to the fields you want to bring back.
Navigate the next screens by choosing any filters or sorting options.

Next time you want to refresh the data from within Excel, right click on any of the data fields and choose Refresh Data. Alternatively, you can configure the data to be refreshed automatically upon opening Excel. These configurations are found by right clicking in any of the linked fields in Excel and choosing Data Range Properties.

A word to the wise:
If linking many queries in many spreadsheets, name the queries in Access so the will appear on top when sorting by query names. And name your spreadsheets identically to the queries they link. Since the Choose Data Source window in Excel is so small, this naming convention will make maintenance easier.

Known problems:
These linked Queries sometimes start acting up and don't refresh appropriately.
Solution: Delete the rows or Sheet and link the query again--no need for creating new ODBC.

Numeric Field Overflow when importing or linking Excel to Access

When linking or importing from Excel into Access, the first few rows of a column are used to determine the Access column data type. Often times, this assumption causes a Numeric Overflow Error.

Example, the first 10 rows of the field "Project ID" contain only numbers. The remaining rows contain a mix of numbers and letters. Access will assume that the field is numeric and will error out as soon as it tries to import the first set of alphanumeric records.

One solution, in Excel, precede the "Project ID" field with an apostrophe: '
For an automated Excel VBA solution, see this post: Numeric Field Overflow error when linking Access to Excel

Another solution, sort the Excel file so that the alphanumeric records are in the first rows.

Get the User Name in Access

To know the Windows user id currently logged in, in a text box on an Access form, type =ENVIRON("USERNAME") .

This will work immediately in Access 2000. But if using Access XP, then by default it will error out because the macro security level is set to Medium. To change this, go to the top menu, choose: Tools / Macro / Security / Low.

Access--Stop Asking Me Questions!

Say you have a macro in Access that runs several Delete, Make Table and Append Queries. By default, Access will ask for a confirmation before it runs the above query types.

If you don't want to be bothered, use the "SetWarnings" Action in the first row of your macro. Remember to turn it back on at the last row.

Finally, just so you will know for sure that your macro ran to completion, use the "MsgBox" action at the last row.

Find Duplicate Records in Excel

Got Dups in Excel?

Say you have a list of invoices in Column A, and you need to know how many times an invoice is listed.

In column B, use the Count formula....

=COUNT(A1,A1:A8)
The first parameter says which cell to count, and the second parameter delineates the range.

Find Duplicate Records in Access or SQL Server Table

Got dups in an Access or SQL Server table?

To find out if you have duplicates of something, for example Invoices....

SELECT
Table1.Invoice, Count(Table1.Invoice) AS CountOfInvoice
FROM Table1
GROUP BY Table1.Invoice
HAVING (((Count(Table1.Invoice))>1));

Speed up your Excel VBA by not looking at it

I have code that automatically creates excel graphs. After a few months, the amount of graphs that it needed to create grew from 10 to 50 and the execution time significantly slowed down.

I realized that if you minimize the application while it is running, it speeds up dramatically. Alternatively, you could use this vba function: Application.ScreenUpdating = False, which will stop updating the screen as the code is being executed.

Just remember to turn it back on at the end of your code: Application.ScreenUpdating = True

ActiveSheet.Paste stops working when upgrading from Excel 2000 to 2003

So I have some vba code that copies and pastes non-adjacent cells from one sheet to another and it stoped working when Excel was upgraded from 2000 to 2003. I searched the Internet for a solution and found a few people with the same problem but nobody seemed to have a definite solution.

The code would halt at: "ActiveSheet.Paste".

The solution was to use: PasteSpecial xlPasteValues, SkipBlanks.