ADO Connection Strings in VBA for Microsoft SQL

Article ID: 851887
Expand all | Collapse all

TechKnowledge Content

SUMMARY

This article describes the information needed for a poject needs to be displayed on a particular window will have to be retrieved through an ADO connection.

MORE INFORMATION

The first option would be to hard code the User ID, Password, Database name and the Data Source name. An example of this would be as follows:
(This connection string would be all on one line in VBA)

.ConnectionString="Provider=SQLOLEDB;DSN=eEnterprise;User=sa;Password=;InitialCatalog=TWO"

The DSN would be the ODBC data source that is setup to the SQL Server. In this example the password is blank. The Initial Catalog is the name of the database where the data is located.

If do not want to hard code this information, there is a dll file that can be used to retrieve all this information. This .dll file can be found on PartnerSource or CustomerSource under Downloads & Updates | Product Releases | Continued in eEnterprise Product Releases | Modifier/VBA Samples. The file is called .

The readme file that is included will show you how to declare the variables to use the dll file. An example of code would declare variables and then use the variables in the connection string.

Dim userinfo As Object
Dim luserid As String
Dim lsqlpassword As String
Dim lintercompanyid As String
Dim lsqldatasourcename As String

This code would be on a window open event.

Set userinfo = CreateObject("RetrieveGlobals.retrieveuserinfo")
luserid = userinfo.retrieve_user()
lsqlpassword = userinfo.sql_password()
lintercompanyid = userinfo.intercompany_id()
lsqldatasourcename = userinfo.sql_datasourcename()

Then in the connection string you would use the variables.
(This .connection string would be all on one line in VBA)

.ConnectionString="Provider=SQLOLEDB;DSN="+lsqldatasourcename+";UserID="+luserid+";password="+lsqlpassword+";Initial Catalog="+lintercompanyid

Using this type of connection string with the retrieve.dll, it will retrieve the user's name, password, the company they are logged into and the datasource they are using.

This article was TechKnowledge Document ID: 22116

Properties

Article ID: 851887 - Last Review: October 15, 2011 - Revision: 3.0
Keywords: 
kbmbsmigrate kbretire KB851887
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com