Prompt the user for database properties: User, Password, Version

Summary

Note:
This article pertains to ArcGIS versions 8.x and 9.x. Later versions of ArcGIS may contain different functionality, as well as different names and locations for menus, commands and geoprocessing tools.

When you create a database connection in ArcCatalog, you are asked whether you want to save the connection properties. If the properties are not saved, you are prompted to provide connection properties whenever you make a database connection. This article outlines how to force the same prompt programmatically.

Note: 
Support for Visual Basic for Applications (VBA) for ArcMap and ArcCatalog ended with the ArcGIS 10.2.2 release, and Esri has not included VBA compatibility setups since version 10.5. See: ArcGIS Desktop and VBA Moving Forward

Procedure

1. Start ArcMap.
2. Create a new UIButtonControl: How To: Create a new UIButtonControl​

Note:
For more information on creating a UIControl, see the ArcGIS Desktop Help topic: 'Creating custom commands with VBA and UI Controls'

3. Right-click the UIButtonControl and select View Source.
4. Copy this code into the UIButtonControl's click event.

Dim pWF As IWorkspaceFactory
Dim pPropSet As IPropertySet

Set pWF = New SdeWorkspaceFactory

Set pPropSet = New PropertySet
pPropSet.SetProperty "SERVER", "myserver"
pPropSet.SetProperty "INSTANCE", "nnnn"

' ***** Omit references to SetProperty when you want to prompt for user input
'pPropSet.SetProperty "USER", "sde"
'pPropSet.SetProperty "PASSWORD", "sde"
'pPropSet.SetProperty "DATABASE", "db"  'SQL Server and Informix only
'pPropSet.SetProperty "VERSION", "SDE.DEFAULT"

pWF.Open pPropSet, 0
MsgBox "Finished"

5. Modify the code for your RDBMS. Click the UIButtonControl to test.

Note:
Experiment with the code. Uncomment those lines of code which set the properties for which you don't wish to prompt the user. For example, to hard-code the version yet still force a prompt for User and Password, then uncomment the SetProperty for VERSION.