Connection refresh problem: can't see temporary table 
Author Message
 Connection refresh problem: can't see temporary table

Hi you all,

Although I keep in mind to operate through the same
connection when I create temporary tables the code that
follows just can't see them at all.  When I execute the hole
code in debug  mode (slower) it passes without problem.

I use .Tables.Refresh on that connection after each temp-table creation
I use OLE DB Services -1, (all)

Thanks for any help
Nicolas



Sat, 12 Jul 2003 21:02:43 GMT  
 Connection refresh problem: can't see temporary table


Fri, 19 Jun 1992 00:00:00 GMT  
 Connection refresh problem: can't see temporary table
In the article

http://support.microsoft.com/support/kb/articles/Q216/9/25.ASP

there is a very important passage:

NOTE: When using the ADO Data control in the manner described above, avoid
using the Refresh method:
ADODC1.Refresh
because this will result in a new connection. Use the Requery method of the
Recordset property instead:
ADODC1.Recordset.Requery

Simona



Quote:
> Hi you all,

> Although I keep in mind to operate through the same
> connection when I create temporary tables the code that
> follows just can't see them at all.  When I execute the hole
> code in debug  mode (slower) it passes without problem.

> I use .Tables.Refresh on that connection after each temp-table creation
> I use OLE DB Services -1, (all)

> Thanks for any help
> Nicolas



Sat, 12 Jul 2003 21:39:25 GMT  
 Connection refresh problem: can't see temporary table


Fri, 19 Jun 1992 00:00:00 GMT  
 Connection refresh problem: can't see temporary table
thank you.
I also found this article on the subject:Q200300 HOWTO: Synchronize Writes
and Reads with the Jet OLE DB Provider and ADO
(there is another one for DAO)

Nicolas



Sun, 13 Jul 2003 01:39:31 GMT  
 Connection refresh problem: can't see temporary table
This may be related... I am using the ADO extensions
ADOX to create tables
with code such as this, where:
catTempTable is an ADOX "catalog" connecting
to my local database,
tblTempFile is the ADOX "table", which I am appending
within that database, and rsLocalTable is a recordset
I want to open, in order to add records to that table.

In the following code,  inxCountOpenTries will typically
go to 700 to 1000 (on a 400 mhz P2) before the table
is recognized and "opened".  Note the call to the
catalogue's Table.Refresh method.
THERE IS NO Requery method for ADOX catalogues,
only Refresh

I have never found a workaround, and this code now exists in
my production code, until someone comes up with an explanation???

' the "table" tblTempFile (with the name "MYTABLE")
' is created and its fields added well
' before this, then it is  "appended" to the catalogue here:

catTempTable.Tables.Append tblTempFile

' initialize my recordset
On Error GoTo NO_TABLES
 Set rsLocalTable = New ADODB.Recordset
 rsLocalTable.CursorLocation = adUseClient

' now try to  open the table repeatedly
' until it actually opens:

 On Error Resume Next

  inxCountOpenTries = 0
 catTempTable.Tables.Refresh

 Do Until (rsLocalTable.State = adStateOpen)

        rsLocalTable.Open "MYTABLE" ,  _
                   myde.cnLocalTables,  _
                  adOpenForwardOnly,  _
                 adLockBatchOptimistic, adCmdTable

        inxCountOpenTries = inxCountOpenTries + 1
 Loop

Quote:

> Hi you all,

> Although I keep in mind to operate through the same
> connection when I create temporary tables the code that
> follows just can't see them at all.  When I execute the hole
> code in debug  mode (slower) it passes without problem.

> I use .Tables.Refresh on that connection after each temp-table creation
> I use OLE DB Services -1, (all)

> Thanks for any help
> Nicolas



Tue, 15 Jul 2003 04:30:19 GMT  
 Connection refresh problem: can't see temporary table
Hi,

I think that it is the same problem we have.
The fact is that we create a new table.
You with ADOX me with DDL (INSERT INTO).
The point is that from DAO we are used to think
"database" but with ADO we must think "Connection".
I finally resolved my problems as proposed in the article
Q200300 using the .RefreshCache method of the
JRO.JetEngine object.

Dim je As JRO.JetEngine
Dim str As String
Set je = New JRO.JetEngine
je.RefreshCache cnx

str = "SELECT ... INTO ... FROM ..."

cnx.BeginTrans
cnx.Execute str, , adCmdText + adExecuteNoRecords
cnx.CommitTrans

je.RefreshCache cnx

-code that follows uses the new table without problem.

I use the same connexion for the cration of the table
and for the query or the insert that follows and uses that
new table. I refreshcache the connection before and after the
creation of the table. In step by step mode when the cursor
passes on that refreshcache line you can almost hear
the disk drive flushing the new data to the database !

I hope it will help you too
Nicolas



Mon, 14 Jul 2003 23:59:25 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. seeing Temporary Tables between connections

2. Problem in generating Crystal Report with temporary tables(#table) in SP

3. ? Internet connection sharing, I've seen this posted several times by people

4. ? Internet connection sharing, I've seen this posted several times by people

5. ? Internet connection sharing, I've seen this posted several times by people

6. ? Internet connection sharing, I've seen this posted several times by people

7. 'Canned' data in VB program

8. can't create temporary table using ADO

9. SQL Server local temporary table problem

10. Problem "seeing" a new table

11. Bizarre problem - VB6 won't see one .EXE file but sees all others

12. DBCombo 'refresh' problem

 

 
Powered by phpBB® Forum Software