Not Responding .AddNew Record ADP

[Aubrey]

Users are experiencing "Not Responding" conditions of 2-3 minutes with this
Code. The tblNotes has 36,351 Rows; tblNoteNarrative has 20,245 Rows. During
Beta testing, this code ran extremely fast (1-2 Seconds) against a
stand-alone sample database. Did moving it to a Remote Server and connectiong
to an Anonymous Merge Subscription replica add Columns that need indexing? Or
do I need to use different values for the .Open Parameters?

Each of these Lines take some 15-20 seconds to respond in Single-Step Debug:
rst1.Open "tblNotes", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
rst2.Open strTblName, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic

- - - - - - - - - -
Private Sub AddNewNote(lngNType As Long, strNBrief As String, strTblName As
String, strFrmName As String)
On Error GoTo Err_Handler
Dim rst1 As New ADODB.Recordset
Dim rst2 As New ADODB.Recordset
Dim lngNID As Long

Randomize
lngNID = Int((Rnd * 2 ^ 30) - 2 ^ 29)

rst1.Open "tblNotes", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
rst1.AddNew
rst1![Patient ID #] = Me!Text219
rst1![PID] = Me!Text387
rst1![NDate] = Now()
rst1![ProvID] = pstrLogonID
rst1![NType] = lngNType
rst1![NBrief] = strNBrief
rst1![IsSaved] = True
rst1![NID] = lngNID
rst1.Update
rst1.Close
Set rst1 = Nothing
rst2.Open strTblName, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
rst2.AddNew
rst2![Patient ID #] = Me!Text219
rst2![PID] = Me!Text387
rst2![SysDate] = Now()
rst2![VisitDate] = DateValue(Now())
rst2![ProvID] = pstrLogonID
rst2![IsSaved] = True
rst2![NID] = lngNID
rst2.Update
rst2.Close
Set rst2 = Nothing
Me.Refresh
DoCmd.OpenForm strFrmName, , , "[NID]=" & lngNID
Exit_Handler:
Exit Sub
Err_Handler:
MsgBox Err.Description
Resume Exit_Handler
End Sub
- - - - - - - - - -

Access 10 Project Runtime on MSDERelA in Windows XP Pro Notebooks. Database
is a 413 MB Anonymous Merge Subscription.

 

[Sylvain Lafontaine]

You should set the dynamic properties "Append-Only Rowset" to true before
opening the recordset:

rst.CursorLocation = adUseClient
rst.Properties("Append-Only Rowset") = True
rst.Open ....

or modify the query so that no record are returned:

rst.Open "select * from dbo.tblNotes where 1=0", ....

 

[Andrew Backer]

Why not construct a valid INSERT statement, or a stored procedure?
Both would probably be better ways to do it in the long run.

 

[Aubrey]

I am back at this problem. Sylvain's suggestions did not speed up module.
Profiler and debug.print still offer no clues to the slow process.

I cannot find sample code for INSERT or stored procedure that I can adapt to
the Project. But I would really like to try them.

Further suggestions? Sample Code? Most definitely appreciated!

 

[Sylvain Lafontaine]

A possible explanation for your problem might be a network problem. How
things are going if you run them directly on the server?

The « Not Responding » problem could also be the result of a permission
problem on the server.

 

[Aubrey]

Not running over a network. The 'Server' is within the same machine --
"Access 10 Project Runtime on MSDERelA in Windows XP Pro Notebooks. Database
is a 413 MB Anonymous Merge Subscription."

Permissions are Windows Authentication, except during Replication, so
Regedt32 used to alter ... MSSQL\MSSQL\LoginMode=2

Good to hear you again.

 

[Sylvain Lafontaine]

I don't use Replication, so I cannot tell you for sure but the use of
adOpenKeyset with the merge replication might be the problem; especially for
inserts and also considering the fact that you are trying to open the whole
table, something that might lead to problems because SQL-Server need to lock
the whole table.

I'm not sure but I think that's also a better idea to not use the syntax «
Dim rs as New ADODB.Recordset » because VBA has the bad tendency of
creating/recreating the object multiple times. Personally, I prefer to
explicitely create and assign the object with Set and New.

Finally, I would try with a client recordset and adOpenStatic. I would also
use a Command object with a Select statement:

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command

cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText
cmd.CommandText = "select * from tblNotes where 1=0"

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

rs.CursorLocation = adUseClient
rs.Open cmd, , adOpenStatic, adLockOptimistic

 

[Aubrey]

WOW!!! Not often that Cut&Paste Code WORKS "AS IS"! Thanks!

Pasted TWICE, once for tblNotes and again for strTblName, so cmd1, cmd2,
rs1, rs2, . . . Ran in under two seconds everytime!

debug.print Now(),"Start, etc."
10/12/2005 5:34:45 AM Start
10/12/2005 5:34:45 AM Open 1
10/12/2005 5:34:45 AM Open 2
10/12/2005 5:34:45 AM Randomize
10/12/2005 5:34:45 AM Add 1
10/12/2005 5:34:46 AM Add 2
10/12/2005 5:34:46 AM Check 7,8
10/12/2005 5:34:46 AM Open Form

 

[Sylvain Lafontaine]

Lucky for you; don't happen often for me too.

If I were you, I would make some tests to determine exactly which one of
these suggestions was the good one.

 

[Sylvain Lafontaine]

Hi,

Nobody is perfect: you will achieve a slightly better performance by
adding the Set command before assigning the ActiveConnection to the command
object:

Set cmd.ActiveConnection = CurrentProject.Connection

Otherwise, it's not the same connection object that is assigned to the
command object but a whole new object, using the connection string provided
by CurrentProject.Connection; with the side effect of also opening a new
connection with the SQL-Server.

This behavior raise from the fact that the default property of the
Connection object is a string and that a string is also the default argument
for the default Connection object constructor.