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 ??

 

[Micron]

maybe try this before getting the user count
DoCmd.SelectObject acTable, "YourTable", [0,1]
DoCmd.Requery
where 0, 1 without brackets specifies if the object is already open (0) or it's not (1)
Not sure if you want the table to be popping into view, which this might cause, nor do I completely understand if you want the count of records affected by the execute, or just the table total, so the following may not help:

I've read that this is supposed to work even though there's no RecordsAffected property for an ADODB execute

Dim RecordsAffected As Long
'build SQL1
lcnn.Execute SQL1, RecordsAffected

but I've also read where that didn't work. The solution there might be to wrap the execute in a transaction:

Dim RecordsAffected As Long
'build SQL1
With lcnn
  .BeginTrans
  .Execute SQL1, RecordsAffected
  .CommitTrans
End With
usercnt = RecordsAffected

Alternatively, use DAO (ADO is/has fallen out of favor) if you want the affected count, or use another recordset to get the count from the domain. There are other ways to create pauses without an API call, but I doubt that has anything to do with your issue.<code>

</code>

 

[Ombir]

maybe try this before getting the user count
DoCmd.SelectObject acTable, "YourTable", [0,1]
DoCmd.Requery
where 0, 1 without brackets specifies if the object is already open (0) or it's not (1)
Not sure if you want the table to be popping into view, which this might cause, nor do I completely understand if you want the count of records affected by the execute, or just the table total, so the following may not help:

I've read that this is supposed to work even though there's no RecordsAffected property for an ADODB execute

Dim RecordsAffected As Long
'build SQL1
lcnn.Execute SQL1, RecordsAffected

but I've also read where that didn't work. The solution there might be to wrap the execute in a transaction:

Dim RecordsAffected As Long
'build SQL1
With lcnn
  .BeginTrans
  .Execute SQL1, RecordsAffected
  .CommitTrans
End With
usercnt = RecordsAffected

Alternatively, use DAO (ADO is/has fallen out of favor) if you want the affected count, or use another recordset to get the count from the domain. There are other ways to create pauses without an API call, but I doubt that has anything to do with your issue.<code>

</code>

Thanks Micron for your valuable time and advice. I was trying to count the records affected by execute. However, I would be really interested to get total table count without Sleep api.

It tried your both suggestions.

Docmd.requery

works fine with debug mode but getting error otherwise. Records affected method works fine without any problem.

I would be really grateful if you could explain this

Alternatively, use DAO (ADO is/has fallen out of favor)

a little bit. Where to use DAO instead of ADO. What are its advantages. I've read ADO is newer model. So I'm Confused now ?


Thanks & Regards,
Ombir

 

[Micron]

ADO is newer and was intended to by M$oft to be the defacto standard but it fell out of favor due to a lack of 3rd party support or so I've heard. I've not done a whole lot of stuff with it since a lot of what I had to work with was ODBC, which ADO doesn't support according to here. It also doesn't support all of DAO's functionality, so AFAIC, what's the point for me to learn it?

I'm sure if you research it, you'll find plenty of info on why ADO is better. For me, it comes down to the tool needed for the job and I think I can count on one hand the number of times I've needed it - even if I was missing a couple of digits.

 

[xenou]

How did you define lcnn?

    lcnn.Execute SQL1

Did you set any properties when you create this object?

 

[Ombir]

How did you define lcnn?

    lcnn.Execute SQL1

Did you set any properties when you create this object?

Hi Xenou,

Please find below code that I used for making connection for Access Database. I am not so familiar with connection properties so didn't set up any.

    Set lcnn = New ADODB.Connection
    If lcnn.State = adStateOpen Then lcnn.Close
    lcnn.Open "Provider=Microsoft.jet.OLEDB.4.0;" _
    & "Data Source= " & test & "\extractor.mdb"

I also tried Micron solution

.Execute SQL1, RecordsAffected

to check affected records from sql server database using below connection string.

connstr = "Driver=SQL Server;Server=Destiny;DATABASE=Sample;UID=Sam;PWD=password;]

But this always returns -1.

 

[xenou]

Is the database server you are connecting to on a local network, or even on your local machine?

Also, what do you mean by this:

The problem I'm facing is usercnt isn't get updated without delay of 5 secs.

is the usercnt returning zero without the delay, or null, or is there an error, or a wrong count ... ? What exactly happens?

 

[Ombir]

Is the database server you are connecting to on a local network, or even on your local machine?

Sql Server is neither on local network nor local machine. It is located in some other location might be in different city or country. I installed Sql Express for testing purpose on local machine but same issue persists.

Yeah, usercnt returns 0 without delay if I use code as posted in Post 1. It returns correct count if I step through code by F8 key.

Further, If I use Micron code on Access database, it returns correct count. But If I use on Sql server it returns -1 if affected record count >=1.

Thanks for your support.

Regards,
Ombir

 

[xenou]

I guess I don't understand what you want to do.

You appear create a connection to an access database (another one -- extractor.mdb?), then pass a command to it that embeds a connection to a remote server? and then you want to count records in some local table?

How many databases are you working with? Where is the data?

 

[Ombir]

My data is in Sql server in a remote location. I want to excute a sql query in access vba on the sql server.

There are three approach as per my understanding:

1. Insert data from sql server into access table and count the records from access table using Dcount function. The problem with this
method is after the execution of sql query, delay of min 4-5 secs is necessary before I use the Dcount function to count records from access
table. Solution given by Micron works fine if I use below code:

SQL = "INSERT INTO anyaccesstable SELECT * FROM [ODBC;Driver=SQL Server;SERVER=Destiny;DATABASE=Sample;" & _
        "UID=Sam;PWD=Password;].[anysqltable] Where fieldname = 'xxx'"
With lcnn
  .BeginTrans
  .Execute SQL, RecordsAffected
  .CommitTrans
End With
usercnt = RecordsAffected

I don't want to insert the data into access table just to get the count affected.


2. Run a Select sql query with where condition on sql server instead of insert query and count the records affected using micron solution.

     Dim cnn As New ADODB.Connection
     Dim connstr As String
     connstr = "Driver=SQL Server;Server=Destiny;DATABASE=Sample;UID=Sam;PWD=password]"
     cnn.Open connstr

    Sql = "Select * from anysqltable where field = 'xxx'"

    cnn.execute sql, RecordsAffected

    usercnt = RecordsAffected

The problem with this method is that RecordsAffected always returns -1 in this case if result of sql query contains >=1 rows.


3. Dump data into recordset and count the records from recordset but I want to avoid this approach.

Hope I'm clear now.

Thanks for your time.