using access database with excel

B

Beamer

New Member
Joined
Mar 17, 2004
Messages
16
Hi all .

I have been up and down the forum for the last hours but do not seem to find the right answer to my problem. :oops: Recently I started experimenting with linking databases to my excel file. No need to say I am an absolute newB to this.

This is the scenario :

I have an access database (maintained by someone else & residing on a network) that is used for x-reference. Structure is known.

I my excel sheet , I have the values for lookup in lets say column A.
Column B holds the pasted and matching data from Access.

I know that I can import the data from Access with get external but I dont want to do this. I want to use the data right from Access without first copying it into excel and doing a lookup function.

If anyone got a good piece of VBA for this, I would appreciate it. Any other suggestions are always welcome (y)
[/img]
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Sort by date Sort by votes
There is no need for VBA. Use Data | Import External Data > New database query... | follow the directions in MS Query and create a parameterized query. When you return to XL, XL will offer you a choice of (a) always use the same constant, (b) ask the user for the criterion value, or (c) get it from a particular cell. If the last, you will also be asked if you want an automatic update whenever the cell value changes.
 
Upvote 0
Thanks fo the reply. My problem remains that if I use the function of the shelf, I can not define the cell to be used as reference. It only gives you where you want to put the data and in what way.


So what I need is sheet1 a2 has a name , I want to use that value (cell) to return a value from the database.

Am I doing something wrong ? any other ideas ?
 
Upvote 0
Please read my initial response closely. You can specify a cell as the argument for a parameterized query.
 
Upvote 0
Tusharm, I have repeatedly read your post and tried different scenario's. I am unable to get to the stage you are describing. When I build my query, I need to select the table that I will use in my database. Next I am asked to select the fileds I want to use out of my table. Then I am asked for the criteria which I need to select out of a list from the fields that are selected. I can also leave it blank.Next MS Q asks me where to import the data. I select the cell for import from Access on an existing worksheet and MS Q copies in the selection I made. When no selection is made it copies the complete data that belongs to the field I selected.

It never comes up with the a , b , c questions you are referring to. I can not set my criteria to read from a cell in XL. What am I doing wrong ? :rolleyes:
 
Upvote 0
See Nate's link. His explanation in that discussion are more detailed than my abbreviated suggestion in this. Once you have the parameterized query (characterized by the criteria in square brackets), XL will ask you the questions I indicated when you save and return from MS Query. Note that Query itself will ask you to provide some values for the parameter. Enter any value you want; it won't matter.
Beamer said:
{snip}
It never comes up with the a , b , c questions you are referring to. I can not set my criteria to read from a cell in XL. What am I doing wrong ? :rolleyes:
Click to expand...
 
Upvote 0
Tusharm , NateO ,

thank you both I have finally had time to work on the examples given by NateO in the other thread and the basics work now (y)
Thanks a million !

The only difficulty I need to solve now is that the excel field (used for lookup) CAN hold more then one value. The values are separated by a comma. The cell does not equal a value in my table ( because it does not recognise that it are multiple values ) and therefore does not return anything.

You guys have any good thoughts how to solve this without touching the values in the cells in excel ?

If not, I guess little else I can do than to produce some code to strip the comma and put the values in different rows and then run the external data query.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Building an information management system using Excel
Replies
2
Views
289
cercenazi
C
F
  • Question
Manipulate Access table or query from excel using excel vba
Replies
7
Views
468
Micron
M
A
  • Question
Recording Excel ranges from several tabs into Access DB
Replies
1
Views
381
alansidman
A
P
  • Question
Replacing Access With Excel
Replies
4
Views
521
Joe4
Joe4
M
  • Question
Populate excel listview from access database
Replies
0
Views
268
Mohamed Hamada 22
M

Forum statistics

Threads
1,216,037
Messages
6,128,439
Members
449,453
Latest member
jayeshw

Share this page

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
Back
Top