Page 536 - Microsoft Office Excel 2003 Programming Inside Out

Microsoft Office Excel 2003 Programming Inside Out

DBName = GetRegistryValue("DBName", "<enter database name>")
DBPassword = GetRegistryValue("DBPassword", "<enter password>")
DBServer = GetRegistryValue("DBServer", "<enter database server>")
DBUserId = GetRegistryValue("DBUserId", "<enter userid>")

If Len(DBServer) = 0 Then
DBServer = "localhost"

End If
If Len(DBName) <> 0 Then
c = "Provider=SQLOLEDB.1"
c = c & ";Data Source=" & DBServer
c = c & ";Initial Catalog=" & DBName

If DBWindowsAuth Then
c = c & ";Integrated Security=SSPI"
ElseIf Len(DBUserId) <> 0 And Len(DBPassword) <> 0 Then
c = c & ";User ID=" & DBUserId
c = c & ";Password=" & DBPassword
Else
c=""
End If
End If

Case 2
c = GetRegistryValue("ConnectionString", "<enter connection string>")

End Select
BuildConnectionString = c

End Function
After setting c to an empty string, the routine grabs the type of database from the registry and
stores it in DBType. This variable is then incorporated into a Select Case statement, which
builds different connection strings depending on the type of database being used.
If an Access (Jet) database is being used, the DBPath value is extracted from the Windows
registry using the GetRegistryValue helper function. This function takes two parameters, the
key and the default value. If the value extracted from the registry is different than the default
value, an empty string will be returned.

Chapter 24

510
Part 6: Excel and the Outside World: Collaborating Made Easy