Excel电子表格到SQL数据库中 [英] Excel Spreadsheet into a SQL database

问题描述

在Excel 2007中工作。

我添加了正确的工具>参考。

错误代码为：

" [Microsoft] [ODBC驱动程序管理器]未找到数据源名称且未指定默认驱动程序"

 Private Sub SQLRecordset（）
 Dim cn As New ADODB.Connection 
 Dim rs As New ADODB.Recordset 
 Dim SQL_String As String 
 Dim StrConn As String 
 Set cn = New ADODB .Connection 
 StrConn =" server = db457994917.db.1and1.com; initial catalog = db457994917; uid = dbo457994917; pwd = electrical;" 
 cn.Open StrConn 
 SQL_String =" SELECT * FROM [register];" 
设置rs =新ADODB.Recordset 
 rs.Open来源：=" PlanetaryOrbits"，ActiveConnection：= cn，_ 
 CursorType：= adOpenKeyset，LockType：= adLockOptimistic，_ 
选项：= adCmdTableDirect 
 

 rs！idp = Cells（2,2）
 

 rs！JD = Cells（3,2）
 rs.Close 
 Set rs = Nothing 
 cn.Close 
结束子

解决方案

嗨Philosophiae，

我想你应该稍微改变你的连接字符串。

尝试连接下面的字符串：

" PROVIDER = SQLOLEDB;数据源= db457994917.db.1and1.com;初始目录= db457994917; uid = dbo457994917; pwd =电气;"

Working in Excel 2007.

I have added the correct Tools>References.

The error code is:

"[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified"

Private Sub SQLRecordset()
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim SQL_String As String
    Dim StrConn As String
    Set cn = New ADODB.Connection
    StrConn = "server=db457994917.db.1and1.com;initial catalog=db457994917;uid=dbo457994917;pwd=electrical;"
    cn.Open StrConn
    SQL_String = "SELECT * FROM [register];"
    Set rs = New ADODB.Recordset
    rs.Open Source:="PlanetaryOrbits", ActiveConnection:=cn, _
        CursorType:=adOpenKeyset, LockType:=adLockOptimistic, _
        Options:=adCmdTableDirect
    

    rs!idp = Cells(2, 2)
    

    rs!JD = Cells(3, 2)
    rs.Close
    Set rs = Nothing
    cn.Close
End Sub

解决方案

Hi Philosophiae,

I think you should change your conection string a little bit.

try the conection string below:

"PROVIDER=SQLOLEDB;Data Source=db457994917.db.1and1.com;initial catalog=db457994917;uid=dbo457994917;pwd=electrical;"

这篇关于Excel电子表格到SQL数据库中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！