VBA/Excel/Access/Word/Access/Workspaces
Содержание
- 1 Closes the Workspace
- 2 Closing a Database
- 3 Get database from Workspace
- 4 Get Workspaces count and name
- 5 List group user
- 6 Lists the tables in the LIBRARY database
- 7 Makes the admin account the owner of the new workspace.
- 8 Open an Access database on the server
- 9 Opens Database in exclusive mode with read/write access
- 10 Reference database from DBEngine
- 11 Reference recordset and table with DBEngine
- 12 To show that the CurrentDb function adds to the Databases collection
- 13 Use DBEngine to reference tables and its columns
Closes the Workspace
<source lang="vb">
Sub closeWorkspace()
Dim myWorkspace As Workspace Set myWorkspace = DBEngine.Workspaces(0) myWorkspace.Close
End Sub
</source>
Closing a Database
<source lang="vb">
Sub close()
Dim myWorkspace As Workspace Dim myDatabase As Database Set myWorkspace = DBEngine.Workspaces(0) Set myDatabase = myWorkspace.OpenDatabase(Name:= "\\server\database\Testing.mdb",Options:=True, ReadOnly:=False) myDatabase.Close
End Sub
</source>
Get database from Workspace
<source lang="vb">
Sub exaCurrentDB()
Dim db, dbExtra, dbOriginal As Database Dim i As Integer Set dbOriginal = DBEngine(0)(0) Debug.Print DBEngine.Workspaces(0).Databases.Count Set dbExtra = CurrentDb() Debug.Print DBEngine.Workspaces(0).Databases.Count For Each db In DBEngine.Workspaces(0).Databases Debug.Print db.Name Next db dbExtra.Close
End Sub
</source>
Get Workspaces count and name
<source lang="vb">
Sub test()
MsgBox "Workspace Count: " & DBEngine.Workspaces.Count MsgBox "Workspace Name: " & DBEngine.Workspaces(0).Name
End Sub
</source>
List group user
<source lang="vb">
Public Sub ListGroupsForUser()
Dim myWorksheetNew As Workspace Dim grpUG As Group " user group Set myWorksheetNew = DBEngine.CreateWorkspace("AdminWorkspace", "Admin", "") Debug.Print "Groups for user " & CurrentUser() & " are:" For Each grpUG In myWorksheetNew.Users(CurrentUser()).Groups Debug.Print grpUG.Name Next myWorksheetNew.Close Set myWorksheetNew = Nothing Set grpUG = Nothing
End Sub
</source>
Lists the tables in the LIBRARY database
<source lang="vb">
Sub exaTables()
Dim dbLibrary As Database Dim tblLibrary As TableDef Set dbLibrary = DBEngine.Workspaces(0).Databases(0) Debug.Print "Tables in LIBRARY" For Each tblLibrary In dbLibrary.TableDefs Debug.Print tblLibrary.Name Next tblLibrary
End Sub
</source>
Makes the admin account the owner of the new workspace.
<source lang="vb">
Sub admin()
Dim myWorkspace As Workspace Set myWorkspace = CreateWorkspace(Name:="Workspace2",UserName:="admin", Password:="", UseType:=dbUseJet)
End Sub
</source>
Open an Access database on the server
<source lang="vb">
Sub work()
Dim myWorkspace As Workspace Dim myDatabase As Database Set myWorkspace = DBEngine.Workspaces(0) Set myDatabase = myWorkspace.OpenDatabase(Name:= "\\server\database\Testing.mdb", Options:=True, ReadOnly:=False)
End Sub
</source>
Opens Database in exclusive mode with read/write access
<source lang="vb">
Sub work()
Dim myWorkspace As Workspace Dim myDatabase As Database Set myWorkspace = DBEngine.Workspaces(0) Set myDatabase = myWorkspace.OpenDatabase(Name:= "\\server\database\Testing.mdb", Options:=True, ReadOnly:=False)
End Sub
</source>
Reference database from DBEngine
<source lang="vb">
Sub exaObjVar()
Dim ws As Workspace Dim dbLib As Database Dim tdfBooks As TableDef Set ws = DBEngine.Workspaces(0) Set dbLib = ws.Databases![d:\library.mdb] Set tdfBooks = dbLib.TableDefs!BOOKS Debug.Print tdfBooks.RecordCount
End Sub
</source>
Reference recordset and table with DBEngine
<source lang="vb">
Sub exaDefaultCollections()
Debug.Print DBEngine.Workspaces(0).Databases![d:\dbase\library.mdb].TableDefs!BOOKS.RecordCount Debug.Print DBEngine(0).Databases![d:\dbase\library.mdb].TableDefs!BOOKS.RecordCount Debug.Print DBEngine(0)![d:\dbase\library.mdb].TableDefs!BOOKS.RecordCount Debug.Print DBEngine(0)![d:\dbase\library.mdb]!BOOKS.RecordCount Debug.Print DBEngine(0)(0)!BOOKS.RecordCount
End Sub
</source>
To show that the CurrentDb function adds to the Databases collection
<source lang="vb">
Sub exaCurrentDb()
Dim db, dbExtra, dbOriginal As Database Dim str As String Dim i As Integer Set dbOriginal = DBEngine(0)(0) Debug.Print DBEngine.Workspaces(0).Databases.Count Set dbExtra = CurrentDb() Debug.Print DBEngine.Workspaces(0).Databases.Count For Each db In DBEngine.Workspaces(0).Databases Debug.Print db.Name Next db dbExtra.Close
End Sub
</source>
Use DBEngine to reference tables and its columns
<source lang="vb">
Sub exaCollections()
Dim colParent As New Collection Dim colChild As New Collection Dim tdfBooks As TableDef Dim objVar As Object Set tdfBooks = DBEngine(0)(0).TableDefs!BOOKS colParent.Add colChild colParent.Add tdfBooks Debug.Print colParent.Count For Each objVar In colParent If TypeOf objVar Is Collection Then Debug.Print "Collection" ElseIf TypeOf objVar Is TableDef Then Debug.Print objVar.Name End If Next
End Sub
</source>