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]

So, for what it's worth I find that this works for me in only a fraction of a second (more or less instantly):

Sub foo()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim timeStart As Date
Dim timeEnd As Date

timeStart = Now

Set cn = New ADODB.Connection
With cn
    .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myTemp\db1.mdb;Persist Security Info=False;"
    .Open
    .Execute "SELECT * INTO TEST FROM Table1"
End With

Set rs = New ADODB.Recordset
With rs
    .Open "TEST", cn
    If Not .EOF Then
        Do While Not .EOF
            Debug.Print .Fields(0).Value
            .MoveNext
        Loop
    End If
End With

If rs.State And ObjectStateEnum.adStateOpen Then
    rs.Close
End If
Set rs = Nothing

If cn.State And ObjectStateEnum.adStateOpen Then
    cn.Close
End If
Set cn = Nothing

timeEnd = Now

Debug.Print timeStart
Debug.Print timeEnd

Debug.Print Format(CDate(timeEnd - timeStart), "hh:nn:ss")

End Sub

The above code is what I mentioned originally: create the table, then demonstrate that it is real by using it immediately.