Excel VBA讀取Access

 1 Dim myconnect As ADODB.Connection
 2 Dim myres As ADODB.Recordset
 3 Dim n As Integer, m As Integer
 4 
 5 
 6 Private Sub btn_exit_Click()
 7     End
 8 End Sub
 9 
10 Private Sub CommandButton1_Click()
11     If myres.BOF = True And myres.EOF = True Then
12         Exit Sub
13     Else
14         n = 1
15         myres.MoveFirst
16         Call display
17     End If
18     
19 End Sub
20 
21 
22 Private Sub CommandButton2_Click()
23     If myres.BOF = True And myres.EOF = True Then
24         Exit Sub
25     Else
26         n = m
27         myres.MoveLast
28         Call display
29     End If
30 End Sub
31 
32 Private Sub CommandButton5_Click()
33     If myres.BOF = True Then
34         Exit Sub
35     End If
36         
37         myres.MovePrevious
38         n = n - 1
39     If myres.BOF = True Then
40         Exit Sub
41     End If
42         
43         Call display
44 End Sub
45 
46 Private Sub CommandButton6_Click()
47     If myres.EOF = True Then
48         Exit Sub
49     End If
50     
51     myres.MoveNext
52     
53     If myres.EOF = True Then
54         Exit Sub
55     End If
56         n = n + 1
57         Call display
58 End Sub
59 
60 Private Sub Frame1_Click()
61 
62 End Sub
63 
64 Private Sub UserForm_Initialize()
65     Dim mydata As String, mytable As String, n As Integer
66     
67     mydata = "C:\Users\Administrator\Documents\Database1.accdb"
68     mytable = "表1"
69     
70 
71     Set myconnect = New ADODB.Connection
72     myconnect.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;data source=" & mydata
73     myconnect.Open
74     
75     Set myres = New ADODB.Recordset
76     myres.Open mytable, myconnect, adOpenKeyset, adLockOptimistic
77     
78     m = myres.RecordCount
79     MsgBox "數據庫：" & mydata & "表：" & mytable & "共有" & m & "條記錄"
80     
81     
82 End Sub
83 
84 
85 Private Sub display()
86     If myres.EOF = True And myres.BOF = True Then
87         TextBox11.Value = "無數據"
88         TextBox22.Value = "無數據"
89         TextBox33.Value = "無數據"
90         TextBox44.Value = "數據庫中無數據"
91     Else
92         TextBox11.Value = myres.Fields(1)
93         TextBox22.Value = myres.Fields(2)
94         TextBox33.Value = myres.Fields(2)
95         TextBox44.Value = "共有 " & m & " 條記錄,當前記錄為" & n
96     End If
97 End Sub

转载于:https://www.cnblogs.com/xyzabc0004/archive/2012/03/23/2415004.html