VBA/Excel/Access/Word/Access/Workspaces

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>