Excel中的连接字符串 [英] Connection string from excel

问题描述

我需要从excel vba连接到sql server 2008 r2.我已经在excel模块中编写了一个函数，但在excel单元格中却得到了False，但是mesgbox写入了值1
但是在前端excel中，当我调用此函数= ConnectDB("Server"，"DB"，"Sa"，"xxx")时，它将返回值false

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

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

我尝试过的事情:

我尝试通过声明Option Explicit来声明Conn仍具有Dim变量.
尝试调试从前端正确传递值(即，从excelvalue传递到函数参数)

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)

推荐答案

此函数将始终返回false，因为您没有从中返回任何值它.

如果状态为已连接"，请尝试返回True

-要检查状态是否已连接，请使用以下

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

Try returning True if the State is ''Connected''

- To check if the State is connected use the following

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

OP仍在苦苦挣扎...
问题的一部分似乎是数据库的连接字符串.它看起来像是OLEDB和ODBC的混合体.如果可能，请使用MS推荐的Windows身份验证.但是连接字符串还是需要整理一下.

抱歉在上面用Return True误导您-我忘记了VBA难以达到VB6语法的令人眩晕的高度.以下函数有效-请注意，我给出了两个版本的连接字符串

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

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

这篇关于Excel中的连接字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！