Live link from Access to Excel

[DavSam]

HELP!! I'm creating my first database, so I am a true beginner. We are using Access97 and I am trying to link a table to an Excel2003 worksheet. Is this even possible?
I have tried using the Query Wizard in Excel with no success and am at a complete loss as to what to do next. Any assistance will be GREATLY appreciated.

 

[GaryPanic]

You do it from Excel - not from access
Data
~import
New Database Qry ....
and pick a table in your d/b

 

[DavSam]

Thanks GaryPanic. I tried that already without success. I did notice that when the 'Choose Data Source' window popped up that the choice for MS Access97 was not sharable. Even though I chose the other Access choice, it still didn't work. any other suggestions? Thanks again.

 

[GaryPanic]

It still should work - did you try and map it to the D/B ?

you should able to link to a previous version of Access without too much problem ...
Also it won't be live (I think) the qry from excel only works when you open it anything done after its open will not show until its closed and reopened
also anything done in Excel will not be updated in Access

What are you trying to do ?
if you want the data in Access - and use access for everything then a link isn't the way to go - just import th e data and use forms/qry to get to what you want/need-

If however you need the xls to show non access users data then a linked table is the route to go down

 

[DavSam]

GaryPanic,
Thanks for the help. I got the query working, finally. Now my problem is that the workbook that the query is populating is used to populate another workbook, but I keep getting an error (#NA!), in the second workbook, when I try to use the two workbooks. You're right, I use Access for everything (adding, deleting, etc.) so a link is the way to go. I'm wondering, do I even need the two workbooks or can I cut-out the 'middle-man' and go direct to the workbook that is actually using the data?

 

[gemma-the-husky]

i dislike linking to excel

excel is uncontrolled, there is no discipline over content. columns can contain mixed data, or wrong data. Users can move/add/delete data and columns, and the data is not necessarily reliable becuase of such issues.

if i HAVE to use excel data, i prefer to import it into access, validate it, and use it all within access.

and i would rather have a csv than excel data.

just a personal view, you understand.

 

[GaryPanic]

Try to stay away from doing what youa re doing - (unless you have no option)
Access can be view as an enhancement to Excel -
Excel is very powerful tool and is ideal in the right place -

Access cna be look at as being a larger version of Excel ( its far more than that) but it does have a few areas wher xls is just a better tool

now what is the 2nd workbook doing - you should be able to use the qry engine in xls to do a lot of stuff (thats if you ahve to use xls)

i think what you need to do (and please remember i am no expert just a hacker ) is to read up on when to use excel and when to use Access

from memory Excel will produce better charts (athlough i have some rather clear simple charts that are ok)- but complicated charts revert to xls

so decide what are you trying to achive in access and what are you try to achive in Excel

if you need xls - then have the raw data linked to access - make sure you ahve the right link ..then work you magic in excel on this changing data set

i have mulitple qry's into one work book - but i have not cross linked them or created anything comlicated other than "raw data"
if you get stuck ping back and i will try to help- however i am snowed under at the moment with other work so cannot promise when I coudl look at it

 

[DavSam]

Thanks, GP. Good advice. The only reason I was going this route is that the customer already had a system in place, but was using SEVERAL (I do mean several) workbooks to accomplish their tasks. As you can guess, it was getting out of control and they asked me to help consolidate all their data in one place. The issue became that they wanted ot keep using the workbook that set-up a chart that they use.

 

[GaryPanic]

not thats fine - charts are better off in xls _unless its a easy one


try this

getting all your data into one sql qry (you can add more than 1 table in the xls to access qry) then base your chart on this information in a new worksheet in the same underlying xls

Now if the data is going to be huge you need to make sure that you encompass a large range
eg total columns A1 to A 65000 (ish) (Sum A1:A65000) etc...
if you do any totalling in your rows after the qry has run you may have to re run them or create a macro to do this

hope this helps

the size and the structure of the chart will dictate the best route to take
check charts in access for their limitations

 

[gemma-the-husky]

excel data is rarely normalised correctly.

sometimes it's hard to convince customers - but they would in general be better not relying on the excel data, and instead relying on an access system that can demonstrate the appropriate level of integrity.

that's why they are talking to you, isn't it? no doubt they are using the wrong product for job they are trying to do. Excel is great for many things, but fast database use isn't one of them, imo

 

[DavSam]

At long last and adding a few more gray hairs, it's working like a charm (and my wife says I'm not trainable...). Thank you all for all your help, I couldn't have done it without you.