This is probably a simple request, and I'm sure I used to have the code to do it, but I've lost it and can't find it on the web. Basically, I need a query that returns a list of tables in the current database. Can anyone please help?
Hmmm, I'm having a few problems. When my databse closes, I want to delete any import error tables generated by the user importing data during a session.
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim lngCounter As Long
Set cnn = CurrentProject.Connection
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open "qryLocalTables", cnn, adOpenStatic, adLockReadOnly, adCmdStoredProc
If rs.EOF <> True Then
For lngCounter = 1 To rs.RecordCount
DoCmd.DeleteObject acTable, rs("name")
Set rs = Nothing
Set cnn = Nothing
I saved your query as qryLocalTables. However, when I execute the code, rs.RecordCount never returns anything other than zero. If I double click on qryLocalTables I get 2 rows. I tried using another query to test my code was correct, and it returns rows without problem from other queries.