sql update query worked with access BE but not with SQL BE

[mcarlyle via AccessMonster.com]

I have this code...

Private Sub Command21_Click()
Dim db As DAO.Database
Dim RS As DAO.Recordset
Dim leadinfo As String


If [Howmany] = 0 Then Exit Sub
If CInt([Howmany]) > [availleads] Then Exit Sub
If [Sales Rep Name] = Null Then Exit Sub



Set db = CurrentDb()
Set RS = db.OpenRecordset("qryUnassignedOldLeads", dbOpenDynaset)
SumLeads = DSum("CountOfSource", "qryUnassignedOldLeads")
Do Until RS.EOF

Percent = RS.Fields("CountOfSource") / SumLeads
NumLeads = CInt(Percent * [Howmany])
If NumLeads = 0 Then
NumLeads = 1
End If
'RS.Fields("CountOfSource"))
CurrentSource = RS.Fields("Source")
DoCmd.SetWarnings False
leadinfo = [Text15] & " " & NumLeads & " " & CurrentSource
MsgBox leadinfo, vbOKOnly, "info to pass"
DoCmd.RunSQL "UPDATE clients SET salesperson = " & Text15 & " WHERE ID IN
( SELECT TOP " & NumLeads & " ID FROM clients WHERE Salesperson IS NULL And
[task note] = 'Unworked Lead' And Source = """ & CurrentSource & """) "
DoCmd.SetWarnings True

RS.MoveNext
Loop

End Sub

It is hanging and locking up on this line

DoCmd.RunSQL "UPDATE clients SET salesperson = " & Text15 & " WHERE ID IN (
SELECT TOP " & NumLeads & " ID FROM clients WHERE Salesperson IS NULL And
[task note] = 'Unworked Lead' And Source = """ & CurrentSource & """) "

This code worked when I was on an access BE but now that I am trying to
update directly to SQL it no longer works.

Any ideas why?

 

[Sylvain Lafontaine]

No idea but here are some suggestions:

1- Show us the final result for the string that will be used as a parameter
to the DoCmd.RunSQL command instead of a bunch of VBA code.

2- Use single quotes (') instead of double quotes (") around CurrentSource.

3- Use the SQL-Server Profiler to see what command exactly Access is sending
to the sql-server.

4- Instead of DoCmd.RunSQL; you should use a Passthrough query for this kind
of stuff.

5- Make sure that no null value if returned for the ID in the subquery.

6- And finally, what do you mean exactly with the words « It is hanging and
locking up on this line ». Do you mean that no answer is returned from
SQL-Server or that the debugger in Access open on this line and tell you
that there is an error or something else?

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain aei ca (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[mcarlyle via AccessMonster.com]

1) not sure what you are asking me here but I will take a stab at it... I
will be sending a numeric value (1-5xx) into a field into the sql table.
This value is chosen from a drop down list and is a "rep number" or number
assigned to a sales rep. The code evenly give the rep an even percentage of
all available leads by source. So if he asks for 10 leads and I have 50 of
source a and 20 of source b and 30 of source c he will get 5 from a 2 from b
and 3 from c.

2) will try that

3) don't know what or where that is... will try to read up and find out.

4) Not sure what that is either... this module was written by my old IT guy
and is the only module I didn't develop and so I don't really know it. Still
trying to figure it out.

5) I did a break and checked it and the values are all correct

6) What I mean is when I push the button that causes this code to be run the
DB just freezes and appears to stop responding. I let it go for over 15
minutes when it usually takes less than 30 seconds. I can do a CTRL Break
and the debug will be on the line that I noted. All the data appears to be
correct, but perhaps the syntax is not.

Thanks.

No idea but here are some suggestions:

1- Show us the final result for the string that will be used as a parameter
to the DoCmd.RunSQL command instead of a bunch of VBA code.

2- Use single quotes (') instead of double quotes (") around CurrentSource.

3- Use the SQL-Server Profiler to see what command exactly Access is sending
to the sql-server.

4- Instead of DoCmd.RunSQL; you should use a Passthrough query for this kind
of stuff.

5- Make sure that no null value if returned for the ID in the subquery.

6- And finally, what do you mean exactly with the words « It is hanging and
locking up on this line ». Do you mean that no answer is returned from
SQL-Server or that the debugger in Access open on this line and tell you
that there is an error or something else?

I have this code...

[quoted text clipped - 45 lines]

Any ideas why?

 

[amilojko]

always use Replace(string, "'","''")
to escape strings that might have single quotes

1) not sure what you are asking me here but I will take a stab at it... I
will be sending a numeric value (1-5xx) into a field into the sql table.
This value is chosen from a drop down list and is a "rep number" or number
assigned to a sales rep. The code evenly give the rep an even percentage of
all available leads by source. So if he asks for 10 leads and I have 50 of
source a and 20 of source b and 30 of source c he will get 5 from a 2 from b
and 3 from c.

2) will try that

3) don't know what or where that is... will try to read up and find out.

4) Not sure what that is either... this module was written by my old IT guy
and is the only module I didn't develop and so I don't really know it. Still
trying to figure it out.

5) I did a break and checked it and the values are all correct

6) What I mean is when I push the button that causes this code to be run the
DB just freezes and appears to stop responding. I let it go for over 15
minutes when it usually takes less than 30 seconds. I can do a CTRL Break
and the debug will be on the line that I noted. All the data appears to be
correct, but perhaps the syntax is not.

Thanks.

No idea but here are some suggestions:

[quoted text clipped - 21 lines]

 

[Sylvain Lafontaine]

Here's a second shot:

1) not sure what you are asking me here but I will take a stab at it... I
will be sending a numeric value (1-5xx) into a field into the sql table.
This value is chosen from a drop down list and is a "rep number" or number
assigned to a sales rep. The code evenly give the rep an even percentage
of
all available leads by source. So if he asks for 10 leads and I have 50
of
source a and 20 of source b and 30 of source c he will get 5 from a 2 from
b
and 3 from c.

I mean here that we're not interested in seeing the code involved in
building the SQL string to be sent as a parameter to the DoCmd.RunSQL
command but in the string itself. Here's an example of what you can do to
Copy&Paste this string here:

Dim SqlString

SqlString = "UPDATE clients SET salesperson = " & Text15 & " WHERE ID IN
( SELECT TOP " & NumLeads & " ID FROM clients WHERE Salesperson IS NULL And
[task note] = 'Unworked Lead' And Source = """ & CurrentSource & """) "

' Here it is: this is the interesting string value to show us!!!!
MsgBox SqlString

' Instead of displaying it in a message box, you can also print it to the
Immediate Debug Window;
' this make it easier to Copy&Paste.
Debug.Print SqlString

DoCmd.RunSQL SqlString

2) will try that

3) don't know what or where that is... will try to read up and find out.

One of the most important tool for helping you in
understanding/debugging/optimizing your queries against the SQL-Server.

4) Not sure what that is either... this module was written by my old IT
guy
and is the only module I didn't develop and so I don't really know it.
Still
trying to figure it out.

It's very important to learn what a Passthrough query is if you want to
build a good interface against a SQL-Server.

5) I did a break and checked it and the values are all correct

6) What I mean is when I push the button that causes this code to be run
the
DB just freezes and appears to stop responding. I let it go for over 15
minutes when it usually takes less than 30 seconds. I can do a CTRL Break
and the debug will be on the line that I noted. All the data appears to
be
correct, but perhaps the syntax is not.

Hum, maybe a locking problem. Try using a passthrough query instead of the
DoCmd.RunSQL. Adding the hint « with (NoLock) » might help (see Google) but
I'm not sure of your real problem here.

Also, very important: maybe you're blocked by a lock because you have one or
more sql-server tables opened in a datasheet window. You should do all of
your work with forms or forms seen as a datasheet but never with tables or
views directly opened as a datasheets.

Also, it appears that you are using AccessMonster.Com. This is a big
problem because you're not really posting at the right place. The newsgroup
that you're posting to is dedicated to ADP and SQL-Server and has nothing to
do with MDB or ACCDB database files and ODBC Linked Tables to a SQL-Server.
However, AccessMonster.Com makes a pretty good job of hiding the real
newsgroup that you're posting to. By posting to a more relevant newsgroup
such as microsoft.public.externaldata or microsoft.public.odbcclientsvr or
even microsoft.public.general, you will increase your chance of getting the
right answer to your problem.

Thanks.

No idea but here are some suggestions:

1- Show us the final result for the string that will be used as a
parameter
to the DoCmd.RunSQL command instead of a bunch of VBA code.

2- Use single quotes (') instead of double quotes (") around
CurrentSource.

3- Use the SQL-Server Profiler to see what command exactly Access is
sending
to the sql-server.

4- Instead of DoCmd.RunSQL; you should use a Passthrough query for this
kind
of stuff.

5- Make sure that no null value if returned for the ID in the subquery.

6- And finally, what do you mean exactly with the words « It is hanging
and
locking up on this line ». Do you mean that no answer is returned from
SQL-Server or that the debugger in Access open on this line and tell you
that there is an error or something else?

I have this code...

[quoted text clipped - 45 lines]

Any ideas why?

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain aei ca (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)