The
ProgrammingMSAccess.COM Site
A
vba Procedure that creates a new view IN PUBS FROM AN access project
'Connect the Access project to a copy of the Pubs
database
'Make sure the VBE window has a reference to the
ADODB library
Sub Maketitleview2()
Dim cnn1 As ADODB.Connection
Dim cmd1 As New ADODB.Command
'Create a pointer to the current
'project's connection
Set cnn1 = CurrentProject.Connection
'Set pointer to Command object
Set cmd1.ActiveConnection = cnn1
'Drop old version of view, if necessary
'Ignore the result error, if necessary
cmd1.CommandText = "DROP VIEW titleview2"
cmd1.CommandType = adCmdText
On Error Resume Next
cmd1.Execute
On Error GoTo 0
'Create new version of view
cmd1.CommandText = "CREATE VIEW titleview2 " & _
"AS SELECT titles.title, titleauthor.au_ord, authors.au_lname, " & _
"titles.price , titles.ytd_sales, titles.pub_id " & _
"FROM authors INNER JOIN " & _
"titleauthor ON authors.au_id = titleauthor.au_id INNER JOIN " & _
"titles ON titleauthor.title_id = titles.title_id"
cmd1.Execute
'Cleanup Connection object
cnn1.Close
Set cnn1 = Nothing
End Sub
Want 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.