This example uses the DefaultType property to predetermine what type of Workspace object will be created when you call the CreateWorkspace method. The TypeOutput function is required for this procedure to run.
Sub DefaultTypeX()
Dim wrkODBC As Workspace
Dim wrkJet As Workspace
Dim prpLoop As Property
' Set DefaultType property and create Workspace object
' without specifying a type.
DBEngine.DefaultType = dbUseODBC
Set wrkODBC = CreateWorkspace("ODBCWorkspace", _
"admin", "")
Debug.Print "DBEngine.DefaultType = " & _
TypeOutput(DBEngine.DefaultType)
With wrkODBC
' Enumerate Properties collection of Workspace object.
Debug.Print "Properties of " & .Name
On Error Resume Next
For Each prpLoop In .Properties
Debug.Print " " & prpLoop.Name & " = " & prpLoop
If prpLoop.Name = "Type" Then Debug.Print _
" (" & TypeOutput(prpLoop.Value) & ")"
Next prpLoop
On Error GoTo 0
End With
' Set DefaultType property and create Workspace object
' without specifying a type.
DBEngine.DefaultType = dbUseJet
Set wrkJet = CreateWorkspace("JetWorkspace", "admin", "")
Debug.Print "DBEngine.DefaultType = " & _
TypeOutput(DBEngine.DefaultType)
With wrkJet
' Enumerate Properties collection of Workspace object.
Debug.Print "Properties of " & .Name
On Error Resume Next
For Each prpLoop In .Properties
Debug.Print " " & prpLoop.Name & " = " & prpLoop
If prpLoop.Name = "Type" Then Debug.Print _
" (" & TypeOutput(prpLoop.Value) & ")"
Next prpLoop
On Error GoTo 0
End With
wrkODBC.Close
wrkJet.Close
End Sub
Function TypeOutput(intTemp As Integer) As String
If intTemp = dbUseJet Then
TypeOutput = "dbUseJet"
Else
TypeOutput = "dbUseODBC"
End If
End Function