Next Tip  Setting Database Properties In External Databases

This really tricky question was posed in Issue 12 of our Access Unlimited magazine.  There was 3 good answers to this question with the first one being the correct one for this problem. One of the others is featured in the download database.

In A97 how do you create the DB Title property (and  others) in a DB that is created in code as part of an application. The DB is empty when initially created and has no properties set and indeed some don't even exist until they are initially set. It is easy enough to create the property in code from within the New DB itself, but the requirement is to create it in the New DB from the first DB immediately after the first database creates the New one. ???

The particular properties are those you see from the System tab in File|Properties menu.

Any insights into this would be appreciated.

Regards

Peter Hallinan <peter @ 3rdmillennium.com.au>

Answer 1  From Robert . Johanson @ Mims.com.au

To try this answer, copy the code below to a blank module, change the NewDb constant and open the debug window.  Now type   Call SetSubject into the window.

 

'Create DB and Set Document Container Properties
'======================================
Option Compare Database
Option Explicit
 
Const NewDb = "c:\temp\Newdb.mdb"
 
Function SetSubject()
'This demonstrates how to create a new database and set one
' of the Access specific SummaryInfo properties
 
Dim wsp As Workspace
Dim db As Database
Dim ctr As Container
Dim doc As Document
Dim blnReturn As Boolean
 
'This is the trick... use MSACCESS to create the new database.
'If you just use Jet (DAO ie CreateDatabase), none of
'the Access specific Containers will exist (and there
'apparently are no methods to create them with DAO).
 
Dim appAccess As New Access.Application
appAccess.NewCurrentDatabase NewDb
appAccess.CloseCurrentDatabase
 
'Now that they exist, you can use DAO to add the specific
' properties with conventional methods
 
Set wsp = DBEngine.Workspaces(0)
Set db = wsp.OpenDatabase(NewDb)
 
' Return reference to Databases container.
 
Set ctr = db.Containers!Databases
 
' Return reference to SummaryInfo document.
 
Set doc = ctr.Documents!SummaryInfo
 
'For example, set the Subject to "Business Contacts"
blnReturn = SetAccessProperty(doc, "Subject", dbText, _
"Business Contacts")
 
' Evaluate return value.
 
If blnReturn = True Then
  Debug.Print "Property set successfully."
Else
  Debug.Print "Property not set successfully."
End If
db.Close
Set db = Nothing
Set wsp = Nothing
End Function
Function SetAccessProperty(obj As Object, strName As String, _
intType As Integer, varSetting As Variant) As Boolean
'this is straight out of the CreateProperty Method
'documentation (ex MSDN)
 
Dim prp As Property
Const conPropNotFound As Integer = 3270
On Error GoTo ErrorSetAccessProperty
 
' Explicitly refer to Properties collection.
 
obj.Properties(strName) = varSetting
obj.Properties.Refresh
SetAccessProperty = True
ExitSetAccessProperty:
 
Exit Function
 
ErrorSetAccessProperty:
If Err = conPropNotFound Then
' Create property, denote type, and set initial value.
  Set prp = obj.CreateProperty(strName, intType, varSetting)
' Append Property object to Properties collection.
  obj.Properties.Append prp
  obj.Properties.Refresh
  SetAccessProperty = True
  Resume ExitSetAccessProperty
Else
  MsgBox Err & ": " & vbCrLf & Err.Description
  SetAccessProperty = False
  Resume ExitSetAccessProperty
End If
 
End Function
 

The other 2 good examples can be found in the answers database 

Notes On Answer 2

The following functions GetProperty_DAO and SetProperty_DAO can be used to either return or set the respective database properties. Each function returns a boolean value indicating the success or failure of the process.

The SetProperty_DAO function has an optional parameter of the path and
name of the database to set the property for. If omitted it uses the current
database.

Note also that they are DAO functions and will require modifications for an
Access 2000 ADO database.

Simply copy this module into any new project, set those properties in
'SetStartupProperties' and comment out the others, then run the routine
SetStartupProperties.

  Your Sample Database Is Called   "dbproperties.zip"

The sample database is included in the zip file

If you do NOT own "The Toolbox", Click here to find out how to purchase The Toolbox.



Click Next Tip for the next help page or try these other advanced pages

Related Pages On This Site

Compiled Access Databases
Checking a report property for all Microsoft Access Reports
Transaction Queries

 

Our Tools and Resources

  • RSS & Newsletter Here
    Join our newsfeed or sign up for our informative newsletter on Office Automation, Access and VB topics

  • Smart Access is online 
    The best magazine written about Microsoft Access is now being transferred to the web. There are 400 articles written by a 100 authors in the collection. 

    See the Smart Access 2010 specials here

  • The Workbench
    Find out who has your database open, start the correct version of Access, easy compacting and backups, change startup options, creation versions,  shutdown database

  • Read about the Toolbox
    Sample downloads, library resource kit and searchable help file comprising most of the information at vb123.com.au plus hidden downloads etc.

  • Convert Access to SQL Server  
    Upsize to SQL Server 2005 or 2008, easily repeated conversions, highly accurate SQL query  translation and web form conversion.

 

 

vb123 Professionals


Get Good Help Here

If you need help with a database or Office programming, our Professionals could be the answer because we have worked on many similar solutions


Frontpage Conversions
We have converted vb123.com to Expression Web, contact us if we can help you move to the latest Microsoft web tool.


About The Editor ~ Contact Us
Garry Robinson writes for a number of popular computer magazines, is now a book author and has worked on 100+ Access databases. He is based in Sydney, Australia

Access 2003 Security

MS Access Security

Read More here