MS Access project with SQL Server - Re: Not Responding .AddNew Record ADP

Author

Re: Not Responding .AddNew Record ADP

Andrew Backer

2005-08-28, 8:24 pm

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

2005-10-27, 8:32 am

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!
--
Aubrey Kelley

"Andrew Backer" wrote:

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

Sylvain Lafontaine

2005-10-27, 8:32 am

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.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

"Aubrey" <miscuates@online.nospam> wrote in message
news:C4D1D7BE-7EBC-4F9F-B091- 94AAEB17F6E5@microso
ft.com...[color=darkred]
>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!
> --
> Aubrey Kelley
>
>
> "Andrew Backer" wrote:
>

Aubrey

2005-10-27, 8:32 am

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\LoginMod
e=2

Good to hear you again.

--
Aubrey Kelley

"Sylvain Lafontaine" wrote:

> 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.
>
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: http://cerbermail.com/?QugbLEWINF
>
>
> "Aubrey" <miscuates@online.nospam> wrote in message
> news:C4D1D7BE-7EBC-4F9F-B091- 94AAEB17F6E5@microso
ft.com...
>
>
>

Sylvain Lafontaine

2005-10-27, 8:32 am

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

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

"Aubrey" <miscuates@online.nospam> wrote in message
news:9DCD5255-4B39-48ED-9196- BA173B67E3DF@microso
ft.com...[color=darkred]
> 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\LoginMod
e=2
>
> Good to hear you again.
>
> --
> Aubrey Kelley
>
>
> "Sylvain Lafontaine" wrote:
>

Aubrey

2005-10-27, 8:32 am

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
--
Aubrey Kelley

"Sylvain Lafontaine" wrote:

> 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
>
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: http://cerbermail.com/?QugbLEWINF
>
>
> "Aubrey" <miscuates@online.nospam> wrote in message
> news:9DCD5255-4B39-48ED-9196- BA173B67E3DF@microso
ft.com...
>
>
>

Sylvain Lafontaine

2005-10-27, 8:32 am

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, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

"Aubrey" <miscuates@online.nospam> wrote in message
news:8F2CDE23-DF1E-4304-B79D- C821FDDC2593@microso
ft.com...[color=darkred]
> 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
> --
> Aubrey Kelley
>
>
> "Sylvain Lafontaine" wrote:
>

Sylvain Lafontaine

2005-10-27, 8:32 am

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.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

"Aubrey" <miscuates@online.nospam> wrote in message
news:8F2CDE23-DF1E-4304-B79D- C821FDDC2593@microso
ft.com...[color=darkred]
> 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
> --
> Aubrey Kelley
>
>
> "Sylvain Lafontaine" wrote:
>

Sponsored Links