VBA中的ADODB连接问题

问题描述：

我有一个VB.NET函数，我想连接它用于连接到SQL Server数据库并返回记录集。我是VB和SQL的新手，所以我希望这很简单，但我确信我的连接字符串和SQL查询是正确的，因为他们是由队友检查的。我有异常信息的副本VBA中的ADODB连接问题

' Retrieve Record Set 
Private Function GetRecords() As ADODB.Recordset 
    Dim conn As New ADODB.Connection 
    Dim rs As New ADODB.Recordset 
    With conn 
     .ConnectionString = "Server=12.345.678.9;Database=db01;User Id=userid01;Password=passwordyword" 
     .Open() ' Exception Thrown Here 
     rs.Open("SELECT * FROM [Table] ;", "sql") 
     rs.Close() 
     .Close() 
    End With 
    Return rs 
End Function

异常消息的代码如下：

An exception of type 'System.Runtime.InteropServices.COMException' occurred in ThisProject.dll but was not handled in user code 
Additional information: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

任何和所有帮助将不胜感激！谢谢！

在您的连接字符串中，您必须提供您使用哪个驱动程序？例如oledb，oracleoledb等.. –

答

在这里你试试。

m_sConnStr = "Provider='SQLOLEDB';Data Source='MySqlServer';" & _ 
"Initial Catalog='Northwind';Integrated Security='SSPI';"

Connection String for VBA

答

这应该工作。请注意，连接也需要通过rs.Open

Private Function GetRecords() As ADODB.Recordset 
    Dim conn As New ADODB.Connection 
    Dim rs As New ADODB.Recordset 
    With conn 
     .ConnectionString = "Driver={SQL Server};Server=12.345.678.9;Database=db01;Uid=userid01;Pwd=passwordyword;" 
     .Open() ' Exception Thrown Here 
     rs.Open("SELECT * FROM [Table] ;", conn) 
     rs.Close() 
     .Close() 
    End With 
    Return rs 
End Function

VBA中的ADODB连接问题

相关推荐