ADODB Connection

[dollygoh]

Hi,

I have created 2 connection db1 and db2. Some codes of my program are as follow:

db1.execute sql1 -----> 1st statement
db2.execute sql2 -----> 2nd statement

The task of the 1st statement is to import a table to another database.

The task of the 2nd statement is to do some alteration of the imported table.

So I have to ensure that the imported table is already in the other database after executing 1st statement & before executing the 2nd statement.
How can I ensure that the 1st statement has completed it actual execution before executing the 2nd statement ? Thanks in advance.

[tina104291325]

u can do that with many ways a simple one would be to try to open the created table with any object even with connection object like

cnn.execute "Select * from newtable"

if the table isn't there that will return an error

[randem]

dollygoh,


Maybe this will help, check the state of the recordset before moving to the next operation.

State Property
      
 
Describes for all applicable objects whether the state of the object is open or closed. 
 
Describes for a Recordset object executing an asynchronous method, whether the current state of the object is connecting, executing, or fetching.
 
Return Value
 
Returns a Long value that can be one of the following constants.
 
Constant Description 
adStateClosed Default. Indicates that the object is closed. 
adStateOpen Indicates that the object is open. 
adStateConnecting Indicates that the Recordset object is connecting. 
adStateExecuting Indicates that the Recordset object is executing a command. 
adStateFetching Indicates that the rows of the Recordset object are being fetched. 
 
 
Remarks
 
You can use the State property to determine the current state of a given object at any time. This property is read-only.
 
The Recordset object’s State property can have a combination of values. For example, if a statement is executing, this property will have a combined value of adStateOpen and adStateExecuting.

[dollygoh]

Hi,


I tried to check the current state of the connection to determine whether the 1st statement has finished it task but it don't works.
I hope to check whether the table is already imported to another database before execute the 2nd statement without needed to use error trapping and handling. Can anyone help ?

(Thanks for those who replied.)

[randem]

dollygoh,

What do you mean when you say doesn't work. You have to be clearer with your statements.

[dollygoh]

Sorry, i have to make myself clear. Maybe I post my code :

db1.Execute SQL1 'to import a table in current databaseA to
'another databaseB.

Do While db1.State = adStateOpen And db1.State = adStateExecuting
SQL2 = "....." 'Some sql statement to alter the table
db2.Execute SQL2
Exit Do
Loop

I hope that after executing the 1st statement (db1.Execute) and the imported table is already in databaseB then the 2nd statement (db2.Execute SQL2) is executed. This is because 2nd statement is to alter the table so the table must be available in databaseB before altering can be done.

db1 is the connection to databaseA
db2 is the connection to databaseB

I have also tried other condition in the do while loop such as :
* db1.State = adStateOpen And db1.State = adStateFetching
* db1.State = adStateOpen And db1.State = adStateConnecting
* db1.State = adStateOpen And db1.State <> adStateFetching
* db1.State = adStateOpen And db1.State <> adStateConnecting
* db1.State = adStateOpen And db1.State <> adStateExecuting

Hope I have make myself clear. Please help. Thank you.

[randem]

dollygoh,

The first thing I see is you kill the statement with the AND. You have to OR the flags togther. Your code should look something like this.

db1.Execute SQL1 
 
Do While db1.State = (adStateOpen or adStateExecuting)
   DoEvents
Loop 
 
db2.Execute SQL2 
 
 
or 
 
Do While (db1.State AND adStateExecuting )
   DoEvents
Loop

[dollygoh]

Hi randem,

it doesn't works even when I use brackets '()' as whenever the 2nd statement is executed, an error occurs stating that the imported table is not exists in databaseB. I need to ensure that
the imported table is already available in databaseB but it seem that none of the 'state' that you mention has determine that the table is exist in databaseB ?

[randem]

What is the value of db1.state when you get to the statement?

[dollygoh]

I got 1 for the state (i think 1 is for adstateopen) so my
do while condition will always be true if I use 'OR' and it will be false if I use 'And' . So in this case, it is a matter of whether the 2nd statement will be execute or not based on the 'OR' and 'And' and not due to whether the table is already in databaseB

[randem]

What does your SQL statement look like. Is it an action query...???. Post the code in question.

Dont forget to use

[vb*code]

your code

[/vb*code]


where vb*code actually will be vbcode. Use this to retain formatting.

[dollygoh]

My code is as follow :

[vb*code]

SQL1="SELECT TEST1.ID, TEST1.Date, TEST1.Name INTO TEST2 IN 'c:\databaseB.mdb' FROM TEST1"
db1.Execute SQL1

Do While db1.State = (adStateOpen And adStateExecuting)

SQL2="ALTER TABLE TEST2 ADD COLUMN Price CURRENCY"
db2.Execute SQL2

Exit Do
Loop

[/vb*code]

[randem]

dollygoh,


Use vbcode inside of the bracket not vb*code.


A couple of things, Why are you attempting to alter the table before the query is finished. That will not help in the long run. That part of your code should not be inside the Do loop as I had indicated on a prior post.

Each time through the loop you are attempting to alter the table, why? Are you not supposed to be waiting?

I will look into why you get just a state of Open. It may be local to MS Access.

db1.Execute SQL1 
 
Do While (db1.State and adStateExecuting) = adStateExecuting 
   DoEvents
Loop 
 
db2.Execute SQL2

[dollygoh]

Hi randem,

Thanks for making the effort to help me.

Yes, I was trying to wait for the 1st query to finish but it seem like I always get db1.state=1 so in my case, checking the connection state doesn't seem to work.

[randem]

dollygoh,

It just dawned on me (Duh), You are checking the connection state not the recordset state that is why you are getting just Open. This is correct the connection is Open not Executing. The state of Executing will only be present in a recordset state.

Since what you are doing is not a recordset, it is an action query, the state will not work for this. What you probably need to check is the RecordsAffected variable, that might work for you.

I will give you the code I use for this (Forget the TraceSQL lines, those are for my program tracing and debuging).

Public Sub ExecuteSQL(SQL As String)
 
    If TraceSQL Then WriteDebug SQL
    dbCurrent.Execute SQL, RecordsAffected
    If TraceSQL Then WriteDebug "Records Affected - " & RecordsAffected
    DoEvents
    
End Sub

[dollygoh]

Hi randem,

I have tried to check the 'RecordsAffected' variable but I
still got the same error, that is my imported table is not exists.
Immediately, when this 1st instruction is executed (db1.Execute
SQL1 RecordsAffected), I can get the no. of records which is
stored in this 'RecordsAffected' variable, but that does not mean
that the imported table in already in databaseB.

I think there are still some synchronization between my 2
database connection-db1 & db2. Below is my code & I hope
I am doing the right thing :

SQL1="SELECT TEST1.ID, TEST1.Date, TEST1.Name INTO TEST2 IN 'c:\databaseB.mdb' FROM TEST1" 
 
db1.Execute SQL1 RecordsAffected
 
Do While Not IsEmpty(RecordsAffected)
  SQL2="ALTER TABLE TEST2 ADD COLUMN Price CURRENCY" 
  Exit Do
Loop
  db2.Execute SQL2

[randem]

dollygoh,

This code you will have to modify, but this is what I use to get the table names from a database and add them to a combo box. You can scan the database until the table exist. The best way is to set up a timer to check every 5 seconds or so and to set an indicator when it exist.

In your code you loop until this indicator is set. This will definitely solve your problem without error trapping. Try this, if you need any assistance with this let me know.

Private Sub GetTableNames()
Dim TableTypes As Variant
Dim i As Integer
 
On Error GoTo GetTableNamesError
 
 '   TableTypes = Array("Table", "Link", "Pass-through")
    
    '====================================================================
    ' Collect all the table names in the database of the type we can use
    '====================================================================
    
    Set dbTable = dbCurrent.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "TABLE"))
'    Set dbTable = dbCurrent.OpenSchema(adSchemaTables)
    
    cboTableName.Clear
    
    With dbTable
    
       Do Until .EOF
          For i = 0 To UBound(TableTypes)
 '            If UCase(!table_type) = UCase(TableTypes(i)) Then
                cboTableName.AddItem !table_Name
 '               Exit For
 '            End If
          Next i
          .MoveNext
       Loop
      
      .Close
   End With
   
   cboTableName = GblContactTable
   cboTableName.Tag = GblContactTable
   Exit Sub
   
GetTableNamesError:
 
    Msg = "GetTableNamesError() - " & Err.Description & vbCrLf
    frmMain.ErrorLog Msg
    Exit Sub
    
End Sub