ADO n Active X Data objects ADO ADO
������ ADO n Active. X Data objects (ADO(
������ ADO เลอก Microsoft Active. X Data Objects 2. 7 Library
���� Dim rs. Data As New ADODB. Recordset Dim Connect. DB As String Dim SQLCommand As String Connect. DB = "Provider=SQLOLEDB; " & "Data Source=(Local); " & "Initial Catalog=VBAEXCEL; " & "Integrated Security=SSPI" SQLCommand = "Select * from Student" rs. Data. Open SQLCommand, Connect. DB
����� Microsoft DAO 3. 6 Object Library
����� DAO Dim ����� AS DAO. Database Set ����� = Open. Database(“������� ”) ���� Dim Connect. DB AS DAO. Database Set Connect. DB = Open. Database(“D: AccessMy. Data. Source. mdb”)
��� Copy ������ Excel Sheet 1. Range("B 2"). Copy. From. Recordset rs. Data คาเทากบ Sheet 1. Cells(2, 2). Copy. From. Recordset rs. Data
Program Private Sub Command. Button 1_Click() Dim rs. Data As New ADODB. Recordset Dim Connect. DB As String Dim SQLCommand As String Connect. DB = "Provider=Microsoft. jet. OLEDB. 4. 0; " & "Data Source = D: VBAProgramExercise. mdb; " SQLCommand = "Select * from Student" rs. Data. Open SQLCommand, Connect. DB If Not rs. Data. EOF Then Sheet 1. Range("B 2"). Copy. From. Recordset rs. Data ‘’’’’������ Sheet 1. Cells(2, 2). Copy. From. Recordset rs. Data Else Msg. Box “Data not Found", vb. Critical, "Error!" End If rs. Data. Close Set rs. Data = Nothing End Sub
����� Sheet 1. Range("B 2"). Copy. From. Recordset rs. Data ‘’’’’มคาเหมอน Sheet 1. Cells(2, 2). Copy. From. Recordset rs. Data
การระบคา Field ใน Recordset ทละคา ������ Recordset. Fields(“Field. Name”) ����� Recordset(“Field. Name”) ���� rs. Data. Fields(“Sur. Name") rs. Data(“Sur. Name") Ex. การนำคา Recordset ไปใสใน Text box txt. Name. Text = rs. Data. Fields("Name") หรอ txt. Name. Text = rs. Data("Name")
การระบคาใน Recordset ทละคา Ex. ���� Recordset ������� Excel ������� Sheet 2. Range("B 2“)= rs. Data. Fields("Name") Sheet 2. Range(“C 2“)= rs. Data. Fields(“Surname") ���� Sheet 2. Range("B 2“)= rs. Data("Name") Sheet 2. Range(“C 2“)= rs. Data(“Surname") ���� Sheet 2. Cells(2, 2) = rs. Data. Fields("Name") Sheet 2. Cells(2, 3) = rs. Data. Fields(“Surname") ���� Sheet 2. Cells(2, 2) = rs. Data("Name") Sheet 2. Cells(2, 3)= rs. Data(“Surname")
- Slides: 20