test driven access development

[Phlip]

Accessories:

The canonic way to write "unit" tests for a database is this:

setUp
begin a transaction
create Sample Database

test_foo
select stuff in the database
assert it matches the samples

test_bar
update stuff in the database
select it back
assert it matches

tearDown
rollback the transaction

That's relatively efficient because transactions buffer everything with
miminal overhead. I want to do that to Access while I test a Form. And I
want to do it from VB Classic, not from VBA inside Access.

So let's set up an experiement to see how close we can get to that pattern.
Creae a form, paint a button, set up the References to Access and DAO, and
write this:

Private Sub Command1_Click()

Dim ax As New Access.Application
ax.Visible = False
ax.AutomationSecurity = msoAutomationSecurityLow
ax.OpenCurrentDatabase "database.mdb"
Dim cn As DAO.Database
Set cn = ax.CurrentDb
ax.DBEngine.BeginTrans

' beginning to build the sample database
cn.Execute "DELETE FROM DataTable"

ax.DoCmd.OpenForm FormName:="DataTable"

End Sub

The last line fails with the infamous 3008 error message, "The table
'DataTable' is already opened exclusively by another user, or it is already
open through the user interface and cannot be manipulated programmatically."

The .BeginTrans call did it. Without that line, I can delete the table and
open its form fine. (I intend to write test cases that mess with that form,
and its data, for the usual test-first benefits.)

The standard answer to 3008 is to turn off record locking. All our locking
here is off and optimistic.

To workaround, I can of course just fudge. I could manually build a
transaction by copying the DataTable to a backup table, then restore it
during tearDown(). Or I can let the test data polute the golden copy of the
database.

The question is this: Am I missing some tweak in the interaction of
BeginTrans and the Form architecture where I can have a real transaction,
and the Form can display data from that transaction transparently?

 

[Larry Linson]

In Access/Jet, transactions apply to database actions performed it code, not
to Access forms. There are some implications of transactions behind the
scenes in the way that forms work, but we have no access to those.

If you develop on a "golden copy" of the database, then your development
methodology is quite different than mine (and most other experienced
developers I know). I always have a separate "development" copy of the
database, both client or front end, and server or backend data. One of the
steps in distribution is to change the table links from the development DB
to the production DB.

In any case, this newsgroup is devoted to Microsoft Access database
software, not to all uses of Jet databases in whatever language. It's just
chance if you find someone here who's familiar with using a Jet DB with the
separate Visual Basic product.

Larry Linson
Microsoft Access MVP

 

[Phlip]

In Access/Jet, transactions apply to database actions performed it code, not
to Access forms. There are some implications of transactions behind the
scenes in the way that forms work, but we have no access to those.

So, your preliminary response is I have overlooked no tweak available in the
transaction system, or in the forms, to let them peacibly coexist.

No prob. I just wanted to cover all bases.

If you develop on a "golden copy" of the database, then your development
methodology is quite different than mine (and most other experienced
developers I know).

sigh< I want the released copy to be "more golden" than the development

copy, which is in turn "more golden" than the test copy. No, I'm not writing
test cases directly against a production database, folks!

I always have a separate "development" copy of the
database, both client or front end, and server or backend data. One of the
steps in distribution is to change the table links from the development DB
to the production DB.

That's on the Do List.

In any case, this newsgroup is devoted to Microsoft Access database
software, not to all uses of Jet databases in whatever language. It's just
chance if you find someone here who's familiar with using a Jet DB with the
separate Visual Basic product.

As an expert on newsgroup topicality (and when to push it), I humbly reject
your rationale. I'm using Access, lock stock and barrel, thru Automation.
The bug happens between a Jet call and an Access call. I could easily
reproduce the bug in VBA or any other Automation-enabled language.

 

[Marshall Barton]

Phlip wrote:
[snip]

The question is this: Am I missing some tweak in the interaction of
BeginTrans and the Form architecture where I can have a real transaction,
and the Form can display data from that transaction transparently?

I can't help you, but A google search found an old thread
that may be helpful

http://groups-beta.google.com/group...+group:*Access*&rnum=2&hl=en#07a3a98c7d130610

 

['69 Camaro]

Hi, Phlip.

So, your preliminary response is I have overlooked no tweak available in
the
transaction system, or in the forms, to let them peacibly coexist.

To add to what Larry wrote, there are explicit transactions (which you
initiated with the BeginTrans procedure) and implicit transactions (which
you initiated by opening a bound form based upon an Dynaset type of
Recordset). You have no control over this implicit transaction, but you
_do_ have control over the explicit transaction, and if you do so correctly,
then they can both peacefully coexist.

The bug happens between a Jet call and an Access call. I could easily
reproduce the bug in VBA or any other Automation-enabled language.

Bug? What bug? It's Jet's built-in safeguard to keep your data consistent
and avoid corruption. Your code requires Jet to make two connections to the
database file, so it opens both in shared mode in the default Workspace
object, since your code didn't specify otherwise. Then your code executes
an operation that requires exclusive mode (to prevent one user's
transactions from stepping on another user's transactions and vice-versa).
Jet can temporarily elevate the Database object to exclusive mode, if and
only if the database isn't already open in shared mode -- and it is. That's
why you get the error message.

The solution is to open the Database object in a separate Workspace object,
not the default Workspace object. Try the following code:

' * * * * * * Begin Code * * * * * * * * * * *

Public Sub TestTrans()

On Error GoTo ErrHandler

Dim ax As New Access.Application
Dim wkspc As Workspace
Dim db As Database
Dim sTestPath As String
Dim sFileName As String
Dim fOpenedWkSpc As Boolean
Dim fOpenedDB As Boolean
Dim fStartedTrans As Boolean

sTestPath = "D:\Test\"
sFileName = "Junk.mdb"

Set wkspc = DBEngine.CreateWorkspace("TempWkSpc", "Admin", "")
fOpenedWkSpc = True
Set db = wkspc.OpenDatabase(sTestPath & sFileName)
fOpenedDB = True
ax.Visible = False
ax.AutomationSecurity = msoAutomationSecurityLow
ax.OpenCurrentDatabase sTestPath & sFileName
ax.DBEngine.BeginTrans
fStartedTrans = True

' beginning to build the sample database
db.Execute "DELETE FROM DataTable"
ax.DoCmd.OpenForm FormName:="DataTable"

'------------------------------------------------------------------
' Do whatever else needs to be done in the transaction.
'------------------------------------------------------------------

CleanUp:
'------------------------------------------------------------------
' This is just a test, so roll back to base level.
'------------------------------------------------------------------

If (fStartedTrans) Then
ax.DBEngine.Rollback
fStartedTrans = False
End If

'------------------------------------------------------------------
' Release system resources.
'------------------------------------------------------------------

ax.Quit
Set ax = Nothing

If (fOpenedDB) Then
db.Close
fOpenedDB = False
End If

Set db = Nothing

If (fOpenedWkSpc) Then
wkspc.Close
fOpenedWkSpc = False
End If

Set wkspc = Nothing

Exit Sub

ErrHandler:

MsgBox "Error in TestTrans( ) in TestModule." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
GoTo CleanUp

End Sub ' TestTrans( )

' * * * * * * End Code * * * * * * * * * * *


HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

 

[Phlip]

Bug? What bug?

MY bug!

Sheesh I apologize profusely for implying that Jet could possibly have a
bug!

Set wkspc = DBEngine.CreateWorkspace("TempWkSpc", "Admin", "")

I thought that Engine should be Access's, not the one on the VB side.

Regardless, this (otherwise very nice) sample misses my requirements.

ax.DBEngine.BeginTrans
db.Execute "DELETE FROM DataTable"
ax.DoCmd.OpenForm FormName:="DataTable"

Only the local workspace sees the transaction. I need to spike the database
with sample data, within the transaction, so OpenForm shows a form
containing the spike sample.

I am aware the reason I can't is "because I can't". What's the cheapest
workaround?

Right now my test code will just check for the sample data in the
semi-golden database version, and add it if it's not there. I would prefer
transactions, or a hand-rolled equivalent, to satisfy the requirements of
Test Isolation.

 

[Brendan Reynolds]

As I understand it, changes to data within the transaction are isolated from
any operation taking place outside of that transaction, because it is
considered unsafe to allow another user or process access (no pun intended)
to uncommitted data. The thinking - again, as I understand it - is that
another user (or process) should not see uncommitted data because the result
would be unreliable. To the best of my knowledge, I don't think there is any
way to have your form see the changes made within the transaction. I'd go
with making a copy of the table, running the test, then restoring the copy.
Or, if the application is split from the data, make a copy of the entire
data file and restore that after the test. Admittedly this is not elegant,
but it works.

 

['69 Camaro]

Hi, Phlip.

I thought that Engine should be Access's, not the one on the VB side.

You're asking this question in an Access newsgroup, not a VB newsgroup, so
you're likely to get the Access version of VBA code examples, not VB
examples. You may have to do some slight tweaking. Larry pointed this out
to you, but in your own words, you "rejected his rationale." We'll try to
help, but caveat emptor.

Only the local workspace sees the transaction.

Now that you've gotten past the hurdle of the error message, you see a flaw
in your logic. You need to redesign your test. (This is why it always
takes longer to develop software than we think it will. As soon as we
overcome one major hurdle, there's another one that requires us to undo or
redesign some of what we've already created in order to get past that
hurdle, too.)

Most people don't have the luxury of resources, but we've found that having
three environments, development, test, and production, work best for
software development. The test environment is isolated, but it's a
duplicate of the production environment and can be replaced as often as
necessary, so the pristine state of the production environment stays that
way throughout testing.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

 

[Phlip]

Most people don't have the luxury of resources, but we've found that having
three environments, development, test, and production, work best for...

It is very unfortunate I used the term "golden copy" early on because that
has completely derailed the thread.

Supposed I asked a different question...

Hi. I'm Joe Schmuck, fresh out of an average USA high school, and I am
learning Access via Code-and-Fix in a debugger, not those flibberty gibberty
tests.

I want to write a form where the user thinks they are cursoring and updating
records when instead they are cursoring into a buffer automatically
maintained by the database. I am aware I can use Copy Table, and direct the
Form into the faked Table, but I am curious if there's a more efficient way.

 

[Brendan Reynolds]

How long does it take to copy a table? How much more efficient can you get?

That said, though, if you're still looking for an alternative - what if you
bound the form to a disconnected recordset? You'd need to use ADO rather
than DAO, but I guess whether that is a problem depends on what you're
testing.

 

[Phlip]

How long does it take to copy a table? How much more efficient can you get?

That said, though, if you're still looking for an alternative - what if you
bound the form to a disconnected recordset? You'd need to use ADO rather
than DAO, but I guess whether that is a problem depends on what you're
testing.

Now that we are safely back to testing...

If the production code relies on Access internals then it relies on DAO, so
we shouldn't afflict the production code by changing to ADO just so the test
code can get a disconnected recordset.

Copying tables it is. I will look up how Jet does a CREATE TEMPORARY TABLE
and take it from there.

Thanks all!

 

[Tony Toews]

Copying tables it is.

I'd suggest using append queries instead to copy data from temporary
tables to permanent tables.

I will look up how Jet does a CREATE TEMPORARY TABLE
and take it from there.

See the TempTables.MDB page at my website which illustrates how to use
a temporary MDB in your app.
http://www.granite.ab.ca/access/temptables.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm