How to

[micarex]

I am using VBA to automate the update of custom properties in the
shapesheet from a SQL datasource. I am running a VBA script in one VSD
file which sequentially opens about 60+ other VSD files and tries to
update the custom properties in the shape sheet for specific shapes
with values from an SQL database. The script works fine except for the
first time the database query is executed. Not surprisingly the
database wants a password and the query fails with an error saying it
can't find the table name in the data source. If step through the
debugger and manually type in the password when the prompt appears, the
code works fine.

Here's the value I have in the User.ODBCConnection property.

="ODBCDataSource=ServiceDeskProduction|ODBCQualifier=SMF_SDK_PRD|PWD=tiger|ODBCTable=dbo.v_configuration_item|1|search_code=Prop.search_code|
7|admin_workgroup_name=Prop.admin_workgroup_name=0|administrator_organization_nam=Prop.administrator_organization_nam=0|administrator_person_name=Prop.administrator_person_name=0|asset_id=Prop.asset_id=0|brand=Prop.brand=0|category=Prop.category=0|cpu_speed=Prop.cpu_speed=0|"

Can anyone tell me how I can include a password for the SQL database in
the User.ODBCConnection property so that the password prompt is skipped
and the connection goes through seamlessly? I can't find doco anywhere
on the format of this field.

 

[David Parker]

In a previous version of the Database Wizard, I got a 60 times speed
increase by writing my own VBA to perform the update. I still read the
User.ODBC cells to get the link information, and the field/custom property
mapping.
Using this approach you could avoid calling the Database Wizard altogether
and thus avoid calling its interface.