Excel VBA : Pull data from Access (VBA equivalent of an index/match)

[Tharka]

Hi there,

Here is my situation:

• I have a table on Access with a first column called "Name" (ex: Peter, Jack, Jane ...) as a Primary Key and many other data columns
• I have an Excel sheet with various Named ranges, each range has only one cell (named after the Fields column in my access database). This sheet has a list of "Names" (ex : only Peter and Jane).

What I want to achieve :
I want to be able to fill in the Named ranges with the relevant data from the Access table. I need to be able to pull one cell at a time (the cross reference of a certain row and a certain column), and not the entire row matching the name. I basically need the VBA equivalent of a INDEX/MATCH formula but for Access.

That's why "Recordset" does not answer my need, as it pulls the entire row. I found a workaround by myself but it seems very cumbersome and I am sure there is a more effective way of doing this. The workaround : I record the .AbsolutPosition of the Field "Name" when it matches and then use this position to pull the required row from the required column.

I am pretty sure all of the above is very confusing, but i'm new to Access and it's pretty tough to explain my problem.

Let me know if you need more info,
Thanks!
Tharka

 

[Micron]

A recordset object has a fields collection. If you know that you want the nth field (e.g. 1st field is 0, or you can use its name property) to go into a known range, then consider Automation to push the data to Excel. Have to say though, that the post can be a bit confusing around the terminology. Access tables have records and fields. Excel has rows and columns. If you use r&c when referring to Access, you might throw people off track as to which one you're referring to. As for AbsolutePosition, I thought it related to the record position in a recordset object. I couldn't find any info on it being applied to a field.

 

[Tharka]

Thanks for your answer!

For those interested, here the final code :

Dim strMyPath As String, strDBName As String, strDB As String, strSQL As StringDim i As Long, n As Long, lFieldCount As Long, k As Long
Dim rng As Range
Dim MyDeal As String
Dim NRange As Name
Dim arr(1 To 1000) As String
Dim item As Variant
Dim item2 As Variant


'instantiate an ADO object using Dim with the New keyword:
Dim adoRecSet As New ADODB.Recordset
Dim connDB As New ADODB.Connection


'--------------
'THE CONNECTION OBJECT


strDBName = "Database.accdb"
strMyPath = "C:\Users\JDoe\Documents"
strDB = strMyPath & "" & strDBName


Set connDB = New ADODB.Connection
Set adoRecSet = New ADODB.Recordset


connDB.Open ConnectionString:="Provider = Microsoft.ACE.OLEDB.12.0; data source=" & strDB


Dim ws As Worksheet
'set the worksheet:
Set ws = ActiveWorkbook.Sheets("Sheet2")


'Set the ADO Recordset object:
Set adoRecSet = New ADODB.Recordset


'Opening the table :
strTable = "Profil"


adoRecSet.Open Source:=strTable, ActiveConnection:=connDB, CursorType:=adOpenStatic, LockType:=adLockOptimistic


lFieldCount = adoRecSet.Fields.Count


MyDeal = ws.Range("A1").Value


For Each NRange In ActiveWorkbook.Names
    M = M + 1
    arr(M) = NRange.Name
Next NRange


adoRecSet.Filter = adoRecSet.Fields(0).Name & " = '" & MyDeal & "'"


If adoRecSet.EOF Then
    adoRecSet.AddNew
    adoRecSet!Nom = MyDeal
    adoRecSet.Filter = adoRecSet.Fields(0).Name & " = '" & MyDeal & "'"
    For i = 1 To lFieldCount - 1
        For Each item In arr
            If adoRecSet.Fields(i).Name = item Then adoRecSet.Fields(i).Value = ws.Range(item).Value
        Next item
    Next i
Else
    For i = 1 To lFieldCount - 1
        For Each item2 In arr
            If adoRecSet.Fields(i).Name = item2 Then adoRecSet.Fields(i).Value = ws.Range(item2).Value
        Next item2
    Next i
End If


adoRecSet.Update
adoRecSet.Close


'close the objects
connDB.Close


'destroy the variables
Set adoRecSet = Nothing
Set connDB = Nothing