Wait until ADODB connection execute

[Ombir]

Hi Friends,


I'm using below code to get count of records in access table which has been updated through sql server through ADODB connection (lcnn).

    SQL1 = "INSERT INTO [Users] " & _
    "Select DISTINCT USER_ID " & _
    "FROM " & _
    "[ODBC;Driver=SQL Server; " & "SERVER=Destiny\SQLEXPRESS;DATABASE=Sample;" & "UID=sam;PWD=password;]." & _ "[User_login] "
    lcnn.Execute SQL1
    
    Sleep 5000
    
    usercnt = DCount("*", "users")
    
    If usercnt >= 20 Then
       'Do something
    End If

The problem I'm facing is usercnt isn't get updated without delay of 5 secs. Code works fine in debug mode without using Sleep Api.

Can somebody help me with faster alternative (preferably without using recordset object) by which I can avoid 5 secs delay ??

 

[xenou]

I Haven't seen the construction/syntax you are using where you create a connection but also put connection information into the SQL string as well. I don't believe it's necessary to wait 4-5 seconds for DCount() but I'm still not sure how many databases you are using, since you seem to have two connection strings in use, plus the database that you are running this from (?) so that could be 3 databases, possibly - extractor.mdb, the sample database on server destiny, and the one that you are running your code from (if its not extractor.mdb).

You missed the simplest option, in my opinion:
select count(*) from Users

Note that if you are connecting to a remote server, you will have to wait some amount of time.

 

[Micron]

Re: #2 - there's no .Recordsaffected property for a Select statement. Now I have no idea what it is you want; only what you don't want.