无源ADO如何显视表的全部内容[已解决]

huangjingfeng88

具体情况请看附件

duzili

下面的示例演示如何打开您自己的 ADO 连接 Microsoft SQL Server 数据库以及将窗体绑定到它：
打开示例数据库 Northwind.mdb。
在设计视图中打开客户窗体。
清除窗体，以取消绑定窗体的 记录源 属性。
Set the OnOpen property of the form to the following event procedure:
Private Sub Form_Open(Cancel As Integer)
   Dim cn As ADODB.Connection
   Dim rs As ADODB.Recordset
         
   'Create a new ADO Connection object
   Set cn = New ADODB.Connection

   'Use the Access 10 and SQL Server OLEDB providers to
   'open the Connection
   'You will need to replace MySQLServer with the name
   'of a valid SQL Server
   With cn
      .Provider = "Microsoft.Access.OLEDB.10.0"
      .Properties("Data Provider").Value = "SQLOLEDB"
      .Properties("Data Source").Value = "MySQLServer"
      .Properties("User ID").Value = "sa"
      .Properties("Password").Value = ""
      .Properties("Initial Catalog").Value = "NorthwindCS"
      .Open
   End With

   'Create an instance of the ADO Recordset class, and
   'set its properties
   Set rs = New ADODB.Recordset
   With rs
      Set .ActiveConnection = cn
      .Source = "SELECT * FROM Customers"
      .LockType = adLockOptimistic
      .CursorType = adOpenKeyset
      .Open
   End With
   
   'Set the form's Recordset property to the ADO recordset
   Set Me.Recordset = rs
   Set rs = Nothing
   Set cn = Nothing
End Sub

将下面的代码添加到窗体的卸载事件：
Private Sub Form_Unload(Cancel As Integer)
   'Close the ADO connection we opened
   Dim cn As ADODB.Connection
   Set cn = Me.Recordset.ActiveConnection
   cn.Close
   Set cn = Nothing
End Sub

duzili

详情请看微软的文章
http://support.microsoft.com/kb/281998/zh-cn

huangjingfeng88

请老师再帮我看下，我改了下代码，但还是不成功，主要是SQL连接不上，我用ADP测试是能连上的，不知道是版本问题还是其它原因

todaynew

Private Sub Form_Load()
Dim rs1 As New ADODB.Recordset
rs1.Open "库存表", CurrentProject.Connection, adOpenStatic, , adCmdTable
Set Me.Recordset = rs1
Me.出厂编号.ControlSource = "出厂编号"
Me.车型.ControlSource = "车型"
Me.数量.ControlSource = "数量"
End Sub

huangjingfeng88

谢谢版主回复，但版主是对本MDB内表的ADO连接，我想要的是对SQL的连接，我将代码改为
rs1.Open "库存表", Cnn, CurrentProject.Connection, adOpenStatic, , adCmdTable
报错误参数号或无数属性值

zhuyiwen

2 楼，正解

huangjingfeng88

我按2楼老师的回复改了代码，但还是没成功，请老师帮我看下为什么好吗

红尘如烟

先使用基于mdb的数据库表创建绑定窗体，然后在窗体的Open事件中加载连接到SQL Server的数据源。
前期绑定写法（必须引用ADO）：

1. Private Sub Form_Open(Cancel As Integer)
   
2.     Dim cnn         As ADODB.Connection
   
3.     Dim rst         As ADODB.Recordset
   
4.     Dim strServer   As String
   
5.     Dim strUserName As String
   
6.     Dim strPassword As String
   
7.     Dim strDatabase As String
   
8.    
   
9.     strServer = "(local)"
   
10.     strUserName = "sa"
    
11.     strPassword = "1234"
    
12.     strDatabase = "db_Csell"
    
13.    
    
14.     cnn.Open "Provider=SQLOLEDB" & _
    
15.              ";Data Source=" & strServer & _
    
16.              ";Initial Catalog=" & strDatabase, _
    
17.              strUserName, strPassword
    
18.     rst.CursorLocation = adUseClient
    
19.     rst.Open "SELECT * FROM 库存表;", cnn, adOpenKeyset, adLockOptimistic
    
20.     Set Me.Recordset = rst
    
21.     Set rst = Nothing
    
22.     Set cnn = Nothing
    
23. End Sub

复制代码

后期绑定写法（不需要引用ADO）：

1. Private Sub Form_Open(Cancel As Integer)
   
2.     Dim cnn         As Object
   
3.     Dim rst         As Object
   
4.     Dim strServer   As String
   
5.     Dim strUserName As String
   
6.     Dim strPassword As String
   
7.     Dim strDatabase As String
   
8.    
   
9.     strServer = "(local)"
   
10.     strUserName = "sa"
    
11.     strPassword = "1234"
    
12.     strDatabase = "db_Csell"
    
13.    
    
14.     Set cnn = CreateObject("ADODB.Connection")
    
15.     cnn.Open "Provider=SQLOLEDB" & _
    
16.              ";Data Source=" & strServer & _
    
17.              ";Initial Catalog=" & strDatabase, _
    
18.              strUserName, strPassword
    
19.     Set rst = CreateObject("ADODB.Recordset")
    
20.     rst.CursorLocation = 3
    
21.     rst.Open "SELECT * FROM 库存表;", cnn, 1, 3
    
22.     Set Me.Recordset = rst
    
23.     Set rst = Nothing
    
24.     Set cnn = Nothing
    
25. End Sub

复制代码

huangjingfeng88

谢谢红尘老师的指点，问题已解决