ADO Execute Method not adding to TablDefs Collection

[umdjb07]

Hello,

I have an issue with VBA where I am using the Connection.Execute method to add a table to my database. When I go to run the code the table is not being added to the TableDefs collection. However, when I hit the save command in the VBE or I open and close the Navigation Pane the table gets added to TableDefs! DoCmd adds the table just fine to the TableDefs. Can anyone help? Below is the code

Dim conn As ADODB.Connection, strConn As String


Set conn = New ADODB.Connection


strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=" & CurrentDb.Name


conn.Open strConn

with conn
' This is the code that I want to use to create a new table from an existing table
' but the code does not add to Access until I Save the code or hide/unhide the Nav Pane
 .Execute "SELECT * INTO tbl_test1 FROM tbl_test_data", , adCmdText
End With


conn.Close
Set conn = Nothing


End Sub

I have tried everything from TableDefs.Refresh to RefreshDatabaseWindow, but have had no such luck.

 

[xenou]

I wouldn't worry about not "seeing" the table as long as it is there. You can test your result by selecting from the table, not by looking for it in the navigation pane.

 

[umdjb07]

Thanks Xenou but I figured out the problem. For some reason there is a lag or delay when using ADO from when the SQL is executed to when Access adds it to the tabledefs collection and makes it available for use. I solved it by using a Do Until Loop counter, waiting for about 5-10 seconds and then the table shows up in the tabledefs collection and the Nav pane when you use refreshdatabsewindow method.

The lag is greater with ADO than DAO but a delay exists for both.

 

[xenou]

As I said, test by using the table, not by looking for it in the navigation pane. You can also work around any possible (?) delays by using an existing table rather than creating a new table (which I always do myself): just first delete all rows in the "temp" table, then insert your data into it and you are good to go.

 

[umdjb07]

Your idea doesn't work because as I said the table wasn't being added to the tabledefs collection due to the lag. If the table isn't in the tabledefs collection then testing for the table is moot. There was a lag from access adding it to tabledefs.

 

[xenou]

I am waiting for you to test your assertion by including a select query in the code after the table is created. I don't know about the tabledefs collection because your code doesn't use the tabledefs collection directly (and as I've said I'm not drawing any conclusions based on what you see in the navigation pane, except the most obvious one which is that you don't see the table in the navigation pane). If you don't care to test as I've suggested then I assume that means you are okay with the "lag".

 

[james_lankford]

Your idea doesn't work because as I said the table wasn't being added to the tabledefs collection due to the lag. If the table isn't in the tabledefs collection then testing for the table is moot. There was a lag from access adding it to tabledefs.

yeah, I don't believe that at all

talking about "lag" in an age where CPUs do 3,400,000,000 clock cycles per second just makes no sense to me

 

[umdjb07]

Well, it's true. The clock cycles of the cpu have nothing do with the "lag". I think it's the way ado behaves in access. As I said the docmd function added the table in zero time. DAO was faster but still a slight lag. Ado took about 5 seconds.

 

[umdjb07]

I will test it later when I get off work. But I think the lag is unavoidable and part of ADO. Or maybe I need s reinstall of access.

 

[xenou]

In my experience lag is not a normal part of using ADO. That's why I'm thinking in other directions on what is happening.