如何从adodb记录集Excel VBA中的一列中选择不同的值? [英] How to select distinct values from one column in adodb recordset Excel VBA?

查看:73
本文介绍了如何从adodb记录集Excel VBA中的一列中选择不同的值?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一个从数据库中获取的 ADODB.Recordset rs .我现在必须重复使用此记录集两次.

I have a ADODB.Recordset rs that I'm getting from DB. I have to reuse this recordset twice now.

这是我的记录集的示例:

This is sample of my Recordset:

Mike     Client
John     Manager
Karen    Client
Joe      Sub
Brian    Manager

现在我需要获取所有标题,所以我想获取:

Now I need to get all the titles, so I want to get:

Client
Manager
Sub

我知道有 rs.Filter ,但是我不确定是否可以选择与之不同.

I know that there is rs.Filter, but I'm not sure if I can select distinct from it.

我还知道我可以克隆此Recordset:

Also I know that I can clone this Recordset:

Dim rs_clone As ADODB.Recordset
Set rs_clone = New ADODB.Recordset
rs_clone = rs.getrows()

是否可以仅克隆不同的记录?还是更好的方法?谢谢

Is it possible to clone only distinct records? Or any better way? Thanks

推荐答案

在数据库中生成sql字符串将为您提供很大的空间,可以非常选择性地选择要返回的内容

Firing a sql string at the database gives you lots of room to be very selective about what you'd like returned

一个小示例(使用后期绑定,我更喜欢在生产代码中使用该绑定),在该示例中,我要从表列 MyColumn

Small example (using late binding which I prefer in production code) where I'm asking for a distinct list from the table column MyColumn

Dim cn As Object
Dim rs As Object

Set cn = CreateObject("ADODB.Connection")
cn.Open strConn
cn.CommandTimeout = 0

Set rs = CreateObject("ADODB.Recordset")
Set rs.ActiveConnection = cn


'=====================
rs.Open "SELECT Distinct MyColumn AS C FROM myTable"

strConn 需要设置为正确的连接字符串.

strConn needs to be set to the correct connection string.

编辑

在这篇文章的帮助下,无法在数据库中触发 sql 字符串

Without being able to fire a sql string at the database with the help of this post vba: get unique values from array I've got the following solution.

如果您希望早期绑定,则需要引用以下内容:

If you prefer early binding then references to the following will be required:

  • Microsoft ActiveX数据对象(Im使用6.1库)
  • Microsoft Scripting运行时(因此我们可以使用字典)

代码如下:

Option Explicit

Global Const strConn As String = _
    "PROVIDER=MySQLprovider;" & _
    "P*SSWORD=MyPword;" & _
    "USER ID=MyLogin;" & _
    "INITIAL CATALOG=MyDB;" & _
    "DATA SOURCE=MyServer;" & _
    "USE PROCEDURE FOR PREPARE=1;" & _
    "AUTO TRANSLATE=True;"


Sub getDistinctRecords()

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.ConnectionTimeout = 0
cn.Open strConn

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.ActiveConnection = cn

'>>this mimics your record set with non-distinct members
rs.Open _
    "SELECT 'a' as MyCol UNION ALL " & _
    "SELECT 'a' as MyCol UNION ALL " & _
    "SELECT 'b' as MyCol UNION ALL " & _
    "SELECT 'b' as MyCol"

Dim Arr() As Variant
Arr = rs.GetRows() 

Dim d As Scripting.Dictionary
Set d = New Scripting.Dictionary

Dim i As Long
For i = LBound(Arr, 2) To UBound(Arr, 2)
    d(Arr(0, i)) = 1
Next i

Dim v As Variant
For Each v In d.Keys()

    '>>d.Keys() is a Variant array of the unique values in myArray.
    '>>v will iterate through each of them.

    '>>to print to the immediate window
    Debug.Print v
Next v

'=====================
     'tidy up connection
On Error Resume Next
    Set rs.ActiveConnection = Nothing
On Error GoTo 0

If Not (rs Is Nothing) Then
    If (rs.State And 1) = 1 Then rs.Close
    Set rs = Nothing
End If
If Not (cn Is Nothing) Then
    If (cn.State And 1) = 1 Then cn.Close
    Set cn = Nothing
End If

End Sub

这篇关于如何从adodb记录集Excel VBA中的一列中选择不同的值?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆