Excel With Access

[MikeT]

Hi,

I don't know if this question belongs in the Excel or Access forum? But being more comfortable with Excel this is where I wanted to be.

1) Using Excel as a query sheet, i.e. input part numbers and qty, can pricing data and descriptions be pulled from an Access database and dropped into this query sheet?

2) If this is possible, would someone know of a site showing examples and explanations.

Usually I can do everything in excel.... but have a new Boss who prefers Access and need to work with him.

Thank you,

Mike Topper

[austenr]

Hi Mike..Welcome to VBAX!!

See if the attached code will help you. If you cannot figure it out, post back.

[OBP]

Mike, if the data is an Access then it is much easier to have the Search and Query in Access as well. You can then (if you must) have the Excel sheet "Dynamically Linked" to the Query.
But you can do most things in Access.
If you need help with the Input Form and Query in Access I am quite happy do help or do it for you.

[OBP]

Sorry austenr, I didn't see your post, I was busy typing (slowly).

[austenr]

Thats OK

[XLGibbs]

As further explanation, you can set up an MS Query to point right to a database (SQL,Access or even another excel file) which can accept parameters. These parameters can be input prompts to the user, or can be based on cell values within the sheet, or can be formulas. The parameters can be set to re-query once the value changes, or you can change them and hit the external data refresh option..

I use a lot of combinations of external queries with parameters and comes in handy for certain projects.

[ndendrinos]

Hello Mike

Maybe this will be of interest to you.

You can import just one row in Access to Excel providing you know what's in the first column in that Access row.
Example:
In Access you have a phone directory set up like this:
LastName...........FirstName..........PhoneNumber
Carter................Tom..................888-111-2222
Willis..................Bob..................888-111-3333
etc...........
It is possible from Excel to type in a cell "Carter" and get in the next column the "Tom" and in the next column the "888-111-2222
without having to import the whole Table to Excel or link to the Table in Excel.

If that's what you want and keep in mind that if you have duplicates in the first column in Access such as "Carter Tom" and let us say "Carter William" the thing will not work for you because again you cannot choose which "CaRTER" you want to import to Excel then yes it is doable.

In other words the info in the first column in Access has to be unique.

If OK with above do this as an example:

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.

[MikeT]

Hello,

This is bloody amazing.... so many replies, so much help!

I tried everything I could this weekend to get a grip on this Excel2Acces2Excel thing and nothing...nada...zero success.

"austenr" - tried to understand your code. For now I couldn't make it work for me. Just starting out with Access and it is a mysterious beast.

"OBP" - thanks for the offer to help. Need to sort this out for myself... if you wouldn't mind helping along the way that would be grand.

"XLGibbs" - I really would like to get to the point where I could pick and choose how I manipulated data and from where. Have a very long way to go.

"ndendrinos" - Thought I was on a roll here and followed your instructions religously. Ended up with #VALUE! in B2-C3. No clue as to why or even how to investigate the error value? Quite obviously I screwed up somewhere.

I would like to thank you all very much for helping out. I really don't know where to go from here? Look forward to your suggestions.

Mike Topper

[OBP]

Mike, I specialise in helping with Access database construction, on a one to one basis if necessary, It is completely free as it is my retirement hobby, as are these forums.
So if you need help with Access just drop me an email.

[Ken Puls]

Hi there,

I have a couple of examples at my site of using Excel and Access together:

Retrieve Data From A Database To Excel Using SQL
Export A Range Of Excel Data To A Database

HTH,

[ndendrinos]

Hello Mike
Not sure why you couldn't get it going so here is a sample.
Expand it in your "C" drive , pull out from the folder both Excel and Access files and leave them in "C" ... delete the expanded folder.
What you now have are just the two Excel and Access files in "C"
Open the excel file and follow instructions ... should work.

If OK look at the code .. it is the same I've posted ... the only difference is the reference to the "Path"