Excel to Access and back again

PhilipLawrence99

New Member
Joined
Aug 11, 2002
Messages
20
has anyone any idea how to pull a single record out of an access database back in to Excel? I need to type a part number in an Excel spreadsheet and for it to go and look in an Access database and bring back information (description, price, etc.) based on that one part number. We've got about 91,000 part numbers so it won;t all fit in a single worksheet - unless you can search across multiple worksheets in the same spreadheet of course.....

Harrassed of Heptonstall looks forward to hearing from you.
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
You will need to do this using a SQL query.

Here's an example...

Set dbs = opendatabase("R:currentfinanceabtprocess.mdb")

Set qry = dbs.CreateQueryDef(a, "SELECT SUM(hours) FROM DEPT_FILTERED_DATA WHERE ACTIVITY = 'Pilot'")

Set rst = qry.OpenRecordset()

[b16].CopyFromRecordset rst

dbs.Close

(this example simply sums the hours of time posted against a particular activity - copies it to cell B16 in your Excel spreadsheet).

The other way of doing this is to set up the query to run in Access as opposed to Excel - you can then call the Query in Access without ever having to go into the application...

there's lots of info on simple SQL code on the internet if you're not familiar with it.

e.g to run Query1 in Access

Dim obj1 As AccessObject
Dim strPath As String
Dim strDBName As String

Set appaccess1 = New Access.Application
strPath = "R:currentFinanceABT"
strFile = "Process.mdb"
strDBName = strPath & strFile
appaccess1.OpenCurrentDatabase strDBName

DoCmd.OpenQuery ("Query1")

You can also import data from Excel to Access in this manner - i.e. copy data from Excel into Access without being in Access.

Hope this helos...
 

PhilipLawrence99

New Member
Joined
Aug 11, 2002
Messages
20
THanks for this but I'm probably not the best person to do SQL. if you send me your email then I'll email you a cut down version of the database listing - you won't want all 91000 lines I'm sure - and a bit more detail on what 'm trying to do....

My email is philip.lawrence@csf.co.uk

Cheers,
 

thelton

New Member
Joined
Sep 5, 2002
Messages
29
If you don't feel comfortable doing SQL then go to XL and use the Data drop down menu and select Get External Data, then choose New database query from the fly out. Then highlight Access database and then check the Query Wizard box at the bottom. This walks step by step to retrieve the data you want. If you don't have Data as a drop down, then you need to go to Tools and choose Add-Ins and check the box.

thelton@pnj.doc.state.in.us
 

thelton

New Member
Joined
Sep 5, 2002
Messages
29
If you don't feel comfortable doing SQL then go to XL and use the Data drop down menu and select Get External Data, then choose New database query from the fly out. Then highlight Access database and then check the Query Wizard box at the bottom. This walks step by step to retrieve the data you want. If you don't have Data as a drop down, then you need to go to Tools and choose Add-Ins and check the box.

thelton@pnj.doc.state.in.us
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
Cheers Thelton - this all worked ok and Phil was ok with the coding.
 

Forum statistics

Threads
1,147,815
Messages
5,743,366
Members
423,790
Latest member
kevinlee_5

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top