Connection string from excel

Question

0.00/5 (No votes)

See more:

, +

I need to connect from excel vba to Sql server 2008 r2. I have written a function in excel module but I get a False in excel cell However the mesgbox writtens the value 1
But in the front end excel when I call this function =ConnectDB("Server","DB","Sa","xxx") it returns the value false

VB

Function ConnectToDB(Server As String, Database As String, User As String, Pwd As String) As Boolean
 
    Set Conn = New ADODB.Connection
    Set objRst = New ADODB.Recordset
    On Error Resume Next
Conn.ConnectionString = "Provider=SQLOLEDB.1;Server=" & Server & "; Database=" & Database & ";User ID=" & User & ";Password=" & Pwd & ";"
Conn.Open
Msgbox(Conn.State)

    End Function

What I have tried:

I tried Declaring the Conn has Dim variables still by declaring Option Explicit.
Tried Debugging the values are passed correctly from the front end (i.e from excelvalue to function parameters)

Posted 2-Jun-16 1:04am

ShaHam11

Updated 2-Jun-16 2:53am

v3

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2016-06-02T02:53:00

This function will always return false because you are not returning any value from it.

Try returning True if the State is 'Connected'

[EDIT] - To check if the State is connected use the following

VB

If (Conn.State And adStateOpen) = adStateOpen Then
    Return True
Else
    Return False
End If

[EDIT] OP still struggling...
Part of the problem here appears to be the connection string to the database. It looks like a mixture of OLEDB and ODBC. If possible use Windows Authentication as recommended by MS. But the connection string needs tidying up anyway.

Apologies for misleading you above with Return True - I forgot that VBA struggles to get up to even the dizzy heights of VB6 syntax. The following function works - note I've given two versions of the connection string

VB

Function ConnectToDB(Server As String, Database As String, User As String, Pwd As String) As Boolean

    Set Conn = New ADODB.Connection
    On Error Resume Next
    
    'This version if you have user id and password
    Conn.ConnectionString = "Provider=SQLOLEDB;Data Source=" & Server & "; Initial Catalog=" & Database & "; UserId=" & User & "; Password=" & Pwd & ";"
    'This way for the recommended approach of using Windows authentication
    Conn.ConnectionString = "Provider=SQLOLEDB;Data Source=" & Server & ";Initial Catalog=" & Database & ";Integrated Security=SSPI;"
    
    Conn.Open
    If (Conn.State And adStateOpen) = adStateOpen Then
        ConnectToDB = True
    Else
        ConnectToDB = False
    End If
 
End Function