ProgrammingMSAccess.com

The ProgrammingMSAccess.COM Site

A vba Procedure that LISTs ALL THE tables and views in the current database connection for a .adp file; it uses the sp_Tables catalog system stored procedure to generate a return set with the desired information.

'This procedure runs with the default reference to the ADODB library

Sub ListAllTablesAndViews()
Dim cmd1 As ADODB.Command

Set cmd1 = New ADODB.Command
cmd1.ActiveConnection = CurrentProject.Connection

'If necessary, drop old version of temporary
'stored procedure, or ignore resulting error
cmd1.CommandText = "DROP PROCEDURE msp_MyTemp"
cmd1.CommandType = adCmdText
On Error Resume Next
cmd1.Execute
On Error GoTo 0

'Create a temporary stored procedure
cmd1.CommandText = "Create Procedure msp_MyTemp AS " & _
"EXEC sp_help"
cmd1.Execute

End Sub

What to understand  Microsoft Access 2000 so that you can program it to do more tasks like this?  Get Programming Microsoft Access 2000 by Rick Dobson from Microsoft Press.  Learn more about the book by clicking here.

Copyright 1999 CAB, Inc. All rights reserved. Republication or redistribution of CAB, Inc. content, including by framing or similar means, is expressly prohibited without the prior written consent of CAB, Inc. CAB, Inc. shall not be liable for any errors in the content, or for any actions taken in reliance thereon.