请教在excel中如何引用access数据库中某一列任意一行的记录,麻烦了，谢谢！

flysum2008 · 发表于 2011-4-11 20:51

请教在excel中如何引用access数据库中某一列任意一行的记录？能实现这一功能吗？万分感谢！详见附件：

[ 本帖最后由 flysum2008 于 2011-4-12 07:40 编辑 ]

flysum2008 · 发表于 2011-4-12 07:41

麻烦大家帮帮我，谢谢啦！

flysum2008 · 发表于 2011-4-12 09:03

原帖由 zhaogang1960 于 2011-4-12 08:53 发表
Sub Macro1()
Dim myData As String, myTable As String, SQL As String, s$
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim i As Integer
myData = ThisWorkbook.Path & "\职 ...

非常感谢您，占用您时间了，我好好学习一下，再次感谢！

zhaogang1960 · 发表于 2011-4-12 09:03

如果要求的记录较少时，可以用记录集的AbsolutePosition属性定位：
Sub Macro1()
Dim myData As String, myTable As String, SQL As String, a
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim i As Integer
myData = ThisWorkbook.Path & "\职工管理22.mdb"
myTable = "职工基本信息22"
Set cnn = New ADODB.Connection
With cnn
      .Provider = "microsoft.jet.oledb.4.0"
      .Open myData
End With
Set rs = New ADODB.Recordset
rs.Open myTable, cnn, adOpenKeyset, adLockOptimistic
a = Array(1, 2, 4, 5, 7, 8, 10, 11, 12)
[a1].CurrentRegion.ClearContents
For i = 0 To UBound(a)
      rs.AbsolutePosition = a(i)
      Cells(i + 1, 1) = rs.Fields(0)
Next
rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing
End Sub

flysum2008 · 发表于 2011-4-12 09:05

再次谢谢您，我的记录就是比较多，有20万条呢，我好好研究一下，不懂再向您请教！

zhaogang1960 · 发表于 2011-4-12 08:53

Sub Macro1()
Dim myData As String, myTable As String, SQL As String, s$
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim i As Integer
myData = ThisWorkbook.Path & "\职工管理22.mdb"
myTable = "职工基本信息22"
Set cnn = New ADODB.Connection
With cnn
      .Provider = "microsoft.jet.oledb.4.0"
      .Open myData
End With
Set rs = New ADODB.Recordset
rs.Open myTable, cnn, adOpenKeyset, adLockOptimistic
s = ",1,2,4,5,7,8,10,11,12,"
[a1].CurrentRegion.ClearContents
For i = 1 To rs.RecordCount
      If InStr(s, "," & i & ",") Then
         m = m + 1
         Cells(m, 1) = rs.Fields(0)
      End If
      rs.MoveNext
Next
rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing
End Sub

zhaogang1960 · 发表于 2011-4-12 08:56

数据库论坛求助.rar (31.53 KB, 下载次数: 89)

LMY123 · 发表于 2014-4-24 09:08

AbsolutePosition学习中

		自动登录	找回密码
密码			免费注册

[求助] 请教在excel中如何引用access数据库中某一列任意一行的记录,麻烦了，谢谢！

评分

评分

浏览过的版块