ADO Connection Strings in VBA for Microsoft SQL

SUMMARY

This article describes the information needed for a pojectneeds 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 codethe 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
ActiveX DLL for Retrieving Login Information.


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