10 Tips To Be A Successful Boss

While being liked can be more about gender bias in the eyes of the beholders than actual performance, experts do have some advice to help the ambitious thrive.

Connect With Others
Don't be scared to connect with others, especially women, lower down on the totem poll, says Ella L. J. Edmondson Bell, an associate professor at the Tuck School of Business at Dartmouth College and author of Career GPS: Strategies for Women Navigating the New Corporate Landscape. These women can be sources of intel, constructive feedback and emotional strength. "Every good woman needs an 'atta-girl' group."

Be Direct
Declare your intentions as a boss, says Barbara Annis of Barbara Annis & Associates, a firm that guides blue-chip companies in gender diversity and inclusiveness. It's easy to misinterpret a supervisor--especially for women who are known to personalize and ruminate. Directly tell your subordinates, "I really want you to experience me as a source of empowerment and support in getting your job done."

Don't Change Just To Fit In
Be careful not to take on the worst characteristics of male bosses and become overly competitive, overly individualistic, overly warrior-like in trying to prove yourself and to fit in, Edmondson Bell says.

Help Others Into The Pipeline
If you're a pioneer who has paved the road to the top, be sure to allow others to travel on it. Just because one woman had to scratch and claw her way to the top doesn't mean the rest of your team should have to, Annis says.

Be Authentic
Authenticity inspires people and forms solid relationships with others, says Edmonson Bell. Good male leaders are authentic. They talk about being a Marine; they talk about their maleness. "Being a woman doesn't have to be a big deal, it's just a part of who you are. You don't have to hide it," she says.

Provide Clear Feedback
As a leader, it's critical that you provide your employees with clear and precise feedback. There's nothing more frustrating for an employee who wants to move up in the ranks or improve their performance if their boss can't articulate what they need to do to make that happen.

Give Credit Where Credit is Due
Stealing credit, particularly from an underling, is petty and unnecessary. And chances are, if someone you supervise performs well, her success will reflect on you too. After all, you're the one who hired her.

Reward Good Work
While everyone likes to get a raise, it's not the only measure for acknowledging good work. A few words of praise--particularly when money is tight--can go a long way in increasing employee satisfaction. And an employee that feels appreciated tends to be a loyal and productive one.

Encourage Team Work
Pitting employees against each other is counterproductive and just plain bad management. Instead, foster an environment of cooperation and encourage workers to turn to each other for advice and support. Two heads almost always yield better ideas than just one.

Be A Good Role Model
How you conduct yourself in various settings--meetings, networking events, business dinner--sends a clear message to your employees about what is acceptable behavior. If you're always checking your BlackBerry or e-mail when you're talking to subordinates, you're sending a strong message that their time isn't valuable to you. If you're swamped, it's better to tell them that you don't have time at the moment but would love to talk when you can give them your undivided attention.

5 Tricks to Manage Your Manager

Managing upward is especially important when jobs are scarce or on the block.

When the job market falters, many employees try to stay below the boss's radar for fear they might be next on the chopping block. However, it's far wiser to take the opposite approach. Now more than ever, it's important to be noticed (though for the right reasons, of course), explains Tina Chen, a director of permanent and temporary staffing firm Carlisle Staffing in Illinois.

"When companies trim staff, they will save the people who stand out because they manage their managers well," says Chen. "There are also more temporary workers now who could manage upward to get hired."

So instead of being the good-enough team member the boss has trouble remembering, use these tricks to become noticed and indispensable.

1. Understand your boss
"Managing upward requires empathy," says Aaron Nurick, professor of management and psychology at Bentley University in Waltham, Mass. Nurick believes that the key to success is being able to see the world from your manager's perspective.

Instead of becoming frustrated by the boss, observe her work style and communication preferences, and then adapt. You may be flooding the boss with emails while she finds it easier to speak via phone. Subtle adjustments on your part will make her job easier--which makes you look better.

You should also read your manager's reactions to the way you present information, says Nurick. For example, you may work for someone who loves numbers and has little patience for more abstract thinking. In that case, try presenting your boss with a quantitative analysis first, and then succinctly explain its impact. Notice, too, if your boss prefers being asked questions or given answers. Decide if you should cut to the chase or soften your opinion by adding, "Would you agree?"

2. Stay three steps ahead
Staffing expert Chen says that dealing with a boss can be like a game of chess. "Always stay three steps ahead of your opponent, and take the lead," she advises. Employees who anticipate their boss's needs before they arise will be seen as leaders and problem solvers.

Once you understand and start to think like your boss, you'll be in a better position to take initiative. Let's say, says Chen, you're asked to find a person's contact information because your boss is working on an insurance policy renewal. Respond by saying, "Yes I have his information, and would you like me to follow up with him regarding the renewal?"

"For every action there's a reaction and then another action to follow," Chen explains. In other words, anticipate additional questions or requests and handle them before you're asked.

3. Be the better half
Become indispensable by learning your manager's weaknesses and complimenting them with your strengths. Perhaps your boss is a brilliant visionary but impossibly disorganized. Rather than griping about how difficult she is to work with, make up for what she lacks by taking on the role of organizer. Suggest deadlines, remind her of appointments, and follow up on items she's forgotten. Not only will you have gained your boss's trust, you'll become the most valued member of her team.

4. Don't be afraid to speak up
Career expert and coauthor of Crucial Conversations Kerry Patterson says most employees are afraid to approach their managers about issues with their work style. In a survey he conducted, 50 percent of employees said they'd left a company because of a disagreeable boss, but only one in five had spoken with the boss about their concerns.

Patterson says the most common frustrations that employees face are micromanaging bosses and absentee bosses. Yet they rarely speak up. "People think it's risky to address these problems, but if it's handled professionally, it increases your boss's opinion of you," he says.

When initiating a tough conversation, Patterson suggests that you offer an example of a specific behavior, explain how it made you feel, and then ask for the manager's perspective.

Here's an example of what you might say: "Last week you checked in three times on a routine project. It made me feel as if you didn't trust my ability to handle it. Is that the case?" This formula opens the door to an honest conversation without the manager feeling attacked. Patterson emphasizes that it's important to point out specific behaviors and not characteristics of your manager, so that he doesn't take offense.

5. Ask for more responsibility
If you want to ensure a path to the top, Chen says you should make an impression with the even higher-ups--your manager's managers. Allow yourself to shine but don't overstep your boundaries, she warns. Your immediate boss controls your projects and promotions, so you need to maintain his trust.

A great way to gain an audience with company leaders is to ask for more responsibility. Identify a big project that you're confident you can handle, and then pour yourself into it to show what you can do. Another handy trick: Offer to head up an initiative. Maybe you become the resident expert on new software or organize a company event. Suddenly, people come to you with questions and view you as a leader.

Ultimately, make yourself valuable. Not only will you receive respect, but you may be in line for a raise.

http://hotjobs.yahoo.com/career-articles-5_tricks_to_manage_your_manager-1384

Think Twice About Buying Commercial Real Estate

Owning your office space could pump value into your company, but more likely it will distract you from your core business.

Early in my career, I owned a small marketing services business. Beyond a few computers and desks, we didn’t have any assets to speak of.

I feared I wasn’t building any equity in my business but, rather, just piddling away hours. I decided to look into buying a piece of commercial property to operate from as a hedge in case my business turned out to be worthless. I reasoned that at least I could sell the property when it was time to move on.

I had an advisory board of seasoned entrepreneurs, so I convened a meeting to discuss the purchase of a commercial property.

The meeting began well enough, with my outlining the merits of owning the space we operated from. Instead of paying thousands of dollars a month to a landlord, I argued, I could be paying the funds to myself. In 15 years, I calculated, I could own the property outright.

Finally, one of my advisers interrupted: “What makes you think your marketing business isn’t sellable?”

“It’s just a collection of people. I have no hard assets,” I responded.

My adviser dug in. “The only thing acquirers buy—the only thing—is the promise of a reliable stream of future profits. Focus on making your existing business more reliable and predictable, and you’ll have a sellable company.”

The room went quiet; my advisers sensed I needed time to absorb this insight. After a minute or so, another adviser weighed in: “Commercial real estate is a different business. The fastest way to jeopardize your existing business is to get distracted by starting a second business.”

By the end of the meeting, all of my directors had panned the idea of buying a space for our company.

I quickly decided against buying the property and focused instead on growing my business from leased space.

Recently, I ran into a friend of mine who is a partner in a successful venture capital firm. He had just decided to move his office from downtown Toronto to Yorkville, Toronto’s most exclusive real estate market.

I decided to test the advice I had been given years earlier and asked my VC friend why he had chosen to rent instead of buy, which was clearly an option given the success of his firm. “We’re in the business of finding the next big technology business,” he said. “We don’t know the first thing about commercial real estate. Why would I pretend to understand that business?”

In the end, I built a business that was worth more than the real estate I was considering buying. I’ll never know if I would have still been able to pull that off while simultaneously getting into the commercial real estate business. Maybe . . . but then again, maybe not.

Succession Planning: Lessons from Richard Branson

"The company must be set up so it can run without me."—Sir Richard Branson, quoted in a recent interview with HR Magazine.

Richard Branson has achieved so much in his career that he barely requires any introduction.

Over the past few decades, he's been an ever-present in the business landscape, founding new ventures, increasing his fortune and operating with something akin to the touch of Midas throughout.

But even billionaire entrepreneurs have their limitations, and Branson—who turns 60 this month—has clearly begun thinking about what the world will look like without him.

As the HR Magazine piece points out "[f]ew companies have so publicly revolved around one man; Virgin is Branson and Branson is Virgin." With that in mind, it stands to reason that any leader—even one who's just taken the reins—could learn something about succession planning from a man whose entire career has involved being at the center of everything.


"For as much as you need a strong personality to build a business from scratch, you must also understand the art of delegation. I have to be willing to step back now."
Sir Richard Branson
Founder, Virgin

In any leadership position, the question of ego comes up from time to time—if not all the time.

Often, decisions are made less for the good of the company and more because the person in charge wants to look good or be seen to be exercising their authority. Under such leaders, businesses can easily turn into personality cults—something that makes succession planning all the more difficult.

If someone can't envision a present that doesn't involve them pulling all the strings, how likely is it that they'll be able to think about the company beyond the end of their tenure? And yet that's exactly what they're expected to do.

It's clear from the article that Branson sees the challenge of planning for the future as a question of people management: it's about getting the right people into the right positions and empowering them to make decisions that are consistent with the company's existing culture.

How you do that, of course, is another matter.

For Branson, it's about getting out of the way and trying not to let his reputation stifle the people he hires: "For as much as you need a strong personality to build a business from scratch, you must also understand the art of delegation. I have to be willing to step back now."

That's a lesson that doesn't only apply to succession planning. Sometimes the most important thing a leader can do is to resist the urge to handle everything and let other people do the things they're good at—for the good of the company now and in the future.

http://www.cnbc.com/id/38311830/

9 Things I learned in 9 years as a CEO

Nine years ago I set out to help small businesses grow by providing an easy-to-use email marketing tool that was completely affordable. Now VerticalResponse is profitable, over 85,000 customers strong and growing, and what a ride it's been! In celebration of VR's success, I decided to write about 9 things I've learned leading a growing business that might help you ease over your own bumps, or flatten them! I'll start with the first five things in this post:

1. Hire a GREAT Lawyer - Luckily, this is one mistake I didn't make, but I've heard nightmare stories from other businesses that weren't as lucky as me. Make sure your business is set up properly. For instance I had no idea that I HAD to set up as a C-Corp. because one of my investors resides outside of the U.S. Make sure your i's are dotted on this one. If I was going to spend a bigger budget, this is where I'd spend. Also, consider giving a percentage of your company to your lawyer in exchange for big dollars. They may be even more attentive when it comes to doing work for your business.

2. Don't Hire Ahead of the Curve - I'm a firm believer that you and your teams should be at more than FULL CAPACITY before you hire. The last thing you want is your new employee to be "waiting around" for work. I once hired a salesperson before the product was ready. I'm sure that was a bit de-motivating to them since they had nothing to sell and I got angry with myself for wasting good money.

3. Get a GREAT Accountant - If your business isn't set up properly from an accounting perspective, you could end up paying for it years down the road. I learned the hard way when I thought my accountant had all the information necessary when it came to paying employee tax for the city of San Francisco. For years we just didn't pay and I never had a clue. When Mr. Auditor visited us one day it was very clear that our accountant had no idea this wasn't happening either, and he should have. A hit of $20,000 wasn't pretty for our business that year.

4. QUICK Decision Making - Sure we'd all love to know every piece of information on a subject before we make a decision around it, but then, no decisions would ever be made. I've had employees belabor every possible aspect of a decision, but in the end it wasted some serious time. Inform yourself as much as you think makes sense, and ask yourself one question: "What's the worse that could happen?" I'm a huge proponent of "doing something" and either being successful or cleaning up a mess and learning from it, versus "doing nothing" and learning nothing.

5. Get Rid of Bad Employees...FAST - There's something to be said for trying to help employees who aren't up to snuff, but at some point you have to let go. I've definitely been "scared" to fire people because in my mind they held some amount of proprietary information that would sink the company. But when they're gone an amazing thing happens...the business continues. It may be a bit slower for some amount of time, but it's amazing how things will seem to run. Find out what they know, make sure others can figure it out, and get rid of them. They're a poison to the rest of the employees, and you risk your team thinking less of you if you don't act.

6. Establish & Enforce Core Values - I just assumed that if we liked the people we were hiring, they were "just like us". They believed in the same things; respect for others, honesty, and passion for what we do. And that was true when we were just 10 people, but if you enforce this even when you are a ten-person company it gives your team something they can stand behind. Also make sure you call people out when they're violating these values.

7. EARLY Training - Even though you want your employees to jump in and work immediately, take a few deep breaths. They're not going to be successful without the right tools, I don't care if it's your receptionist in your dental office, an accounting manager, or your lead engineer. They need to understand what you stand for, what your business is all about, and how their specific job fits into the picture. At VerticalResponse we show people a video of me welcoming them to the company outlining who we are and what we stand for. Then each employee goes through a 13-step training on our product, and there’s a test at the end. Then they sit on the phones and listen to our amazing customer service team handle incoming inquiries so they know who our customers are and what types of issues they face. This has helped new employees tenfold get indoctrinated in our company and our products.

8. Don’t try To Be Everything to Everyone – It’s a fact, even great employees will leave your company. Most won't leave because of money, but they'll usually find something that pays them better. They may tell you it's because there was more money offered, but they wouldn't have been looking if they were happy in the first place. Some will leave because they don’t like their boss, some will leave because you simply can't provide a career path for them. You just have to wish them well and offer to be a reference, especially if he or she was a good employee. When you do have one of those angry employees with an ax to grind saying bad things about you or your company, take the high road. That sort of behavior by former employees only serves to make them look bad and we all know what a small world it is.

9. Love What You Do - You have to be passionate about what you do every day, since you're likely putting in 80 hours of work a week. Don't forget to take a break when you need one and don't feel guilty. There is a chance that not many people are working harder than you at your business. If the day comes that you dread going to work in the morning, and you have taken your vacations, maybe it's time to ask yourself if you should be replaced. You don't want your team feeling you've thrown in the towel.

It's been a great 9 years at VerticalResponse and I look forward to the challenges in every coming year, no matter what shape they may take. There have been a lot of mistakes along the way, but I think it's made me a better leader. We never stop learning!

http://www.inc.com/janine-popick/2010/07/9-lessons-from-9years-in-business.html

Best Advice from Warren Buffet

"The power of unconditional love. I mean, there is no power on earth like unconditional love. And I think that if you offered that to your child, I mean, you’re 90 percent of the way home. There may be days when you don’t feel like it — it’s not uncritical love; that’s a different animal — but to know you can always come back, that is huge in life. That takes you a long, long way. And I would say that every parent out there that can extend that to their child at an early age, it’s going to make for a better human being."

Carriage return

When creating long text strings in VBA and you use Chr(13) you will see that this "Carriage return Character" is represented by a small square in forms or on reports. To avoid this you should use the following structure.

& Chr(13) & Chr(10)

NOTE: They must be used in the displayed order.
13 is a Carriage return
10 is a Line Feed

Also you may use the following
vbCr is a Carriage Return
vbCrLf is a Carriage Return and Line Feed
vbLf is a Line Feed
vbNewLine Is a new Line

VBA Function to get text driving directions from XML web page

Reference Needed: Microsoft XML, V6.0

Function TextDirectionsXML(StartAddress As String, EndAddress As String) As String
On Error GoTo ErrMsg
dteStartTime = Now()
'populate addresses
strStartAddress = StartAddress
strEndAddress = EndAddress

' check google.com using xml
Dim xSite As XMLHTTP60
Set xSite = New XMLHTTP60
xSite.Open "GET", "http://maps.google.com/maps?q=from+" & strStartAddress & "+to+" & strEndAddress & "&output=kml", False
xSite.Send
Do Until xSite.readyState = 4
Loop

'MsgBox xSite.getAllResponseHeaders
'MsgBox xSite.getResponseHeader("Last-Modified")
'MsgBox "Status Text: " & xSite.statusText & vbCr & vbCr & "Status: " & xSite.Status
'MsgBox xSite.responseText


Dim xml_doc As New DOMDocument
Dim nde_Placemark As IXMLDOMNode
'Dim nde_Dividend As IXMLDOMNode
xml_doc.loadXML (xSite.responseText) ' response text is the xml file being returned

i = 1
DriveDirection = ""

'Looop through node
For Each nde_Placemark In xml_doc.selectNodes("//Placemark")

str_Phrase = nde_Placemark.childNodes(0).Text
str_DistanceTime = nde_Placemark.childNodes(1).Text
If str_Phrase = "Route" Then
str_Phrase = ""
If InStr(str_DistanceTime, "&") <> 0 Then
If InStr(str_DistanceTime, "hour") > 1 Then
'time is at least 1 hour
pos1 = InStr(str_DistanceTime, "about")
pos2 = InStr(str_DistanceTime, "hours")
intHours = Mid(str_DistanceTime, pos1 + 6, pos2 - pos1 - 7)
pos1 = InStr(str_DistanceTime, "hours")
pos2 = InStr(str_DistanceTime, "mins")
intMinutes = Mid(str_DistanceTime, pos1 + 6, pos2 - pos1 - 7)
IntTotalMinutes = intHours * 60 + intMinutes
Else
'time is less than 1 hour
intHours = 0
pos1 = InStr(str_DistanceTime, "about")
pos2 = InStr(str_DistanceTime, "mins")
intMinutes = Mid(str_DistanceTime, pos1 + 6, pos2 - pos1 - 7)
IntTotalMinutes = intHours * 60 + intMinutes
End If

intDistanceValue = Mid(str_DistanceTime, 11, InStr(str_DistanceTime, "&") - 11)
intDistanceUnit = Mid(str_DistanceTime, InStr(str_DistanceTime, ";") + 1, 2)
strTotalMiles = intDistanceValue & intDistanceUnit
intSpeed = (intDistanceValue / IntTotalMinutes) * 60
Else
MilesUnits = ""
End If

Else
If InStr(str_DistanceTime, "&") <> 0 Then
intDistanceValue = Mid(str_DistanceTime, 4, InStr(str_DistanceTime, "&") - 4)
intDistanceUnit = Mid(str_DistanceTime, InStr(str_DistanceTime, ";") + 1, Len(str_DistanceTime))
strTotalMiles = " (Go " & intDistanceValue & intDistanceUnit & ")"
Else
MilesUnits = ""
End If

End If

If i = 1 Then Str_AddressBeg = nde_Placemark.childNodes(2).Text

If str_Phrase <> "" Then
If Left(str_Phrase, 6) = "Arrive" Then
Str_AddressEnd = nde_Placemark.childNodes(1).Text
DriveDirection = DriveDirection & i & "." & str_Phrase & vbCrLf
Else
DriveDirection = DriveDirection & i & "." & str_Phrase & strTotalMiles & vbCrLf
End If

Else
DriveDirection = DriveDirection
End If

i = i + 1
Next

dteEndTime = Now()
dteRunTime = "(" & Format(dteEndTime - dteStartTime, "s") & " Second(s))"
Header = "Retreived: " & Format(Now(), "m/d/yy, h:mma/p")
TextDirectionsXML = DriveDirection & vbCrLf & _
"Distance: " & strTotalMiles & vbCrLf & _
"Travel Time: " & intHours & ":" & intMinutes & vbCrLf & _
"Speed: " & Format(intSpeed, "#,##0.0 mph") & vbCrLf & _
"Est Time To Deliver: " & Format(TimeValue(TimeSerial(0, i * 1 + (IntTotalMinutes * 2) + 15, 0)), "h:mm") & vbCrLf & _
Str_AddressBeg & vbCrLf & _
Str_AddressEnd & vbCrLf


' Header & " " & dteRunTime & vbCrLf & _
' "ettd: " & i * 1 + (IntTotalMinutes * 2) + 15


Exit Function
ErrMsg:

TextDirectionsXML = ""
Exit Function

End Function

http://www.tek-tips.com/viewthread.cfm?qid=1444524&page=8
http://groups.google.com/group/Google-Maps-API/msg/4dc2fad4f74e3314

Median Function for Access

Function DMedian(FieldName As String, _
TableName As String, _
Optional WhereClause As String = "" _
) As Single

Dim dbMedian As DAO.Database
Dim rsMedian As DAO.Recordset
Dim lngLoop As Long
Dim lngOffSet As Long
Dim lngRecCount As Long
Dim dblTemp1 As Double
Dim dblTemp2 As Double
Dim strSQL As String

Set dbMedian = CurrentDb()
strSQL = "SELECT [" & FieldName & _
"] FROM [" & TableName & "] "
' NOTE: To ignore nulls when calculating the median value, use
' the following 4 lines:
' strSQL = strSQL & "WHERE [" & FieldName & "] IS NOT NULL "
' If Len(WhereClause) > 0 Then
' strSQL = strSQL & "AND (" & WhereClause & ") "
' End If
' NOTE: The following 3 lines will include nulls. Remove them
' (and use the 4 lines above) if you want to ignore nulls.
If Len(WhereClause) > 0 Then
strSQL = strSQL & "WHERE " & WhereClause & " "
End If
strSQL = strSQL & "ORDER BY [" & FieldName & "]"
Set rsMedian = dbMedian.OpenRecordset(strSQL)
If rsMedian.EOF = False Then
rsMedian.MoveLast
lngRecCount = rsMedian.RecordCount
If lngRecCount Mod 2 <> 0 Then
lngOffSet = ((lngRecCount + 1) / 2) - 2
For lngLoop = 0 To lngOffSet
rsMedian.MovePrevious
Next lngLoop
DMedian = rsMedian(FieldName)
Else
lngOffSet = (lngRecCount / 2) - 2
For lngLoop = 0 To lngOffSet
rsMedian.MovePrevious
Next lngLoop
dblTemp1 = rsMedian(FieldName)
rsMedian.MovePrevious
dblTemp2 = rsMedian(FieldName)
DMedian = (dblTemp1 + dblTemp2) / 2
End If
End If

End_DMedian:
On Error Resume Next
rsMedian.Close
dbMedian.Close
Set dbMedian = Nothing
Exit Function

Err_DMedian:
Err.Raise Err.Number, "DMedian", Err.Description
Resume End_DMedian

End Function

Query to show Access Database Objects

SELECT IIf([ParentID]=-2147483647,'Form',IIf([ParentID]=-2147483646,'Module',IIf([ParentID]=-2147483645,'Report',IIf([ParentID]=251658241 And [Type]=4,'Table Linked',IIf([ParentID]=251658241 And [Type]=5 And [Flags]<>3,'Query',IIf([ParentID]=251658241 And [Type]=1 And [Flags]=0,'Table Local',IIf([ParentID]=251658241 And [Type]=6,'Table Direct',""))))))) AS ObjectType, MSysObjects.Name, MSysObjects.DateCreate, MSysObjects.DateUpdate, MSysObjects.Connect, MSysObjects.Database, InStr([Connect],"WSID") AS Expr1, Len([Connect]) AS Expr2, IIf(Len([Database])>2,"",Left(Right([Connect],Len([Connect])-InStr([Connect],"WSID")-4),InStr(Right([Connect],Len([Connect])-InStr([Connect],"WSID")-5),";"))) AS CreatedFromMachine, IIf(Len([Database])>2,"",Left(Right([Connect],Len([Connect])-InStr([Connect],"DATABASE=")-8),InStr(Right([Connect],Len([Connect])-InStr([Connect],"DATABASE=")-9),";"))) AS SQLDataBase

FROM MSysObjects

WHERE (((IIf([ParentID]=-2147483647,'Form',IIf([ParentID]=-2147483646,'Module',IIf([ParentID]=-2147483645,'Report',IIf([ParentID]=251658241 And [Type]=4,'Table Linked',IIf([ParentID]=251658241 And [Type]=5 And [Flags]<>3,'Query',IIf([ParentID]=251658241 And [Type]=1 And [Flags]=0,'Table Local',IIf([ParentID]=251658241 And [Type]=6,'Table Direct',""))))))))<>"") AND ((InStr([Connect],"WSID"))<>""))
ORDER BY IIf([ParentID]=-2147483647,'Form',IIf([ParentID]=-2147483646,'Module',IIf([ParentID]=-2147483645,'Report',IIf([ParentID]=251658241 And [Type]=4,'Table Linked',IIf([ParentID]=251658241 And [Type]=5 And [Flags]<>3,'Query',IIf([ParentID]=251658241 And [Type]=1 And [Flags]=0,'Table Local',IIf([ParentID]=251658241 And [Type]=6,'Table Direct',""))))))), MSysObjects.Name, MSysObjects.DateCreate;

Sort Data by Double Clicking a Control

Private Sub CUSTNAME_Label_DblClick(Cancel As Integer)
If Me.OrderBy = "CUSTNAME DESC" Then
Me.OrderBy = "CUSTNAME"
Me.OrderByOn = True
Else
Me.OrderBy = "CUSTNAME DESC"
Me.OrderByOn = True
End If
End Sub

Recordsets Collection and Recordset Object

The Recordset object is the primary object used to manipulate data in Access databases (and other databases as well, via ODBC connections). Although there is a Recordsets collection (the collection of all open Recordset objects in a database), it is not much use, except to list the open recordsets and their SQL statements, as in the following code sample:

Private Sub cmdListRecordsets_Click()    Dim dbs As Database   Dim rst As Recordset   Dim strDBName As String   Dim intCount As Integer   Dim strTable As String      strTable = "Orders"   strDBName = "D:\Documents\Northwind.mdb"   Set dbs = OpenDatabase(strDBName)   intCount = dbs.Recordsets.Count   Debug.Print intCount & _         " recordsets in current database (before opening a recordset)"   Set rst = dbs.OpenRecordset(strTable, dbOpenTable)   intCount = dbs.Recordsets.Count   Debug.Print intCount & _         " recordsets in current database (after opening a recordset)"      For Each rst In dbs.Recordsets      Debug.Print "Open recordset: " & rst.Name   Next rst   End Sub

A new Recordset object is automatically added to the Recordsets collection when you open the recordset, and it is automatically removed when you close it. Note that when you first count the recordsets in the preceding code, the count is 0; after setting a recordset variable, it is 1. The position of the Recordsets collection in the DAO object model is shown in Figure 8-1.

Figure 8-1. The Recordsets collection in the DAO object model

You can create as many recordset variables as you want, and different recordsets can access the same tables, queries, or fields without causing a problem. You can even open two recordsets from the same data source, and this is not a problem, so long as you refer to the recordsets by the variables used to assign them, not by their names.

TIP:

See the Name property section later in this chapter for a discussion of why it is unwise to use the Name property to reference a recordset.

There are five types of recordsets: Table-type, Dynaset, Snapshot, Forward-only, and Dynamic. (See the Type property section for a discussion of recordset types.) Each Recordset object contains a collection of Fields that represents the fields in the underlying table(s). You can list the field names and values, but you will just get the values in the current record, unless you first go to a particular record. For example, the following code moves to the last record in a recordset and lists the field names and values for that record:

Private Sub cmdListFields_Click()    Dim dbs As Database   Dim rst As Recordset   Dim strDBName As String   Dim strTable As String   Dim fld As Field      strTable = "Categories"   strDBName = "D:\Documents\Northwind.mdb"   Set dbs = OpenDatabase(strDBName)   Set rst = dbs.OpenRecordset(strTable, dbOpenTable)   With rst      .MoveLast      For Each fld In .Fields         Debug.Print fld.Name & " value: " & fld.Value      Next fld      .Close   End With   End Sub

Although you can reference a Recordset object in the Recordsets collection by any of the following syntax variants, it is advisable to use its variable instead to avoid possible ambiguity.

Recordsets(0)Recordsets("name")Recordsets![name]

The Recordsets collection has two properties and one method, which are shown in Table 8-1.

Table 8-1: Members of the Recordsets Collection

Type	Name	Description
Property	Count	Indicates the number of Recordset objects in the Recordsets collection
Property	Item	Retrieves a Recordset object either by its index in the collection or by its name
Method	Refresh	A nonfunctional method

The Recordset object has 32 properties (shown in Table 8-2) and 24 methods (listed in Table 8-3). It also has two collections: the Fields collection, a collection of the individual fields in the recordset; and the Properties collection, a collection of the individual properties supported by the Recordset object.

Table 8-2: Recordset Object Properties

Property	Description
AbsolutePosition	The relative position of the current record
BatchCollisionCount	Number of records that did not complete the last batch update
BatchCollisions	Array of bookmarks representing rows that had a collision during the last batch update
BatchSize	For batch updates, the number of statements to send back to the server in each batch
BOF	Flag indicating whether the record pointer is at the beginning of the file
Bookmark	Value that uniquely identifies a particular record in the recordset
Bookmarkable	Indicates whether a recordset supports bookmarks
CacheSize	Determines the number of records from an ODBC data source that will be cached locally
CacheStart	The bookmark of the first record to be cached locally
Connection	The Connection object that owns the recordset
DateCreated	Date and time the recordset was created
EditMode	Indicates the recordset's editing state
EOF	Flag indicating whether the record pointer is at the end of the file
Filter	Expression that filters records from the recordset
Index	Name of the current index
LastModified	Bookmark indicating the most recently modified record
LastUpdated	Date and time the recordset was last updated
LockEdits	The type of locking in effect when editing
Name	The name of the Recordset object
NoMatch	Flag indicating whether a search was successful
PercentPosition	Relative position of the current record in the recordset
RecordCount	Number of records in the recordset
RecordStatus	The update status of the current record in the next batch update
Restartable	Indicates whether a recordset supports the Requery method
Sort	Expression defining the sort order of records
StillExecuting	Indicates whether an asynchronous operation has finished executing
Transactions	Indicates whether the recordset supports transactions
Type	The recordset (or cursor) type
Updatable	Indicates whether the recordset can be updated
UpdateOptions	Indicates how the SQL WHERE clause is constructed for each record during a batch update and whether the update should use an UPDATE statement or a DELETE followed by an INSERT
ValidationRule	Defines a validation rule used to validate data as it is changed
ValidationText	Text displayed when a record fails to meet the validation criteria

Table 8-3: Recordset Object Methods

Method	Description
AddNew	Adds a new record to an updatable recordset
Cancel	Cancels execution of a pending asynchronous method call
CancelUpdate	Cancels any pending updates
Clone	Creates a duplicate Recordset object
Close	Closes the recordset
CopyQueryDef	Returns a copy of the QueryDef object originally used to create the recordset
Delete	Deletes the current record
Edit	Prepares a record for editing
FillCache	Fills all or part of a recordset's local cache
FindFirst	Finds the first record that meets designated criteria
FindLast	Finds the last record that meets designated criteria
FindNext	Finds the next record that meets designated criteria
FindPrevious	Finds the previous record that meets designated criteria
GetRows	Retrieves rows into a two-dimensional array
Move	Moves the record pointer either forward or backward
MoveFirst	Moves to the first record of the recordset
MoveLast	Moves to the last record of the recordset
MoveNext	Moves to the next record of the recordset
MovePrevious	Moves to the previous record of the recordset
NextRecordset	Retrieves the next set of records returned by a multipart SELECT query
OpenRecordset	Creates a new recordset
Requery	Reissues the query that created the recordset
Seek	Locates a record that meets the criteria based on the current index
Update	Saves changes to a record

Access to the Recordset Object

Creatable

No

Returned by

The OpenRecordset method of the Connection object
The Recordsets property of the Connection object
The OpenRecordset method of the Database object
The Recordsets property of the Database object
The Clone method of the Recordset object
The OpenRecordset method of the Recordset object
The OpenRecordset method of the TableDef object

Recordsets Collection Properties

Count

Data Type

Integer

Description

Indicates the number of recordsets in the Recordsets collection. See the code sample in the introduction to this chapter for an example of its usage. As far as I can see, there is little (if any) practical use for this property.

Item
Recordsets.Item(Index)

Argument	Data Type	Description
Index	Integer	The ordinal position of the Recordset object in the Recordsets collection, or a string containing the name of the Recordset object to be retrieved from the collection

Data Type

Recordset object

Description

Retrieves a particular Recordset object from the Recordsets collection. A Recordset object can be retrieved either based on its ordinal position in the collection or based on its name. (But see the Name section later in this chapter for the reasons why it is unwise to reference a recordset based on its name.) Mostly, recordsets are manipulated by means of the variable used to set them, since this is the best way to assure that you are working with the correct recordset.

Recordsets Collection Methods

Refresh

---

Recordsets.Refresh

Although the documentation lists Refresh as a method of the Recordsets object in Help and the Object Browser, actually this method is inapplicable to the Recordset object, since Refresh applies only to persistent objects. (Since recordsets exist only in memory, the Recordsets collection is not a collection of persistent objects.) Calling the method, however, does not generate an error; it simply has no effect.

Recordset Object Properties

Table 8-4 summarizes which properties apply to each type of Recordset object and whether the property setting is read/write (RW) or read-only (RO) for Jet ( J) and ODBCDirect (O) databases, or for both Jet and ODBCDirect workspaces ( JO). A blank cell indicates that the property does not apply to either type of workspace. In cases where the property is always read-only with a value of False, that is indicated by an F in the cell.

Table 8-4: Recordset Property Summary

Property	Table	Dynaset	Snapshot	Forward-Only	Dynamic
AbsolutePosition		RW	RW		RW
		JO	JO		O
BatchCollisionCount		RO	RO	RO	RO
		O	O	O	O
BatchCollisions		RO	RO	RO	RO
		O	O	O	O
BatchSize		RW	RW	RW	RW
		O	O	O	O
BOF	RO	RO	RO	RO	RO
	J	JO	JO	JO	O
Bookmark	RW	RW	RW		RW
	J	JO	JO		O
Bookmarkable	RO	RO	RO		RO
	J	JO	JO		O
CacheSize		RW/RO	RO		RO
		J/O	O		O
CacheStart		RW
		J
Connection		RW	RW	RW	RW
		O	O	O	O
DateCreated	RO
	J
EditMode	RO	RO	RO	RO	RO
	J	JO	JO	JO	O
EOF	RO	RO	RO	RO	RO
	J	JO	JO	JO	O
Filter		RW	RW	RW
		J	J	J
Index	RW
	J
LastModified	RO	RO	RO		RO
	J	JO	O[1]		O
LastUpdated	RO
	J
LockEdits	RW	RW/RO	RW/RO	RW	RO
	J	J/O	J/O	J	O
Name	RO	RO	RO	RO	RO
	J	JO	JO	JO	O
NoMatch	RO	RO	RO
	J	J	J
PercentPosition	RW	RW	RW		RW
	J	JO	JO		O
RecordCount	RO	RO	RO	RO	RO
	J	JO	JO	JO	O
RecordStatus		RO	RO	RO	RO
		O	O	O	O
Restartable	F	RO	RO	RO	RO
	J	JO	JO	JO	O
Sort		RW	RW
		J	J
StillExecuting		RO	RO	RO	RO
		O	O	O	O
Transactions	RO	RO	RO (F)	RO (F)
	J	J	J	J
Type	RO	RO	RO	RO	RO
	J	JO	JO	JO	O
Updatable	RO	RO	F/RO	F/RO	RO
	J	JO	J/Oa	J/Oa	O
UpdateOptions		RW	RW	RW	RW
		O	O	O	O
ValidationRule	RO	RO	RO	RO
	J	J	J	J
ValidationText	RO	RO	RO	RO
	J	J	J	J

AbsolutePosition

---

Data Type

Long

Description

This misleadingly named property sets or returns the relative record number of a recordset's current record. It is a zero-based number corresponding to the ordinal position of the current record in the recordset, ranging from zero to one less than the record count. If there is no current record (for example, for an empty recordset), AbsolutePosition returns -1.

TIP:

Despite the "Absolute" in the property name, this property is not stable and certainly is not a record number. It changes every time records are added to or deleted from a recordset. Use the Bookmark property to set a variable you can use to return to a particular record after moving the record pointer by a search or requerying.

VBA Code

This code displays the ordinal number of the record in a recordset while iterating through it:

Private Sub cmdAbsolutePosition_Click()    Dim dbs As Database   Dim strDBName As String   Dim rst As Recordset      strDBName = "D:\Documents\Northwind.mdb"   Set dbs = OpenDatabase(strDBName)   Set rst = dbs.OpenRecordset("Employees", dbOpenSnapshot)   With rst      .MoveFirst      Do While Not .EOF         Debug.Print !LastName & " record--No. " & .AbsolutePosition + 1         .MoveNext      Loop      .Close   End With   dbs.Close End Sub 

BatchCollisionCount

Data Type

Long

Description

For ODBCDirect workspaces only, this property returns the number of records that did not complete in the last batch update. It corresponds to the number of Bookmarks in the BatchCollisions property.

BatchCollisions

Data Type

Variant Array

Description

For ODBCDirect workspaces only, this property returns a variant containing an array of bookmarks, representing rows that had a collision during the last batch Update call. The number of elements in the array can be determined by retrieving the value of the BatchCollisionCount property.

BatchSize

---

Data Type

Long

Description

For ODBCDirect workspaces only, this property sets or returns the number of statements sent back to the server in each batch. The default value is 15. Setting BatchSize to 1 causes each statement to be sent separately; you might do this when working with those database servers that don't support batch updates.

BOF

---

Data Type

Boolean

Description

The BOF property (the name is derived from "Beginning of File") indicates that the current record position is before the first record in a recordset. It is useful for determining whether you have gone beyond the beginning of the records in a recordset when moving backward. In a recordset with no objects, this property is True. However, if you delete the last remaining record in a recordset, BOF may remain False until you attempt to reposition the record pointer. See Table 8-5 for a summary of what happens when you use the Move methods with different combinations of the BOF and EOF properties.

Table 8-5: The Move Methods with BOF and EOF

BOF/EOF	MoveFirst, MoveLast	MovePrevious, Move <>	Move 0	MoveNext, Move > 0
BOF=True, EOF=False	Allowed	Error	Error	Allowed
BOF=False, EOF=True	Allowed	Allowed	Error	Error
Both True	Error	Error	Error	Error
Both False	Allowed	Allowed	Allowed	Allowed

Table 8-6 shows what happens to the BOF and EOF properties after using Move methods that don't find a record.

Table 8-6: When Move Methods Don't Find a Record

	BOF	EOF
MoveFirst, MoveLast	True	True
Move 0	No change	No change
MovePrevious, Move <>	True	No change
MoveNext, Move > 0	No change	True

VBA Code

This code uses the BOF marker to prevent going beyond the first record when iterating backward through a recordset:

Private Sub cmdBOF_Click()    Dim dbs As Database   Dim rst As Recordset   Dim strDBName As String   Dim strTable As String   Dim fld As Field      strTable = "Categories"   strDBName = "D:\Documents\Northwind.mdb"   Set dbs = OpenDatabase(strDBName)   Set rst = dbs.OpenRecordset(strTable, dbOpenTable)   With rst      .MoveLast      Do While Not .BOF         Debug.Print !CategoryName         .MovePrevious      Loop      .Close   End With   End Sub

Bookmark

---

Data Type

Variant array of Byte data

Description

Uniquely identifies the current record in a recordset. By retrieving the value of a record's Bookmark property, you can later return the record pointer to that record. It is the closest thing Access has to a record number. You can use bookmarks on recordsets whose Bookmarkable property is True, which is always the case for recordsets based entirely on Jet tables. For recordsets based on other databases, Bookmarkable may not be True, in which case you can't use bookmarks.

VBA Code

See the code sample in the Requery section for an example of using the Bookmark property to return to the same record after requerying; the following code sample shows another way the Bookmark property can be useful in returning to the previous record after a failed search:

Private Sub cmdBookmark_Click()    Dim dbs As Database   Dim rst As Recordset   Dim strDBName As String   Dim strTable As String   Dim varBookmark As Variant      strTable = "Categories"   strDBName = "D:\Documents\Northwind.mdb"   Set dbs = OpenDatabase(strDBName)   Set rst = dbs.OpenRecordset(strTable, dbOpenTable)   With rst      If .Bookmarkable = False Then         MsgBox "This recordset is not bookmarkable -- exiting!"         Exit Sub      Else         MsgBox "This recordset is bookmarkable -- continuing!"      End If      varBookmark = .Bookmark      .Index = "PrimaryKey"      .Seek "=", 5      If .NoMatch Then .Bookmark = varBookmark      Debug.Print !CategoryName      .Close   End With End Sub

Bookmarkable

Data Type

Boolean

Description

The Bookmarkable property indicates whether you can use bookmarks in a recordset. If you are not sure that the tables underlying a recordset are all Jet tables, you can check this property before attempting to use bookmarks. See the Bookmark section for an example of its usage.

CacheSize

---

Data Type

Long

Description

Sets or returns the number of records retrieved from an ODBC data source that will be cached locally. The value must be between 5 and 1200, but it can't be greater than available memory permits. Setting CacheSize to 0 turns off caching. Using a cache can improve performance, since retrieving data from a local cache is faster than retrieving it from the server.

CacheStart

---

Data Type

String

Description

The CacheStart property (used in conjunction with CacheSize and FillCache) sets or returns a value that specifies the bookmark of the first record in a dynaset-type recordset containing the data to be locally cached from an ODBC data source in a Jet workspace.

Connection

---

Data Type

Connection Object

Description

Sets or returns the Connection object that owns the recordset, for ODBCDirect workspaces only.

DateCreated

Data Type

Date/Time

Description

Returns the date and time the recordset was created. Note that this is usually different than the date the underlying table was created.

VBA Code

Private Sub cmdDateCreated_Click()    Dim dbs As Database   Dim rst As Recordset   Dim strDBName As String   Dim strTable As String      strTable = "Categories"   strDBName = "D:\Documents\Northwind.mdb"   Set dbs = OpenDatabase(strDBName)   Set rst = dbs.OpenRecordset(strTable, dbOpenTable)   Debug.Print rst.Name & " recordset created on " & rst.DateCreated   End Sub

EditMode

Data Type

Long

Description

Indicates the state of editing, as listed in Table 8-7.

Table 8-7: The EditMode Property Settings

Named Constant	Value	Description
dbEditNone	0	No editing operation is in progress.
dbEditInProgress	1	The Edit method has been invoked, and the current record is in the copy buffer.
dbEditAdd	2	The AddNew method has been invoked, and the current record in the copy buffer is a new record that hasn't been saved in the database.

The value of the EditMode property can be useful in determining whether you should use the Update or CancelUpdate method when editing is interrupted. The following code sample shows the EditMode value for various stages of editing records.

VBA Code

Private Sub cmdEditMode_Click()    Dim dbs As Database   Dim rst As Recordset   Dim strDBName As String   Dim strTable As String      strTable = "Categories"   strDBName = "D:\Documents\Northwind.mdb"   Set dbs = OpenDatabase(strDBName)   Set rst = dbs.OpenRecordset(strTable, dbOpenTable)      With rst      .Move 3      Debug.Print "EditMode before editing: " & .EditMode      .Edit      Debug.Print "EditMode after Edit : " & .EditMode      !Description = "New description of this category"      .Update      Debug.Print "EditMode after updating: " & .EditMode      .AddNew      Debug.Print "EditMode after AddNew: " & .EditMode      .CancelUpdate      Debug.Print "EditMode after canceling editing: " & .EditMode      .Close   End With End Sub 

EOF

Data Type

Boolean

Description

The EOF property (derived from "End of File") indicates that the current record position is after the last record in a recordset. It is useful for determining whether you have gone beyond the end of the records in a recordset when moving forward. In a recordset with no objects, this property is True. However, if you delete the last remaining record in a recordset, EOF may remain False until you attempt to reposition the record. See Table 8-5 in the BOF section for a summary of what happens when you use the Move methods with different combinations of the BOF and EOF properties, and Table 8-6 for a listing of what happens to the BOF and EOF properties after using Move methods that don't find a record.

VBA Code

This code uses the EOF marker to prevent going beyond the last record when iterating through a recordset:

Private Sub cmdEOF_Click()    Dim dbs As Database   Dim rst As Recordset   Dim strDBName As String   Dim strTable As String   Dim fld As Field      strTable = "Categories"   strDBName = "D:\Documents\Northwind.mdb"   Set dbs = OpenDatabase(strDBName)   Set rst = dbs.OpenRecordset(strTable, dbOpenTable)   With rst      Do While Not .EOF         Debug.Print !CategoryName         .MoveNext      Loop      .Close   End With   End Sub

Filter

---

Data Type

String

Description

Sets or returns a value that filters the records in a recordset (for Jet workspaces only). Basically, it is the WHERE clause of a SQL statement without the word WHERE. You can use Filter with dynaset-, snapshot-, or forward-only-type recordsets.

TIP:

After using the Filter property with a recordset, you don't see the results of filtering immediately--you must open another recordset from the filtered recordset to see the results.

VBA Code

This code sample illustrates using the Filter property to filter records by country, opening a second recordset of UK employees based on the original, unfiltered recordset:

Private Sub cmdFilter_Click()    Dim dbs As Database   Dim strDBName As String   Dim rstEmployees As Recordset   Dim rstUKEmployees As Recordset   Dim strSearch As String      strDBName = "D:\Documents\Northwind.mdb"   Set dbs = OpenDatabase(strDBName)   Set rstEmployees = dbs.OpenRecordset("Employees", dbOpenDynaset)      'Create a filtered recordset based on the first recordset   rstEmployees.Filter = "Country = 'UK'"   Set rstUKEmployees = rstEmployees.OpenRecordset()    With rstUKEmployees      Debug.Print vbCrLf & "Filtered recordset:"      Do While Not .EOF         Debug.Print "Name: " & !LastName & ", country: " & !Country         .MoveNext      Loop   End With End Sub

TIP:

Using the Filter property on a recordset is generally less efficient than just applying a filter to a recordset and opening it in one step, using a SQL statement with a WHERE clause.

Index

---

Data Type

String

Description

Sets or returns the name of the index to use for a table-type recordset in a Jet workspace. It must be the name of an existing index in the Indexes collection of the TableDef object that is the data source of the Recordset object. The Index property is used with the Seek method for locating records in an indexed recordset. See the Seek section later in this chapter for an example of usage.

LastModified

Data Type

Variant array of Byte data

Description

Returns a bookmark indicating which record in a recordset was most recently added or modified. It applies to table-type or dynaset-type recordsets only. The primary use of this property is to return to the record that was most recently modified in code by setting the Bookmark property equal to LastModified.

TIP:

The value of LastModified only reflects changes made to the recordset itself; if a record was changed in the interface or directly in the table, this change is not reflected in the LastModified property.

VBA Code

This code loops through tblCustomers (a copy of the Northwind Customers table), modifying records that meet a criterion, and then returns to the last modified record. I'm using the RecordCount property (-1) to avoid being at the EOF marker after looping; otherwise, there would be an error on the first Debug.Print line:

Private Sub cmdLastModified_Click()    Dim dbs As Database   Dim rst As Recordset   Dim strTable As String   Dim intCount As Integer   Dim i As Integer      strTable = "tblCustomers"   Set dbs = CurrentDb   Set rst = dbs.OpenRecordset(strTable, dbOpenTable)   intCount = rst.RecordCount - 1      With rst      For i = 1 To intCount         If !Country = "UK" Then            .Edit            !Country = "United Kingdom"            .Update            Debug.Print "Changed " & !CompanyName & " record"         End If         .MoveNext      Next i            Debug.Print "After looping, at " & !CompanyName & " record"      'Go to most recently modified record      .Bookmark = .LastModified      Debug.Print "Last record modified: " & !CompanyName & " record"      .Close   End With End Sub

LastUpdated

Data Type

Date/Time

Description

Returns the date and time the recordset was last updated--in other words, when the data in the base table(s) was last changed. The changes can be made either in the interface or in code (unlike the LastModified property).

VBA Code

Private Sub cmdLastUpdated_Click()    Dim dbs As Database   Dim rst As Recordset   Dim strDBName As String   Dim strTable As String      strTable = "Categories"   strDBName = "D:\Documents\Northwind.mdb"   Set dbs = OpenDatabase(strDBName)   Set rst = dbs.OpenRecordset(strTable, dbOpenTable)   Debug.Print "Date Created: " & rst.DateCreated   Debug.Print "Last Updated: " & rst.LastUpdated End Sub

LockEdits

---

Data Type

Boolean

Description

For updatable recordsets the LockEdits property sets or returns a value indicating the type of locking in effect while editing, as shown in Table 8-8. Pessimistic locking (True) means that the page containing the record being edited is unavailable to other users until you are through editing and use the Update method to save the record. Optimistic locking (False) means that other users can access the same record you are working on, except just briefly while you are actually updating the record. Optimistic locking is more risky (two users can simultaneously change a record), but pessimistic locking may cause delays while records are unnecessarily locked.

Table 8-8: The LockEdits Values/Settings

Named Constant	Value	Description
True	-1	(Default) Pessimistic locking is in effect. The 2K page containing the record you're editing is locked as soon as you call the Edit method.
False	0	Optimistic locking is in effect for editing. The 2K page containing the record is not locked until the Update method is executed.

TIP:

The LockEdits value can be preset by setting the lockedit argument of the OpenRecordset method; setting the lockedit argument to dbPessimistic sets the LockEdits property to True, and setting it to any other value sets LockEdits to False. When working with ODBC data sources, LockEdits is always set to False, allowing only optimistic locking.

Name

Data Type

String

Description

For recordsets, the Name property is either the name of the underlying table or query, or, if the recordset is based on a SQL statement, the first 256 characters of the SQL statement. This makes the Name property unreliable as a means of referencing a particular recordset. To reference a recordset, just use the variable it was set with.

VBA Code

The code lists the names of several recordsets created based on a table, a query, and a SQL statement:

Private Sub cmdName_Click()    Dim dbs As Database   Dim rstTable As Recordset   Dim rstQuery As Recordset   Dim rstSQL As Recordset   Dim rst As Recordset   Dim strDBName As String   Dim intCount As Integer   Dim strTable As String   Dim strQuery As String   Dim strSQL As String    strDBName = "D:\Documents\Northwind.mdb"   Set dbs = OpenDatabase(strDBName)   strTable = "Orders"   strQuery = "Ten Most Expensive Products"   strSQL = "SELECT * FROM Customers"   Set rstTable = dbs.OpenRecordset(strTable, dbOpenTable)   Set rstSQL = dbs.OpenRecordset(strSQL, dbOpenSnapshot)   Set rstQuery = dbs.OpenRecordset(strQuery, dbOpenSnapshot)      For Each rst In dbs.Recordsets      Debug.Print rst.Name   Next rst End Sub

NoMatch

Data Type

Boolean

Description

Indicates whether a search was successful. It applies to searches done with the Seek method or one of the Find methods.

If a search is unsuccessful (NoMatch = True), the current record will no longer be valid. To avoid problems, save the record's bookmark to a variable so you can return to it after an unsuccessful search, as in the code sample in the Seek section.

PercentPosition

---

Data Type

Single

Description

Sets or returns a value between 0 and 100, representing the position of the current record in a recordset. For dynaset- or snapshot-type recordsets, move to the last record before using this method to ensure an accurate record count. The following code prints the percent position to the Debug window after each successful find of a record meeting a search criterion.

TIP:

You can use Percent Position with a scroll bar control on a form or text box to indicate the position of the current record in a recordset.

VBA Code

Private Sub cmdPercentPosition_Click()    Dim dbs As Database   Dim strDBName As String   Dim rst As Recordset   Dim strSearch As String      strDBName = "D:\Documents\Northwind.mdb"   Set dbs = OpenDatabase(strDBName)   Set rst = dbs.OpenRecordset("Employees", dbOpenSnapshot)   strSearch = "[Title] = 'Sales Rep'"      'MoveLast to ensure an accurate count of records.   With rst      .MoveLast      .MoveFirst      .FindFirst strSearch      Debug.Print !LastName & " record -- " & .PercentPosition & "%"      .FindNext strSearch      Debug.Print !LastName & " record -- " & .PercentPosition & "%"      .Close   End With   dbs.Close End Sub

RecordCount

Data Type

Long

Description

Returns the number of records in a recordset. In case of dynaset-, snapshot-, or forward-only-type recordsets, you need to access all the records in the recordset before getting an accurate count of the records. See the following code sample for an example of usage of RecordCount for this purpose. This is not necessary for table-type recordsets.

VBA Code

Private Sub cmdRecordCount_Click()    Dim dbs As Database   Dim strDBName As String   Dim rst As Recordset   Dim intCount As Integer      strDBName = "D:\Documents\Northwind.mdb"   Set dbs = OpenDatabase(strDBName)   Set rst = dbs.OpenRecordset("Employees", dbOpenSnapshot)      Debug.Print "Record count before traversing recordset: " & _               rst.RecordCount      'MoveLast to ensure an accurate count of records.   rst.MoveLast      Debug.Print "Record count after traversing recordset: " & _               rst.RecordCount   rst.Close   dbs.Close End Sub

RecordStatus

Data Type

Long

Description

Indicates the update status of the current record, if it is part of a batch update (for ODBCDirect workspaces only). The value returned indicates whether (and how) the current record will be involved in the next optimistic batch update. See Table 8-9 for a listing of the constants that may be returned.

Table 8-9: The RecordStatus Return Value Intrinsic Constants

Named Constant	Value	Description
dbRecordUnmodified	0	(Default) The record has not been modified or has been updated successfully.
dbRecordModified	1	The record has been modified and not updated in the database.
dbRecordNew	2	The record has been inserted locally with the AddNew method, but not yet inserted into the database.
dbRecordDeleted	3	The record has been deleted locally, but not yet deleted in the database.
dbRecordDBDeleted	4	The record has been deleted locally and in the database.

Restartable

Data Type

Boolean

Description

Indicates whether a recordset supports the Requery method. If the value is True, Requery can be used to re-execute the query on which the recordset is based; if it is False, the query can't be re-executed.

VBA Code

The following code opens three different types of recordsets, examines the Restartable property of each, and requeries those that are restartable:

Private Sub cmdRestartable_Click()    Dim dbs As Database   Dim rstTable As Recordset   Dim rstQuery As Recordset   Dim rstSQL As Recordset   Dim rst As Recordset   Dim strDBName As String   Dim intCount As Integer   Dim strTable As String   Dim strQuery As String   Dim strSQL As String    strDBName = "D:\Documents\Northwind.mdb"   Set dbs = OpenDatabase(strDBName)   strTable = "Orders"   strQuery = "Ten Most Expensive Products"   strSQL = "SELECT * FROM Customers"   Set rstTable = dbs.OpenRecordset(strTable, dbOpenTable)   Set rstSQL = dbs.OpenRecordset(strSQL, dbOpenSnapshot)   Set rstQuery = dbs.OpenRecordset(strQuery, dbOpenSnapshot)      'Determine whether each recordset is restartable,   'and requery it if so.   For Each rst In dbs.Recordsets      Debug.Print rst.Name & " restartable? " & rst.Restartable      If rst.Restartable = True Then rst.Requery   Next rst   End Sub

Sort

---

Data Type

String

Description

Sets or returns the sort order for records in a recordset (for Jet workspaces only). Basically, it is the ORDER BY clause of a SQL statement without the phrase ORDER BY. You can use Sort with dynaset- and snapshot-type recordsets only. As with the Filter property, the Sort property only takes effect when a new recordset is created from the sorted recordset. The Sort property overrides any sort order that might be specified for a QueryDef on which a recordset is based.

TIP:

Using the Sort property on a recordset is generally less efficient than just applying a sort order to a recordset and opening it in one step, using a SQL statement with an ORDER BY clause.

VBA Code

The following code applies a Sort order to a recordset and then opens a second recordset based on the sorted original recordset:

Private Sub cmdSort_Click()    Dim dbs As Database   Dim strDBName As String   Dim rst As Recordset   Dim rstSort As Recordset      strDBName = "D:\Documents\Northwind.mdb"   Set dbs = OpenDatabase(strDBName)   Set rst = dbs.OpenRecordset("Employees", dbOpenDynaset)   rst.Sort = "Region"   Set rstSort = rst.OpenRecordset      With rstSort       Do Until .EOF           Debug.Print "State: " & !Region & " for "; !LastName           .MoveNext       Loop   End With End Sub

StillExecuting

Data Type

Boolean

Description

For ODBCDirect workspaces only, indicates whether an asynchronous operation (one called with the dbRunAsync option) has finished executing. The return value is True if the query is still executing and False if it has finished executing. The Cancel method can be called to cancel execution if the value is True.

Transactions

Data Type

Boolean

Description

This property is True if the recordset supports transactions, and False if it does not. For ODBC workspaces the Transactions property indicates whether the ODBC driver supports transactions. The property can be used for dynaset- or table-type recordsets in Jet workspaces; for snapshot- and forward-only-type recordsets in Jet workspaces, it is always False. For dynaset- or table-type recordsets in Jet workspaces, the Transactions property is always True, indicating that you can use transactions.

TIP:

You should always check the Transactions property and make sure it returns True before working with transactions using the BeginTrans, CommitTrans, and Rollback methods.

VBA Code

This code creates a recordset from the QueryDefs in Northwind and lists the Transactions value for each one:

Private Sub cmdTransactions_Click()    Dim dbs As Database   Dim strDBName As String   Dim qdf As QueryDef   Dim rst As Recordset      strDBName = "D:\Documents\Northwind.mdb"   Set dbs = OpenDatabase(strDBName)      Debug.Print "QueryDefs in " & dbs.Name   For Each qdf In dbs.QueryDefs      On Error Resume Next      Set rst = dbs.OpenRecordset(qdf.Name)      Debug.Print "Recordset name and type: " & rst.Name & vbTab & _                  rst.Type      Debug.Print "Transactions possible?: " & rst.Transactions   Next qdf   dbs.Close  End Sub

Type

Data Type

Integer

Description

Indicates the recordset type of a Recordset object. The possible values are listed in Table 8-10.

Table 8-10: The Type Setting Return Values Intrinsic Constants

Named Constant	Value	Description
dbOpenTable	1	Table ( Jet workspaces only)
dbOpenDynamic	16	Dynamic (ODBC workspaces only)
dbOpenDynaset	2	Dynaset
dbOpenSnapshot	4	Snapshot
dbOpenForwardOnly	96	Forward-only

Using the Type property on recordsets can be useful when you don't know what type the recordset is. Note that the recordset type is not the same as the QueryDef type. There are many more QueryDef type constants than Recordset constants, and the available constants are different for the two types of objects.

VBA Code

The following code sample lists the recordset type of recordsets based on all the queries in Northwind:

Private Sub cmdType_Click()    Dim dbs As Database   Dim strDBName As String   Dim qdf As QueryDef   Dim rst As Recordset      strDBName = "D:\Documents\Northwind.mdb"   Set dbs = OpenDatabase(strDBName)      Debug.Print "QueryDefs in " & dbs.Name   For Each qdf In dbs.QueryDefs      Debug.Print "Query name and type: " & qdf.Name & vbTab & qdf.Type      Set rst = dbs.OpenRecordset(qdf.Name)      Debug.Print "Recordset name and type: " & rst.Name & vbTab & _                  rst.Type   Next qdf   dbs.Close End Sub

Updatable

Data Type

Boolean

Description

Indicates whether a recordset can be changed or updated. If it is True, the recordset can be updated; if it is False, it can't be updated.

VBA Code

This code creates recordsets from all the Northwind queries and reports on whether they are updatable:

Private Sub cmdUpdatable_Click()    Dim dbs As Database   Dim strDBName As String   Dim qdf As QueryDef   Dim rst As Recordset      strDBName = "D:\Documents\Northwind.mdb"   Set dbs = OpenDatabase(strDBName)      Debug.Print "QueryDefs in " & dbs.Name   For Each qdf In dbs.QueryDefs      On Error Resume Next      Set rst = dbs.OpenRecordset(qdf.Name)      Debug.Print "Recordset name and type: " & rst.Name & vbTab & _                  rst.Type      Debug.Print "Recordset updatable?: " & rst.Updatable   Next qdf   dbs.Close   End Sub

UpdateOptions

---

Data Type

Long

Description

Indicates how the WHERE clause is constructed for each record during a batch update and whether the update should use an UPDATE statement or a DELETE followed by an INSERT (for ODBCDirect workspaces only). The UpdateOptions value can be any of the constants in Table 8-11.

Table 8-11: The UpdateOptions Return Value/Settings Intrinsic Constants

Named Constant	Value	Description
dbCriteriaKey	1	(Default) Uses just the key column(s) in the where clause.
dbCriteriaModValues	2	Uses the key column(s) and all updated columns in the where clause.
dbCriteriaAllCols	4	Uses the key column(s) and all the columns in the where clause.
dbCriteriaTimeStamp	8	Uses just the timestamp column if available (will generate a run-time error if no timestamp column is in the result set).
dbCriteriaDeleteInsert	16	Uses a set of DELETE and INSERT statements for each modified row.
dbCriteriaUpdate	32	(Default) Uses an UPDATE statement for each modified row.

ValidationRule

Data Type

String

Description

Returns a value used to validate data as it is being changed or added to a field in a recordset's underlying data source table. It only applies to Jet workspaces. The ValidationRule phrase describes a comparison in the form of a SQL WHERE clause without the WHERE keyword. If the data does not meet the validation criteria, a trappable run-time error is generated, and the error message contains the text of the ValidationText property, if specified, or else the text of the expression specified by the ValidationRule property. See the ValidationRule section in Chapter 10, TableDefs Collection and TableDef Object, for more information on this property.

TIP:

ValidationRule comparison strings are limited to referencing the field; they can't contain references to user-defined functions or queries.

VBA Code

This code lists the fields in the Northwind Customers table and their validation rules and validation text, if any:

Private Sub cmdValidationRule_Click()      Dim dbs As Database   Dim rst As Recordset   Dim strDBName As String   Dim strTable As String   Dim fld As Field      strTable = "Employees"   strDBName = "D:\Documents\Northwind.mdb"   Set dbs = OpenDatabase(strDBName)   Set rst = dbs.OpenRecordset(strTable, dbOpenTable)   With rst      .MoveLast      For Each fld In .Fields         Debug.Print fld.Name         If fld.ValidationRule <> "" Then            Debug.Print "Validation Rule: " & fld.ValidationRule            Debug.Print "Validation Text: " & fld.ValidationText         End If      Next fld      .Close   End With End Sub

ValidationText

Data Type

String

Description

The ValidationText property returns a value specifying the text of the message that appears when data for a field fails the validation rule specified by the ValidationRule property. It applies only to Jet workspaces. See the ValidationText section in Chapter 10 for more information on this property. See the code sample in the ValidationRule section for an example of usage of this property.

Recordset Object Methods

Table 8-12 summarizes the Recordset type supported by each method and whether it applies to Jet ( J) or ODBCDirect (O) workspaces, or both ( JO). A blank cell indicates that the property does not apply to either type of workspace.

Table 8-12: Recordset Method Summary

Method	Table	Dynaset	Snapshot	Forward-Only	Dynamic
AddNew	J	JO	O[2]	O	O
Cancel		O	O	O	O
CancelUpdate	J	JO	Oa	O	O
Clone	J	J	J
Close	J	JO	JO	JO	O
CopyQueryDef		J	J	J
Delete	J	JO	Oa	O	O
Edit	J	JO	Oa	O	O
FillCache		J
FindFirst		J	J
FindLast		J	J
FindNext		J	J
FindPrevious		J	J
GetRows	J	JO	JO	JO	O
Move	J	JO	JO	[3]	O
MoveFirst	J	JO	JO		O
MoveLast	J	JO	JO		O
MoveNext	J	JO	JO	JO	O
MovePrevious	J	JO	JO		O
NextRecordset		O	O	O	O
OpenRecordset	J	J	J
Requery		JO	JO	JO	O
Seek	J
Update	J	JO	Oa	O	O

The recordset types and their uses are listed in Table 8-13.

Table 8-13: Recordset Types and Their Uses

Recordset Type	Usage
Table	Represents a single base table. Supports the AddNew, Delete, and Seek methods, but not the Find* methods. ( Jet only.)
Dynamic	Represents one or more base tables. Supports the AddNew and Delete methods, but not the Find* or Seek methods.
Dynaset	Represents a table or an updatable query. Supports the AddNew, Delete, and Find* methods, but not the Seek method.
Snapshot	A read-only recordset; useful for finding data or printing. Does not allow updating, except in the case of an updatable Snapshot in an ODBCDirect workspace. Supports the Find* methods.
Forward-only	Similar to a snapshot, but only allows forward movement. Useful when you only need to make a single pass through a recordset. Does not support the Find* methods.

AddNew

---

recordset.AddNew

Adds a new record to an updatable recordset (table-type or dynaset recordsets only). For dynasets the new records are added at the end of the recordset. For indexed dynasets, the new record is placed in indexed order; if the dynaset is not indexed, the new record is added to the end of the recordset.

TIP:

Don't confuse AddNew and Append. The DAO AddNew method is the equivalent of Append in other database languages, such as dBASE, while the Append method in DAO is used to add new members to collections.

After adding a new record with the AddNew method, you need to use the Update method to save the new record, as in the following code sample. If you omit the Update, you won't get a warning, and the new record will be lost when you move to another record or close the recordset.

Note that in VBA code, you should use the dot (.) operator for recordset methods and properties, and the bang (!) operator for fields. In VBS code use the dot operator for methods, properties, and fields.

TIP:

If you get an "Item not found in this collection" error message when updating a recordset, it is probably the result of a misspelled field name (fields are members of the Fields collection within the recordset).

VBA Code

This code first lists the categories in the Northwind Categories table, then adds a new record, then lists the categories again, showing the newly added one:

Private Sub cmdAddNew_Click()    Dim dbs As Database   Dim rst As Recordset   Dim strDBName As String   Dim strTable As String    strTable = "Categories"   strDBName = "D:\Documents\Northwind.mdb"   Set dbs = OpenDatabase(strDBName)   Set rst = dbs.OpenRecordset(strTable, dbOpenTable)    'List categories before adding new record   Debug.Print "Categories before adding new record:" & vbCrLf   rst.MoveFirst   Do Until rst.EOF      Debug.Print rst!CategoryName      rst.MoveNext   Loop      'Add new category   With rst      .AddNew      !CategoryName = "Dried Foods"      !Description = _         "Freeze-dried and sun-dried fruits, vegetables, and meats"      .Update   End With      'List categories after adding new record   Debug.Print vbCrLf & "Categories after adding new record:" & vbCrLf   rst.MoveFirst   Do Until rst.EOF      Debug.Print rst!CategoryName      rst.MoveNext   Loop   rst.Close End Sub

Cancel

---

recordset.Cancel

Cancels execution of a pending asynchronous method call. It only applies to recordsets in ODBCDirect workspaces since it requires use of the dbRunAsync value for the MoveLast method's Options argument. See the StillExecuting section for an example that uses this method.

CancelUpdate

---

recordset.CancelUpdate updatetype

Argument	Data Type	Description
recordset	Recordset object	The Recordset object for which you are canceling pending updates
updatetype	Integer	A named constant or Integer value (see Table 8-14)

Cancels any pending updates for a Recordset object, such as would result from use of the Edit or AddNew methods. Before using the CancelUpdate method, you should check the EditMode property of the recordset to find out if there is a pending operation that can be canceled.

Table 8-14: The UpdateType Intrinsic Constants

Named Constant	Value	Description
dbUpdateRegular	1	(Default) Cancels pending changes that aren't cached
dbUpdateBatch	4	Cancels pending changes in the update cache

TIP:

The type argument settings in Table 8-14 can only be used if batch updating is enabled. In an ODBCDirect workspace (only), this is done by setting the DefaultCursorDriver property to dbUseClientBatchCursor when the connection is opened, and the recordset must be opened using OpenRecordset with the locktype argument set to dbOptimisticBatch .

VBA Code

This code illustrates the use of CancelUpdate to allow a user to confirm adding a new record to the Categories table in the Northwind database:

Private Sub cmdCancelUpdate_Click()    Dim dbs As Database   Dim strDBName As String   Dim rst As Recordset   Dim intReturn As Integer   Dim strCategory As String   Dim strDescription As String      strDBName = "D:\Documents\Northwind.mdb"   Set dbs = OpenDatabase(strDBName)   Set rst = dbs.OpenRecordset("Categories")   With rst      .AddNew      strCategory = "Test"      strDescription = "Test new food category"      !CategoryName = strCategory      !Description = strDescription      intReturn = MsgBox("Add " & strCategory & " -- " & _         strDescription & " as a food category?", vbYesNo)      If intReturn = VbYes Then         .Update         MsgBox strCategory & " -- " & strDescription & _            " added as a food category"      Else         .CancelUpdate         MsgBox strCategory & " -- " & strDescription & _            " not added as a food category"      End If   End With End Sub

Clone

---

Set duplicate = original.Clone

Argument	Data Type	Description
duplicate	Recordset object	The duplicate Recordset object being created
original	Recordset object	The original Recordset object being duplicated

Creates a duplicate Recordset object that references the original Recordset object. The original and duplicate recordsets can have different current records. (Note, though, that after the cloning operation, the duplicate recordset initially has no current record.) Using the Clone method allows you to share bookmarks between Recordset objects, since their bookmarks are interchangeable.

VBA Code

This code sets up a recordset based on the Northwind Categories table, then clones it, and moves to different records in the original and duplicate recordsets:

Private Sub cmdClone_Click()    Dim dbs As Database   Dim strDBName As String   Dim rstO As Recordset   Dim rstD As Recordset   Dim strSearch As String   Dim strText As String      strDBName = "D:\Documents\Northwind.mdb"   Set dbs = OpenDatabase(strDBName)   Set rstO = dbs.OpenRecordset("Categories", dbOpenDynaset)   Set rstD = rstO.Clone   strText = "Dried Food"   strSearch = "[CategoryID] = 2"   rstO.FindFirst strSearch   strSearch = "[CategoryID] = 8"   rstD.FindFirst strSearch    'Report on where pointer is in the two recordsets.   Debug.Print "At "; rstO!CategoryName & " record in original recordset"   Debug.Print "At "; rstD!CategoryName & _               " record in duplicate recordset"   rstO.Close   rstD.Close   dbs.Close End Sub

Close

---

recordset.Close

Closes a recordset. You should always use this method to close a recordset before closing a database, because otherwise your pending edits and updates will be canceled.

TIP:

If you try to close a recordset that has already been closed, run-time error 3420, "Object invalid or no longer set," occurs.

See the code sample in the Clone section for an example of usage.

CopyQueryDef

---

Set querydef = recordset.CopyQueryDef

Argument	Data Type	Description
querydef	QueryDef object	The QueryDef object you want to create
recordset	Recordset object	The Recordset object you are creating

Returns a QueryDef object that is a copy of the QueryDef object originally used to create the Recordset object. A recordset must be created using the OpenRecordset method before using the CopyQueryDef method. This method can only be used in Jet workspaces. CopyQueryDef can be useful when you need to recreate a QueryDef from a recordset passed to a procedure.

WARNING:

An error occurs if you use this method on a recordset that was not based on a QueryDef.

VBA Code

The cmdCopyQueryDef_Click event procedure calls the CreateRecordset function, which sets a recordset variable, rst. (Note that the rst recordset variable is declared in the Declarations section of the module to make it public in scope.) When control returns to the event procedure, the CopyQueryDef method is used to recreate the QueryDef. The contents of one of the QueryDef's fields are then listed to the Debug window:

Private Function CreateRecordset()    Dim dbs As Database   Dim strDBName As String   Dim qdf As QueryDef      strDBName = "D:\Documents\Northwind.mdb"   Set dbs = OpenDatabase(strDBName)   Set qdf = dbs.QueryDefs("Sales by Category")   Set rst = qdf.OpenRecordset   End Function Private Sub cmdCopyQueryDef_Click()    Dim qdf As QueryDef   Call CreateRecordset   Set qdf = rst.CopyQueryDef      With rst      Do While Not .EOF         Debug.Print !CategoryName         .MoveNext      Loop      .Close   End With   End Sub

Delete

---

recordset.Delete

Deletes the current record from an updatable recordset. If the deleted record is in the primary table in a relationship set to permit cascading deletes, one or more records in the related table may also be deleted. The deleted record remains current (although it can't be edited or used) until you move to another record. It is not necessary to use Update after calling the Delete method.

TIP:

If you want to be able to undo deletions, you can use transactions and the Rollback method. Transactions are covered in Chapter 4, Workspaces Collection and Workspace Object .

VBA Code

This code goes to the last record in a recordset and deletes it:

Private Sub cmdDelete_Click()    Dim dbs As Database   Dim rst As Recordset   Dim strDBName As String   Dim strTable As String   Dim intCount As Integer      strTable = "Categories"   strDBName = "D:\Documents\Northwind.mdb"   Set dbs = OpenDatabase(strDBName)   Set rst = dbs.OpenRecordset(strTable, dbOpenTable)   intCount = rst.RecordCount   Debug.Print intCount & " records in recordset"      With rst      .MoveLast      .Delete      intCount = .RecordCount      .Close   End With         Debug.Print intCount & " records in recordset (after delete)"   End Sub

Edit

---

recordset.Edit

The Edit method prepares a record in an updatable recordset for editing by placing it in a temporary copy buffer. Generally, you must use the Edit method before making any changes to a record; however, the AddNew and Delete methods are exceptions (no need to use Edit before using either of them). After making changes to a record, you need to use the Update method to save the record.

TIP:

You must have a current record in order to use the Edit method.

WARNING:

If you edit a record and don't use the Update method to save the changes, they will be lost without warning or an error message when you close the recordset or move to another record.

VBA Code

Private Sub cmdEdit_Click()    Dim dbs As Database   Dim rst As Recordset   Dim strDBName As String   Dim strTable As String      strTable = "Categories"   strDBName = "D:\Documents\Northwind.mdb"   Set dbs = OpenDatabase(strDBName)   Set rst = dbs.OpenRecordset(strTable, dbOpenTable)      With rst      .MoveLast      Debug.Print "Description value before editing: " & !Description      .Edit      !Description = "New description of this category"      .Update      Debug.Print "Description value after editing: " & !Description      .Close   End With      End Sub

FillCache

---

recordset.FillCache rows, startbookmark

Argument	Data Type	Description
recordset	Recordset object	A Recordset object created from an ODBC data source, such as a TableDef representing a linked table.
rows	Integer	The number of rows to store in the cache (if omitted, the CacheSize property value is used).
startbookmark	String	The Bookmark specifying the record from which the cache is filled (if omitted, the CacheStart property setting is used).

For Jet-connected ODBC data sources only, this method fills all or part of a recordset's local cache. Caching can improve performance, as data can be retrieved faster from the local cache than from the remote data source.

VBA Code

The following VBA code compares the performance when iterating through a recordset based on a linked table, with or without caching. According to Help, performance should be better with caching, but when I ran this code with a table linked to Northwind, the time was actually slightly longer with caching.

Private Sub cmdFillCache_Click()    Dim dbs As Database   Dim rst As Recordset   Dim strTable As String   Dim sngStart As Single   Dim sngEnd As Single   Dim sngNoCache As Single   Dim sngCache As Single   Dim intCount As Integer   Dim strTemp As String      'Set up a recordset based on a linked table.   strTable = "tblContacts"   Set dbs = CurrentDb   Set rst = dbs.OpenRecordset(strTable, dbOpenDynaset)         'Iterate through recordset and time the operation.   With rst      .MoveFirst      Do While Not .EOF         sngStart = Timer         strTemp = !LastName         .MoveNext      Loop         sngEnd = Timer      sngNoCache = sngEnd         'Display performance results.      Debug.Print "Without cache: " & Format(sngNoCache, "##0,000.000") _                  & " seconds"            'Cache the first 100 records and time the operation again.      intCount = 0      .MoveFirst      .CacheSize = 100      .FillCache      sngStart = Timer      .MoveFirst      Do While Not .EOF         strTemp = !LastName         intCount = intCount + 1         .MoveNext         If intCount Mod 100 = 0 Then            On Error Resume Next            .CacheStart = .Bookmark            .FillCache         End If      Loop         sngEnd = Timer      sngCache = sngEnd         'Display performance results.      Debug.Print "With cache: " & Format(sngCache, "##0,000.000") _                  & " seconds"      .Close   End With   End Sub

FindFirst

---

recordset.FindFirst criteria

Argument	Data Type	Description
recordset	Recordset object	An existing dynaset-type or snapshot-type Recordset object
criteria	String	A search string used to locate a record, similar to the WHERE clause in a SQL statement, but without the word WHERE

For Jet workspaces only, the FindFirst method locates the first record in a dynaset or snapshot-type recordset that meets the specified criteria and makes that record the current record. If no matching record is found, the NoMatch property is set to True, making this property useful in determining the success or failure of the FindFirst method, as shown in the following code sample.

TIP:

To locate a record in a table-type recordset, use the Seek method instead of the FindFirst method.

VBA Code

Private Sub cmdFindFirst_Click()    Dim dbs As Database   Dim rst As Recordset   Dim strDBName As String   Dim strTable As String   Dim strSearch As String   Dim strName As String      strTable = "Employees"   strDBName = "D:\Documents\Northwind.mdb"   Set dbs = OpenDatabase(strDBName)   Set rst = dbs.OpenRecordset(strTable, dbOpenDynaset)   strName = Chr(39) & "Smith" & Chr(39)   strSearch = "[LastName] = " & strName   With rst      .FindFirst strSearch      Debug.Print strName & " found? " & Not .NoMatch      .Close   End With   End Sub

FindLast

---

recordset.FindLast criteria

Argument	Data Type	Description
recordset	Recordset object	An existing dynaset-type or snapshot-type Recordset object
criteria	String	A search string used to locate a record, similar to the WHERE clause in a SQL statement, but without the word WHERE

Similar to the FindFirst method, for Jet workspaces only, the FindLast method locates the last record in a dynaset or snapshot-type recordset that meets the specified criteria and makes that record the current record. If no matching record is found, the NoMatch property is set to True.

VBA Code

Private Sub cmdFindLast_Click()    Dim dbs As Database   Dim rst As Recordset   Dim strDBName As String   Dim strTable As String   Dim strSearch As String   Dim strName As String      strTable = "Employees"   strDBName = "D:\Documents\Northwind.mdb"   Set dbs = OpenDatabase(strDBName)   Set rst = dbs.OpenRecordset(strTable, dbOpenDynaset)   strName = Chr(39) & "Davolio" & Chr(39)   strSearch = "[LastName] = " & strName   With rst      .FindLast strSearch      Debug.Print strName & " found? " & Not .NoMatch      .Close   End With   End Sub

FindNext

---

recordset.FindNext criteria

Argument	Data Type	Description
recordset	Recordset object	An existing dynaset-type or snapshot-type Recordset object
criteria	String	A search string used to locate a record, similar to the WHERE clause in a SQL statement, but without the word WHERE

For Jet workspaces only, the FindNext method locates the next record in a dynaset or snapshot-type recordset that meets the specified criteria and makes that record the current record. If no matching record is found, the NoMatch property is set to True, so you can use this property to tell whether the method was successful in locating another match, as shown in the following code sample.

VBA Code

Private Sub cmdFindNext_Click()    Dim dbs As Database   Dim rst As Recordset   Dim strDBName As String   Dim strTable As String   Dim strSearch As String   Dim strTitle As String      strTable = "Employees"   strDBName = "D:\Documents\Northwind.mdb"   Set dbs = OpenDatabase(strDBName)   Set rst = dbs.OpenRecordset(strTable, dbOpenDynaset)   strTitle = Chr(39) & "Sales Representative" & Chr(39)   strSearch = "[Title] = " & strTitle   With rst      .FindFirst strSearch      If .NoMatch = False Then         Debug.Print "Found a match for " & strTitle & " -- name: " & _                     !LastName      End If      .FindNext strSearch      If .NoMatch = False Then         Debug.Print "Found another match for " & strTitle & "--name: " _                     & !LastName      End If      .Close   End With End Sub

FindPrevious

---

recordset.FindPrevious criteria

Argument	Data Type	Description
recordset	Recordset object	An existing dynaset-type or snapshot-type Recordset object
criteria	String	A search string used to locate a record, similar to the WHERE clause in a SQL statement, but without the word WHERE

FindPrevious works much like FindNext, except that it moves backward through the recordset instead of forward.

VBA Code

Private Sub cmdFindPrevious_Click()    Dim dbs As Database   Dim rst As Recordset   Dim strDBName As String   Dim strTable As String   Dim strSearch As String   Dim strTitle As String      strTable = "Employees"   strDBName = "D:\Documents\Northwind.mdb"   Set dbs = OpenDatabase(strDBName)   Set rst = dbs.OpenRecordset(strTable, dbOpenDynaset)   strTitle = Chr(39) & "Sales Representative" & Chr(39)   strSearch = "[Title] = " & strTitle   With rst      .FindFirst strSearch      If .NoMatch = False Then         Debug.Print "Found a match for " & strTitle & " -- name: " _                     & !LastName      End If      .FindNext strSearch      If .NoMatch = False Then         Debug.Print "Found another match for " & strTitle & "--name: " _                     & !LastName      End If      .FindPrevious strSearch      If .NoMatch = False Then         Debug.Print "Went back to last match for " & strTitle & _                     " -- name: " & !LastName      End If      .Close   End With End Sub

GetRows

---

Set varArray = recordset.GetRows(numrows)

Argument	Data Type	Description
varArray	Variant	An array that stores the retrieved rows of data
recordset	Recordset object	A Recordset object
numrows	Variant	The number of rows to retrieve (if left blank, all available rows are retrieved)

Retrieves multiple rows from a Recordset object into a two-dimensional array. It is very useful for filling list boxes and combo boxes on Outlook forms or Office UserForms from Access tables since they can't be bound to tables or recordsets, unlike Access controls. See the VBS code sample and the second VBA sample for examples of this usage. If you want to retrieve just one field value from the array, you can specify the array element, as in the first VBA code sample (bear in mind that the array numbering is zero-based).

Access VBA Code

Private Sub cmdGetRows_Click()    Dim dbs As Database   Dim rst As Recordset   Dim strTable As String   Dim strFieldValue As String   Dim varRecords As Variant   Dim intRecord As Integer   Dim intField As Integer      strTable = "Employees"   Set dbs = CurrentDb   Set rst = dbs.OpenRecordset(strTable, dbOpenSnapshot)   varRecords = rst.GetRows(10)   Debug.Print "Fourth field in fifth record: " & varRecords(5, 6)   End Sub

Outlook VBS Code

Sub cmdFillListBox_Click    Dim rst   Dim dao   Dim wks   Dim dbs   Dim strAccessDir   Dim objAccess   Dim CustomerArray(99, 2)    'Pick up path to Access database directory from Access SysCmd function.   Set objAccess = Item.Application.CreateObject("Access.Application")   strAccessDir = objAccess.SysCmd(9)   strDBName = strAccessDir & "Samples\Northwind.mdb"   objAccess.Quit    'Set up reference to Access database.   Set dao = Application.CreateObject("DAO.DBEngine.35")   Set wks = dao.Workspaces(0)   Set dbs = wks.OpenDatabase(strDBName)    'Retrieve Customer information from table.   Set rst = dbs.OpenRecordset("Customers")   Set ctl = Item.GetInspector.ModifiedFormPages("Message").Controls("lstCustomers")    ctl.ColumnCount = 3   ctl.ColumnWidths = "50; 150 pt; 75 pt"    'Assign Access data to an array of 3 columns and 100 rows.   CustomerArray(99, 2) = rst.GetRows(100)    ctl.Column() = CustomerArray(99, 2) End Sub

VBA Code Behind an Office UserForm

This code runs from the Initialize event of an Office UserForm, so that the lstCustomers listbox is filled with data from Northwind when the form is run. The UserForm could be run from Word 97 or 2000, Excel 97 or 2000, or Outlook 2000:

Private Sub UserForm_Initialize()    Dim dao As Object   Dim rst As Recordset   Dim wks As Workspace   Dim dbs As Database   Dim strDBName As String   Dim strAccessDir As String   Dim objAccess As New Access.Application   Dim CustomerArray(99, 2)   Dim ctl As ListBox      'Pick up path to Access database directory from Access SysCmd function.   strAccessDir = objAccess.SysCmd(9)   strDBName = strAccessDir & "Samples\Northwind.mdb"   objAccess.Quit    'Set up reference to Access database.   Set dao = CreateObject("DAO.DBEngine.35")   Set wks = dao.Workspaces(0)   Set dbs = wks.OpenDatabase(strDBName)    'Retrieve Customer information from table.   Set rst = dbs.OpenRecordset("Customers")   Set ctl = lstCustomers    ctl.ColumnCount = 3   ctl.ColumnWidths = "50; 150 pt; 75 pt"    'Assign Access data to an array of 3 columns and 100 rows.   CustomerArray(99, 2) = rst.GetRows(100)    ctl.Column() = CustomerArray(99, 2) End Sub

Move

---

recordset.Move rows, startbookmark

Argument	Data Type	Description
recordset	Recordset object	The Recordset object whose current record position is being moved.
rows	Long	The number of rows to move. If rows is greater than zero, the movement is forward; if it is negative, the movement is backward.
startbookmark	String	The Bookmark of the record to start movement from. If omitted, Move begins from the current record.

Moves the current position in a recordset, either forward or backward. If you are at the BOF marker and move backward or at the EOF marker and move forward, you will get an error. You will also get a run-time error if you try to use the Move method when either the BOF or EOF property is True. (See the BOF and EOF property sections for an explanation of these properties.)

VBA Code

This VBA code moves to the last record in a recordset, then back three records:

Private Sub cmdMove_Click()    Dim dbs As Database   Dim rst As Recordset   Dim strDBName As String   Dim strTable As String      strTable = "Employees"   strDBName = "D:\Documents\Northwind.mdb"   Set dbs = OpenDatabase(strDBName)   Set rst = dbs.OpenRecordset(strTable, dbOpenDynaset)   With rst      .MoveLast      .Move -3      Debug.Print "On " & !LastName & " record"      .Close   End With End Sub

MoveFirst

---

recordset.MoveFirst

Moves to the first record in a recordset and makes it the current record. It is often used before a loop that iterates through the records in a recordset to ensure that the loop starts with the first record, as in the code sample in the FillCache section.

WARNING:

If you have edited the current record, before moving to another record, be sure to save the changes with the Update method; otherwise, the changes will be lost with no warning.

MoveLast

---

recordset.MoveLast

Moves to the last record in a recordset and makes it the current record.

TIP:

For dynaset- or snapshot-type recordsets, you need to use the MoveLast method before counting the number of records in a recordset in order to get an accurate count.

VBA Code

Private Sub cmdMoveLast_Click()      Dim dbs As Database   Dim rst As Recordset   Dim strDBName As String   Dim strTable As String   Dim intCount As Integer      strTable = "Categories"   strDBName = "D:\Documents\Northwind.mdb"   Set dbs = OpenDatabase(strDBName)   Set rst = dbs.OpenRecordset(strTable, dbOpenDynaset)   intCount = rst.RecordCount   Debug.Print intCount & " records in recordset (before MoveLast)"         With rst      .MoveLast      intCount = .RecordCount      .Close   End With         Debug.Print intCount & " records in recordset (after MoveLast)" End Sub

MoveNext

---

recordset.MoveNext

Moves to the next record in a recordset and makes it the current record. It is often used in looping structures in code. See the FillCache section for an example of usage.

MovePrevious

---

recordset.MovePrevious

Moves to the previous record in a recordset and makes it the current record. Usage is similar to MoveNext, except for the direction of movement.

NextRecordset

---

Set boolean = recordset.NextRecordset

Argument	Data Type	Description
boolean	Boolean	True indicates that the next set of records is available in recordset; False indicates that there are no more records, and recordset is empty.
recordset	Recordset object	An existing Recordset object variable to which you want to return pending records.

This method gets the next set of records (if any) returned by a multipart select query in an OpenRecordset call. It returns a Boolean value indicating whether there are any more additional records pending. The method only applies to ODBCDirect workspaces.

OpenRecordset

---

Set recordset = object.OpenRecordset(name, type, options, lockedit)
Set recordset = object.OpenRecordset(type, options, lockedit)

Argument	Data Type	Description
recordset	Recordset object	The Recordset object to be opened.
object	Connection, Database, Recordset, QueryDef, or TableDef object	The object from which the recordset is to be created.
source	String	The record source for the new Recordset object. May be a table name, a query name, or a SQL statement. For table-type Jet recordsets, only table names are allowable.
name	Integer	A named constant or Integer value defining the type of recordset to open (see Table 8-15).
options	Long	A named constant or Long value (see Table 8-16).
lockedit	Integer	A named constant or Integer value (see Table 8-17).

WARNING:

A saved query or SQL statement used for the source argument must be a query that returns records, not an action query. If you specify an action query, an "Invalid Operation" error occurs.

Table 8-15: The Type Intrinsic Constants

Named Constant	Value	Description
dbOpenTable	1	Opens a table-type Recordset object ( Jet workspaces only)
dbOpenDynamic	16	Opens a dynamic-type Recordset object, similar to an ODBC dynamic cursor (ODBCDirect workspaces only)
dbOpenDynaset	2	Opens a dynaset-type Recordset object, similar to an ODBC keyset cursor
dbOpenSnapshot	4	Opens a snapshot-type Recordset object similar to an ODBC static cursor
dbOpenForwardOnly	8	Opens a forward-only-type Recordset object

Table 8-16: The Options Intrinsic Constants

Named Constant	Value	Description
dbAppendOnly	8	Allows users to append new records to the Recordset, but prevents them from editing or deleting existing records ( Jet dynaset-type Recordset only).
dbSQLPassThrough	64	Passes a SQL statement to a Jet-connected ODBC data source for processing ( Jet snapshot-type Recordset only).
dbSeeChanges	512	Generates a run-time error if one user is changing data that another user is editing ( Jet dynaset-type Recordset only). This setting is useful in applications where multiple users have simultaneous read/write access to the same data.
dbDenyWrite	1	Prevents other users from modifying or adding records ( Jet Recordset objects only).
dbDenyRead	2	Prevents other users from reading data in a table ( Jet table-type Recordset only).
dbForwardOnly	256	Creates a forward-only Recordset ( Jet snapshot-type Recordset only). It is provided only for backward compatibility, and you should use the dbOpenForwardOnly constant in the type argument instead of using this option.
dbReadOnly	4	Prevents users from making changes to the Recordset ( Jet only). The dbReadOnly constant in the lockedit argument replaces this option, which is provided only for backward compatibility.
dbRunAsync	1024	Runs an asynchronous query (ODBCDirect workspaces only).
dbExecDirect	2048	Runs a query by skipping SQLPrepare and directly calling SQLExecDirect (ODBCDirect workspaces only). Use this option only when you're not opening a Recordset based on a parameter query.
dbInconsistent	16	Allows inconsistent updates ( Jet dynaset-type and snapshot-type Recordset objects only).
dbConsistent	32	Allows only consistent updates ( Jet dynaset-type and snapshot-type Recordset objects only).

Table 8-17: The LockEdit Intrinsic Constants

Named Constant	Value	Description
dbReadOnly	4	Prevents users from making changes to the Recordset (default for ODBCDirect workspaces). You can use dbReadOnly in either the options argument or the lockedit argument, but not both. If you use it for both arguments, a run-time error occurs.
dbPessimistic	2	Uses pessimistic locking to determine how changes are made to the Recordset in a multiuser environment. The page containing the record you're editing is locked as soon as you use the Edit method (default for Jet workspaces).
dbOptimistic	3	Uses optimistic locking to determine how changes are made to the Recordset in a multiuser environment. The page containing the record is not locked until the Update method is executed.
dbOptimisticValue	1	Uses optimistic concurrency based on row values (ODBCDirect workspaces only).
dbOptimisticBatch	5	Enables batch optimistic updating (ODBCDirect workspaces only).

The OpenRecordset method creates a new recordset and automatically appends it to the Recordsets collection. The first syntax variant for the OpenRecordset method call applies to Connection and Database objects, and the second variant applies to QueryDef, Recordset, and TableDef objects. See the OpenRecordset section in Chapter 5, Databases Collection and Database Object, for a number of code samples illustrating use of this method with Database objects in Access VBA, Word VBA, Excel VBA, and Outlook VBS code.

VBA Code

This code opens a filtered recordset based on another recordset and displays the results to the Debug window:

Private Sub cmdOpenRecordset_Click()    Dim dbs As Database   Dim strDBName As String   Dim rstEmployees As Recordset   Dim rstWAEmployees As Recordset   Dim strSearch As String      strDBName = "D:\Documents\Northwind.mdb"   Set dbs = OpenDatabase(strDBName)   Set rstEmployees = dbs.OpenRecordset("Employees", dbOpenDynaset)      With rstEmployees      Debug.Print vbCrLf & "Unfiltered recordset:"      Do While Not .EOF         Debug.Print "Name: " & !LastName & ", state: " & !Region         .MoveNext      Loop   End With      'Create a second, filtered recordset based on the first recordset.   rstEmployees.Filter = "Region = 'WA'"   Set rstWAEmployees = rstEmployees.OpenRecordset()    With rstWAEmployees      Debug.Print vbCrLf & "Filtered recordset:"      Do While Not .EOF         Debug.Print "Name: " & !LastName & ", state: " & !Region         .MoveNext      Loop   End With   End Sub

Requery

---

recordset.Requery newquerydef

Argument	Data Type	Description
recordset	Recordset object	An existing Jet dynaset-, snapshot-, or forward-only Recordset object, or an ODBCDirect Recordset object
newquerydef	Variant	(Optional) The Name property of a QueryDef object ( Jet workspaces only)

The Requery method updates the data in a recordset by re-executing the query on which it is based. The newquerydef argument can be used to specify a new query for the recordset.

WARNING:

When you use the Requery method, the first record in the recordset becomes the current record, which can be a problem on forms. To avoid losing the user's place on a form after requerying, you can set a search string before requerying so you can return to the record you were on, as in the following code sample.

VBA Code

This code runs from a command button on an Access form; it calls a function (Calcinome, not reproduced here) that modifies data in the form's record source, so that the form needs requerying:

Private Sub cmdRequery_Click()    Dim strSearch As String   Dim strContract As String      'Create search string for current record.   strContract = Me![ContractNo]   strSearch = "[ContractNo] = " & strContract      'Call a function that needs requerying.   Call Calcincome   Me.Requery      'Find the record that matches the control.   Debug.Print "Search string: " & strSearch   Me.RecordsetClone.FindFirst strSearch   Me.Bookmark = Me.RecordsetClone.Bookmark End Sub

Seek

---

recordset.Seek comparison, key1, key2...key13

Argument	Data Type	Description
recordset	Recordset object	An existing table-type Recordset object with an index specified by the Recordset object's Index property
comparison	String	One of the following expressions: <, <=, =, >=, or >
key1, key2..., key13		One or more values (up to 13 in number) corresponding to fields in the Recordset object's current index, as specified by its Index property

For indexed table-type recordsets in Jet workspaces, the Seek method locates a record that meets the criteria for the current index and makes it the current record. You must set the Index property to the index you want to use before using Seek. (You can also determine if the index you want is active by retrieving its value beforehand.) If the index identifies a nonunique key field, Seek locates the first record that satisfies the criteria.

For =, >=, and > comparisons, Seek starts at the beginning of the index and seeks forward; for <>

You must specify values for all fields defined in the index in order to use Seek.

The key1 argument must be of the same field data type as the corresponding field in the current index.

Seek can be used even if there is no current record. You can't use Seek on a linked table, because you can't create a table-type recordset on a linked table.

See the Index property section for more information about indexes.

VBA Code

Private Sub cmdSeek_Click()    Dim dbs As Database   Dim strDBName As String   Dim rst As Recordset   Dim varBookmark As Variant   Dim intEmployee As Integer      strDBName = "D:\Documents\Northwind.mdb"   Set dbs = OpenDatabase(strDBName)   Set rst = dbs.OpenRecordset("Employees", dbOpenTable)   With rst      .Index = "PrimaryKey"            'Bookmark current record so we can return to it later.      varBookmark = .Bookmark      intEmployee = InputBox("Enter an Employee ID:")      .Seek "=", intEmployee            'Return to current record if Seek fails.      If .NoMatch Then         MsgBox "ID " & intEmployee & " not found"         .Bookmark = varBookmark      Else         MsgBox "Found ID at " & !LastName & " record"      End If      .Close   End With   End Sub 

Update

---

recordset.Update(updatetype, force)

Argument	Data Type	Description
recordset	Recordset object	An open, updatable Recordset object.
updatetype	Integer	A named constant or Integer value (see Table 8-18) (for ODBCDirect workspaces only).
force	Boolean	(Optional) If True, force changes regardless of whether another user has made any changes. If False (default), the update fails if another user has made changes while the update is pending.

Table 8-18: The UpdateType Intrinsic Constants

Named Constant	Value	Description
dbUpdateBatch	4	All pending changes in the update cache are written to disk
dbUpdateCurrentRecord	2	Only the current record's pending changes are written to disk
dbUpdateRegular	1	(Default) Pending changes aren't cached and are written to disk immediately

The Update method is crucial to saving your edits; it must be used for all edits done to a record, after creating a new record with AddNew, or calling the Edit method to edit an existing record. One exception is that you don't need to use Update after deleting a record with Delete. If you don't use Update after making a change (other than a Delete), the changes will be lost in any of the following situations:

• You use Edit or AddNew and move to another record without using Update.

• You use Edit or AddNew then use Edit or AddNew again without first using Update.

• You set the Bookmark property to another record.

• You close the recordset without first using Update.

• You cancel editing by using CancelUpdate.

VBA Code

This example code illustrates a standard use of the Update method after using the Edit method and making some changes to a record, in a looping structure that iterates through an entire recordset:

Private Sub cmdUpdate_Click()    Dim dbs As Database   Dim strDBName As String   Dim rst As Recordset      strDBName = "D:\Documents\Northwind.mdb"   Set dbs = OpenDatabase(strDBName)   Set rst = dbs.OpenRecordset("Employees", dbOpenTable)   With rst      .MoveFirst      Do While Not .EOF         .Edit         If !Title = "Sales Representative" Then !Title = "Sales Rep"         .Update         .MoveNext      Loop      .Close   End With   dbs.Close   End Sub

---

1. In an ODBCDirect workspace a snapshot-type Recordset may be updatable, depending on the ODBC driver. The LastModified property is available and the Updatable property is True only on ODBCDirect snapshot-type Recordset objects if the ODBC driver supports updatable snapshots.

2. In an ODBCDirect workspace, a snapshot-type recordset may be updatable, depending on the ODBC driver. The AddNew, Edit, Delete, Update, and CancelUpdate methods are only available on ODBCDirect snapshot-type Recordset objects if the ODBC driver supports updatable snapshots.

3. Only with forward moves that don't use a bookmark offset.

http://oreilly.com/catalog/progacdao/chapter/ch08.html