how to call a function in a make tabel query?

[PuddinPie]

Hello,

I don't know if it's possible but here goes. I have a function that list's all the tables in it's database. I want to take that list and turn it into a record set so I can add it to a veriable.
"ie. table1, table2, table."
"rs.recordset.next"
Or something like that where it will assign the first table name to a value and then when done move onto the next and so on untill its gone through all of the tables.
The easiest way I could think of was to have it all in a table and call the table as a record set and cycle through them but I don't know how to call the function I have in a make table query or SQL statement.

Any help would be nice and if there is a better way then please let me know.

Thank you.

 

[mdlueck]

... it will assign the first table name to a value and then when done move onto the next and so on untill its gone through all of the tables.

I have a FE DB table emptier which I harvested this code from...

  Dim adoRS As ADODB.Recordset
  Dim intCounter As Integer
  Dim strSQL As String
  Dim strThisTable As String

  'Define a query to look for FE DB temp tables
  strSQL = "SELECT M.[Name] " & _
            "FROM MSysObjects AS M " & _
            "WHERE (((M.[Name]) LIKE 'tmptblqry_%') AND ((M.[Type])=1));"

  'Define attachment to database table specifics
  Set adoRS = New ADODB.Recordset
  With adoRS
    .ActiveConnection = CurrentProject.Connection
    .CursorLocation = adUseClient
    .Open strSQL
    
    'Check to make sure we found records
    If adoRS.BOF Or adoRS.eof Then
      GoTo Exit_btnEmptyFETempTables_Click
    End If

    Do While Not adoRS.eof
      'Call the shared API
      dbutils_EmptyLocalTmpTbl (adoRS!Name)

      'Move onto the next record
      .MoveNext
    Loop
  End With

So that loops through all Access tables in the current database, skipping over linked tables and what not.

 

[vbaInet]

Could you tell us what purpose of the exercise is?

 

[PuddinPie]

I have an archive section for a split database. The archive copys some of the tables to an external archive database. I want to be able to run something afterwords that cycles through all of the tables copied and compaire the record count from the FE table to the archive table to make sure they match. I have the compaire down and working but I wanted to have it make a list of all of the tables and use a variable to run through them instead of writing out the code for them. The other reason for not wanting to put it all in manualy is that the EU will be able to create new tables and if it makes a list it will save me from having to go in each time and update the coding manually for each new table.

 

[PuddinPie]

Ok. I figured it out. I used the tabledefs to list the names and then wrote an if statement to exclude the tables I did not want it to check and the system tables.

 

[vbaInet]

You can exclude the ones you don't wish to see in MSysObjects using the IN() operator. Paste this into the the SQL view of a query and view the results:

SELECT MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Name) Not In ('[COLOR=Red]Table 1[/COLOR]', '[COLOR=Red]Table 2[/COLOR]')) AND ((MSysObjects.Type)=1));

You can include the tables you don't wish to see in the red part.