VBA DB Connection Problem

[Daniel Lee]

Hello :
I am new to project professional VBA programming.
I need to adopt VBA to query SQL Server DB table "not projectserver table".
Is there any example to show how to open DB in project professional
by VBA?
Another question is how to config DB connection string in VBA
easily ?
After I complete VBA coding it will be transferred to customers' site
and the DB connection string will change. How could I make it change easily
when Code transfer.
Any comment will be very appreciated.

Daniel

 

[Carsten Petersen]

Hi Daniel,

The way I usualy connect to a database from VBA is by using an ADODB
connection, the code that creates the connections is:

'***************************************************************************
**
'General section
Public cn As New ADODB.Connection
'***************************************************************************
**
Public Sub Make_Connection()

Dim srv, db As String

'***************************************************************************
**
'***** String values
'***************************************************************************
**
srv = "PRJSERVER"
db = "ProjectServer"
'***************************************************************************
**
'***** Establish connection
'***************************************************************************
**
Set cn = New ADODB.Connection
cn.Provider = "SQLOLEDB"
cn.Properties("Data Source").Value = srv
cn.Properties("Initial Catalog").Value = db
cn.Properties("Integrated Security").Value = "SSPI"
With cn
.ConnectionTimeout = 0
.CommandTimeout = 0
.Open
End With
End Sub
'***************************************************************************
**

At the customer site you'll just have to change the two string values.
Please notice that I'm using integrated security, which means that the users
have to be members of a database role that holds the rights to acces tables,
stored procedures, views etc.

When you are about to query the database table, you just call the above
routine. The following code shows as an example how to retrieve the PROJ_ID
from the MSP_PROJECTS table.

****************************************************************************
*
Dim rs As New ADODB.Recordset
Dim ProjID as Integer
strSQL = "Select Proj_ID from MSP_Projects Where Proj_Name='" &
CStr(ActiveProject.Name) & "'"
rs.Open strSQL, cn, adOpenStatic, adLockReadOnly
ProjID = rs.Fields(0)
rs.Close
****************************************************************************
*

I hope this helps.

BR
Carsten

 

[Daniel Lee]

Hello Carsten:

Thanks for your reply. I will try it as soon as possible.
I had one "access VBA programming" books on hand and I tried to transfer the
book's DB open example to project VBA.
I found the folloing declaration
"Public cn As New ADODB.Connection"
works fine in access but got compile trouble in project VBA.
Did I miss anything ?


Regards,
Daniel Lee

 

[Carsten Petersen]

Hej Daniel,

I forgot to mention that you need to reference Microsoft ActiveX Data Object
2.x Library. The references can be found in the VBA editor Tools =>
References.

That should work.

BR
Carsten