Re: Not Responding .AddNew Record ADP

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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@xxxxxxxxxxxxx> wrote in message
news:8F2CDE23-DF1E-4304-B79D-C821FDDC2593@xxxxxxxxxxxxxxxx
> 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@xxxxxxxxxxxxx> wrote in message
>> news:9DCD5255-4B39-48ED-9196-BA173B67E3DF@xxxxxxxxxxxxxxxx
>> > 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.
>> >
>> > --
>> > 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@xxxxxxxxxxxxx> wrote in message
>> >> news:C4D1D7BE-7EBC-4F9F-B091-94AAEB17F6E5@xxxxxxxxxxxxxxxx
>> >> >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.
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>


.

Relevant Pages

  • Re: Too many args when running a stored procedure
    ... Perhaps you have a duplicate definition of "AddAJob" in the master database schema. ... you don't show that you are associating the connection object with ... >> it with the command object. ... >>> @Rate money ...
    (microsoft.public.dotnet.languages.vb)
  • Re: ado_command (Communicate the connection object to the command object)
    ... Error: Object returned error code: 0xFFFFFFFE ... % Communicate the connection string to the connection object ... %disp('Communicate the sql command string to the command object'); ... We suspect it has something to do with the version of the ado_connection and ado_command being different, but not sure how to fix it. ...
    (comp.soft-sys.matlab)
  • ado_command (Communicate the connection object to the command object)
    ... Error: Object returned error code: 0xFFFFFFFE ... % Communicate the connection string to the connection object ... %disp('Communicate the sql command string to the command object'); ...
    (comp.soft-sys.matlab)
  • Re: ado_command (Communicate the connection object to the command object)
    ... Error: Object returned error code: 0xFFFFFFFE ... % Communicate the connection string to the connection object ... %disp('Communicate the sql command string to the command object'); ...
    (comp.soft-sys.matlab)
  • Re: keyboard shortcut to edit cell contents
    ... Edit Mode itself isn't a command - it's a state. ... Edit Mode is also different for cells, in that the result of the entry ... Does assigning a key command to "Edit Text..." ...
    (microsoft.public.mac.office.excel)