Looping through a list of tables?

[solo7]

I want to loop through a list of all the tables in a database, so I can back them all up instead of just 1.
How can I get a list of tables into an array?

solo7

 

[PHV]

Take a look at the TableDefs collection.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

i find the following info to be useful.

'Code Courtesy of
'Dev Ashish
'******************** Code Start ************************
Use the following SQL statements as Rowsource for the control depending on which object you want.

Queries:
SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND
(MSysObjects.Type)=5 ORDER BY MSysObjects.Name;

Forms:
SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND
(MSysObjects.Type)=-32768 ORDER BY MSysObjects.Name;

Tables:
SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND
(Left$([Name],4) <> "Msys") AND (MSysObjects.Type)=1 ORDER BY MSysObjects.Name;

Reports:
SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND
(MSysObjects.Type)= -32764 ORDER BY MSysObjects.Name;

Modules:
SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND
(MSysObjects.Type)= -32761 ORDER BY MSysObjects.Name;

Macros:
SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND
(MSysObjects.Type)= -32766 ORDER BY MSysObjects.Name;

 

[cmmrfrds]

As suggested you can use the 2 methods already listed. A couple more are to use the ADOX Catalog or use OpenSchema that is part of ADO. Example of OpenSchema.


Function TestTableColumn()

Dim cn As New ADODB.Connection, cn2 As New ADODB.Connection
Dim rs As Recordset, rs2 As Recordset
Dim connString As String
connString = "provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=\\bigtuna\Databases\bank.mdb;" & _
"Persist Security Info=False"
cn.ConnectionString = connString
''cn.Open connString
''cn2.Open connString

Set cn = CurrentProject.Connection
Set cn2 = CurrentProject.Connection

Set rs = cn.OpenSchema(adSchemaTables, _
Array(Empty, Empty, Empty, "table"))

While Not rs.EOF
Debug.Print rs!table_name
Set rs2 = cn2.OpenSchema(adSchemaColumns, _
Array(Empty, Empty, "" & rs!table_name & ""))
While Not rs2.EOF
Debug.Print " " & rs2!column_name
Debug.Print " " & rs2!data_type
Debug.Print " " & rs2!Description
Debug.Print " " & rs2!is_nullable
rs2.MoveNext
Wend
rs.MoveNext
Wend
rs.Close
Set cn = Nothing

End Function