ExcelBanter - How to get lookup data from Access Table

On Aug 7, 3:09*pm, "Nigel" wrote:
I have found that setting up a prequery in the access database then pass the
value to filter the table in Access then pass the value back to Excel. *What
code have you so far?

--

Regards,
Nigel

"Madiya" wrote in message

...

Hi,
I have a access database which is updated daily. It has around
20,00,000 records with 56 columns.
It is always available at the same place/path with same name (C:
\GRSN.ACCDB).

I need to lookup a value from excel and retrive all columns of that
value in Excel from access database table.
So far I have succeeded in connecting and getting all the records
using ADO.
But I want only that specific value just like vlookup in excel.
Can someone pl help me with this please?

I am using Excel 2007 and Access 2007.

Regards,
Madiya- Hide quoted text -

- Show quoted text -

Thanks Nigel.
I could not understand prequary stuff you have mensioned. Sorry, I am
too new in Access.
This is my first project in Access.
I have gathered below code from erlandsandata site which is having
many such example codes.
This code fatches all the recordsets and all columns in excel.
I need only those recordsets which matches with my data in excel.

Thanks again
Madiya

Here is the code I am using.
===========================
Sub TEST_BELOW()
Columns("A:A").ColumnWidth = 20.43
Columns("B:B").ColumnWidth = 11
Columns("C:C").ColumnWidth = 21
Columns("D:D").ColumnWidth = 9.57
Call ADOImportFromAccessTable("C:\Documents and Settings\Ketan\Desktop
\GRSN\GRSN.accdb", _
"RSNDATA", Range("A1"))

End Sub
Sub ADOImportFromAccessTable(DBFullName As String, _
TableName As String, TargetRange As Range)

'FROM ERLANDSANDATA SITE, ADO EXAMPLES
'URL http://www.erlandsendata.no/english/...badacexportado

' Example: ADOImportFromAccessTable "C:\FolderName\DataBaseName.mdb",
_
"TableName", Range("C1")
Dim cn As ADODB.Connection, RS As ADODB.Recordset, intColIndex As
Integer
Set TargetRange = TargetRange.Cells(1, 1)
' open the database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & _
DBFullName & ";"
Set RS = New ADODB.Recordset
With RS
' open the recordset
.Open TableName, cn, adOpenStatic, adLockOptimistic,
adCmdTable
' all records
'.Open "SELECT * FROM " & TableName & _
" WHERE [FieldName] = 'MyCriteria'", cn, , , adCmdText
' filter records

RS2WS RS, TargetRange ' write data from the recordset to the
worksheet

' ' optional approach for Excel 2000 or later (RS2WS is not
necessary)
' For intColIndex = 0 To rs.Fields.Count - 1 ' the field names
' TargetRange.Offset(0, intColIndex).Value = rs.Fields
(intColIndex).Name
' Next
' TargetRange.Offset(1, 0).CopyFromRecordset rs ' the recordset
data

End With
RS.Close
Set RS = Nothing
cn.Close
Set cn = Nothing
End Sub

Sub RS2WS(RS As ADODB.Recordset, TargetCell As Range)
Dim f As Integer, r As Long, c As Long
If RS Is Nothing Then Exit Sub
If RS.State < adStateOpen Then Exit Sub
If TargetCell Is Nothing Then Exit Sub

' With Application
' .Calculation = xlCalculationManual
' .ScreenUpdating = False
' .StatusBar = "Writing data from recordset..."
' End With

With TargetCell.Cells(1, 1)
r = .Row
c = .Column
End With

With TargetCell.Parent
' .Range(.Cells(r, c), .Cells(.Rows.Count, c + RS.Fields.Count
- 1)).Clear
' clear existing contents
'Format RSN Column as text
Columns(c).Select
Selection.NumberFormat = "@"
TargetCell.Offset(1, 0).Select

' write column headers
For f = 0 To RS.Fields.Count - 1
On Error Resume Next
.Cells(r, c + f).Formula = RS.Fields(f).Name
On Error GoTo 0
Next f
' write records
On Error Resume Next
RS.MoveFirst
On Error GoTo 0
Do While Not RS.EOF
r = r + 1
For f = 0 To RS.Fields.Count - 1
On Error Resume Next
.Cells(r, c + f).Formula = RS.Fields(f).Value

On Error GoTo 0
Next f

RS.MoveNext
Loop
.Rows(TargetCell.Cells(1, 1).Row).Font.Bold = True
.Columns("A:IV").AutoFit
End With

With Application
.StatusBar = False
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub
================================