连接不上SQL

wangjin1982_0

1. Public Sub SynTableConfig()
   
2. Dim cn As New ADODB.Connection, sh As Worksheet
   
3. Dim rs As New ADODB.Recordset
   
4. Dim strCn As String, strSQL As String
   
5. 
   
6. 
   
7. strCn = "Provider=sqloledb;Server=127.0.0.1;Database=tew_catalog;Uid=tew;Pwd=SQLpwd4ew;"
   
8. strSQL = "select * from tew_catreference"
   
9. cn.Open strCn
   
10. rs.Open strSQL, cn
    
11. 
    
12. i = 1
    
13. Set sht = ThisWorkbook.Worksheets("sheet1")
    
14. Do While Not rs.EOF
    
15. sht.Cells(i, 1) = rs("cre_reference")
    
16. rs.MoveNext
    
17. i = i + 1
    
18. Loop
    
19. rs.Close
    
20. cn.Close
    
21. End Sub

复制代码

我一直连接不上SQL，不知道是否是我的连接代码有问题，请高手指教。我的SQL实例名称是  TEW_SQLEXPRESS，这个内容不知道在哪儿写。

microyip

1. Public Sub SynTableConfig()
   
2.     Dim cn As Object, sh As Worksheet
   
3.     Dim strSQL As String
   
4.    
   
5.     Set cn = CreateObject("Adodb.Connection")
   
6.     cn.ConnectionString = _
   
7.         "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=tew;Password=SQLpwd4ew;Initial Catalog=tew_catalog;Data Source=127.0.0.1"
   
8.     cn.Open
   
9.    
   
10.     strSQL = "select cre_reference from tew_catreference"
    
11.     Sheet1.[A1].CopyFromRecordset cn.Execute(strSQL)
    
12.     cn.Close
    
13. End Sub
    

复制代码

wangjin1982_0

我发现问题了，我把数据源添加上SQL实例就好了。多谢楼上了

1. Public Sub SynTableConfig()
   
2. Dim cn As Object, sh As Worksheet
   
3. Dim strSQL As String
   
4. 
   
5. Set cn = CreateObject("Adodb.Connection")
   
6. cn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=tew;Password=SQLpwd4ew;Initial Catalog=tew_catalog;Data Source=DESKTOP-CU7B3R2\TEW_SQLEXPRESS"
   
7. cn.Open
   
8. strSQL = "select cre_reference from tew_catreference"
   
9. Sheet1.[A1].CopyFromRecordset cn.Execute(strSQL)
   
10. cn.Close
    
11. End Sub

复制代码