vb123.com

Garry Robinson's Popular MS Access, Office and VB Resource Site

Home Contact Us
Order our Software

RSS & Newsletter
Join our XML/RSS Newsfeed or sign up for our informative newsletter on Office Automation, Access and VB topics
Read More

Get Good Help
If you need help with a database, our Professionals could be the answer
Read More

Is Your Database Corrupt ?
If you have a corrupt database, Try our Access Recovery service

The Workbench Find out who has your database open, start the correct version of Access, easy compacting and backups, change startup options, mde compile, shutdown database Read and Download

The Toolshed
Searchable help file comprising of all the information at vb123.com plus hidden downloads etc. Read More

The Toolbox
Libraries of software that we regularly import into our projects. This is a newer version of the Toolshed More..

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

DryToast
Backup and query your BaseCampŽ projects
Read More

Datamining/Graphs
Explore your data with this versatile graphing and data mining shareware tool. Read More

Garry's Blog
Find out a few other things that Garry has been writing about Microsoft Access. Read more

About The Editor 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
Contact Us ...

Search ...

or try our new site built with SharePoint Designer
vb123.com.au

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.

Download It Here

Click 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

External References

Why not find out about Peter's Access Frontend Database Syncroniser at www.3rdmillennium.com.au