Rows Returned SQL Server Server? 1

[FinalPrime]

I need some help with SQL Server .
I'm still Access 2000 bound.

cn is Active Connection to Server
dtemp is Access Current()

rsServer is recordset to Server
rstAccess is recordset with Access
r.Open "tblAccess", CurrentProject.Connection, adOpenDynamic, adLockOptimistic

acct = rstAccess![AccountNumber]
sql = "SELECT tblOnServer.AccountID FROM tblOnServer "
sql = sql & "WHERE tblOnServer.ID ="&acct&" ;"

Set rsServer = New ADODB.Recordset
With rsServer
.ActiveConnection = cn
.CursorType = adOpenDynamic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Source = sql
.Open
End With
If <rsServer rows are returned> then
<do this>
else
<do that>
end if

In an Access ADO recordset, I would use the 'RecordCount' value. If it were zero, then
the above code would execute <do that>

However, I need to look for zero records returned with the rsServer recordset. If using COUNT to determine if > 0, I'd have to run a second SQL query to get the data?

I simply want to update an Access table if rows are returned in a SQL Server query.

Seems simply to me. However, I'm new to SQL Server coding.

Any help will be appreciated.

Thanks in advance,
FinalPrime
member S.A.G.A
(Society Against Geek Acronyms)

 

[SQLSister]

Ok you can check for existence using the Where exists structure or you can use @@Rowcount to find the exact number of rows returned. Use Books Online to see how to use these commands.

However, it is not a good idea to use cursors with SQL server as they are slow and very inefficient. Can you redo your process to use a set-based statment instead? Often this can be accomplished with if statements in the stored procedure or with the use of the case statement within the insert, select, delete, update statement itself. By replacing the cursor, you can often improve processing time from minutes to milliseconds.

Cursors should only be used if you actually need to process records individually and 80% or more of the time are only necessary for dba type functions (such as cycling through all the table names to run a systemn stored procedure on each one), not user functions. When accessing ordinary data, cursors are very rarely required.

 

[FinalPrime]

thanks very much for the cursor tips.
I'll digest them as i continue my intro to SQL Server.

I did however figure out a way to
handle the no-record-return test in the Post.

If rsServer.BOF = True Then
If rsSever.EOF = True Then
<do this since no records were returned>
End If
End If

thanks again
Final Prime