Accessing data in Access from Excel

[Osmar Mateus]

Is it possible to read data from Access into Excel?

 

[ndendrinos]

Hello Osmar and Happy New Year

This example will explain it best.
Create on your Desktop an Access Database named Vault
In it create a Table named Records
In the Table "Records"create three columns
..Inv..........Name...........Total
..111.........Osmar..........$10.00
..112..........Nick.............$20.00

In Excel go to your VB Editor go to Tools and choose References and make sure there is a tick mark in "Microsoft Active Data Objects 2.7 Library (If you do not have 2.7 chosse the highest one your version of Excel has)....Click on OK
Still in the VB Editor click on Insert and choose Module and in the right hand screen that's empty paste the following code:

Dim adoCN As ADODB.Connection
Dim strSQL As String

Const DatabasePath As String = "C:\Documents and Settings\Osmar\Desktop\Vault.mdb"

'Function argument descriptions
'LookupFieldName - the field you wish to search
'LookupValue - the value in LookupFieldName you're searching for
'ReturnField - the matching field containing the value you wish to return

Public Function DBVLookUp(TableName As String, _
LookUpFieldName As String, _
LookupValue As String, _
ReturnField As String) As Variant
Dim adoRS As ADODB.Recordset
If adoCN Is Nothing Then SetUpConnection

Set adoRS = New ADODB.Recordset
strSQL = "SELECT " & LookUpFieldName & ", " & ReturnField & _
" FROM " & TableName & _
" WHERE " & LookUpFieldName & "=" & LookupValue & ";"
' If lookup value is a number then remove the two '
adoRS.Open strSQL, adoCN, adOpenForwardOnly, adLockReadOnly
If adoRS.BOF And adoRS.EOF Then
DBVLookUp = "Value not Found"
Else
DBVLookUp = adoRS.Fields(ReturnField).Value
End If
adoRS.Close
End Function

Sub SetUpConnection()
On Error GoTo ErrHandler
Set adoCN = New Connection
adoCN.Provider = "Microsoft.Jet.OLEDB.4.0" 'Change to 3.51 for Access 97
adoCN.ConnectionString = DatabasePath
adoCN.Open
Exit Sub
ErrHandler:
MsgBox Err.Description, vbExclamation, "An error occurred"
End Sub

One last thing is to edit in the above code the path line ""C:\Documents and Settings\Osmar\Desktop\Vault.mdb"to what it really is.
To find this out right click on the Access Data Base icon and click on "Properties"...see what it says.... (my data base is called "Vault" and happens to be on my Desktop.... yours will have a different name and might be somewhere else than your Desktop)
When you have edited your "path" you can close the VB Editor.


Create an Excel Workbook
On sheet 1 create three columns

..........A............B..............C.........
1.......Inv.......Name........Total.......
2.........................................

Insert this formula in B2
=DBVLookUp("Records","Invoice",A2,"Name")
Insert this formula in C2
=DBVLookUp("Records","Invoice",A2,"Total")

That's it you are done and now if you type in A2 111 or 112 you should get the corresponding row from Access
BTW the Access DB need not be open for this to work.

One last thing Osmar,
I've made my answer to you very detailed for the reason that in this forum there are readers with all levels of VBA knowledge and the ones like me will be able to benefit from this post as well.

 

[IkeKairey]

Password Protected Access

Has anyone tried using this code with an Access database that is password protected? It doesnt seem to work