How to get lookup data from Access Table


M

Madiya

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
 
Ad

Advertisements

N

Nigel

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?
 
M

Madiya

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
(e-mail address removed)









- 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/index.php?d=envbadacexportado

' 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
================================
 
Ad

Advertisements

J

Joel

The record set (RS) defines the cells to return. So this is the section that
needs to be modified. I included comment in the rows below


A record set is a filter set of rows and columns from a table. Can come
from access but can also be used to return rows and columns from an excel
spreadsheet. Microsoft treats access tables and excel worksheets the same.

With RS
' open the recordset


Select the table from access or a worksheet frim excel

.Open TableName, cn, adOpenStatic, adLockOptimistic,adCmdTable
' all records

The * in the Select indicates you are returning all columns in the table. m
the SELCT is the start of the SQL (String Query Language)


This line is commented out. the comments need to be remove. and the vbcrlf
need to be added. Repelace MyCriteria with any filtered text you like.

MySQL = "SELECT * FROM " & TableName & vbCRLF & _
"WHERE [TableName.FieldName] = " chr(34) & MyCriteria" & chr(34)

.Open MySQL, 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
' the recordset data
TargetRange.Offset(1, 0).CopyFromRecordset rs

End With
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top