Avatar billede sturman Nybegynder
15. oktober 2004 - 11:00 Der er 19 kommentarer og
1 løsning

Sammenkæd excel ark

Hi all,

sorry for writing in english, written Danish is not that good, but feel free to write your response (if any) in Danish.

Here is my problem.

I have one excel file, with many sheets, all the sheets contain the same headings. e.g. each sheet contains customer number and customer name. The numbers may be the same in each sheet as well as the name. so they are not unique.

I can link one sheet to access, but how do i link all of the sheets to access and link those sheets together into a table, then i will link that table to the database.

The problem is that you can only link one table to one database. so i need to link all of the sheets together before i can link it to the db.

So basically when someone updates/alters a sheet in the excel file it will update it in access.

Can i link multiple sheets together ? to create one master tabel that also gets updated when the sheets do ?

If you do not understand what it is that i am asking just let me know and i will try to re-formulate the question.


S.
15. oktober 2004 - 11:05 #1
Hey Sturman,

You should be able to link all the sheets into seperate tables and then present the data as one table using a UNION-query;

table Sheet1 UNION Table Sheet2 UNION Table Sheet3

/Thomas
Avatar billede sturman Nybegynder
15. oktober 2004 - 11:13 #2
Hi Thomas

i have linked all the sheets into seperate tables. Bit of a noob on the old access, so where would i execute this SQL ? After the data is presented in one table, can it be linked to the db, if so, will this table be updated when the other sheets/tables are altered ?

S.
15. oktober 2004 - 11:19 #3
You make a new query (with no tables)->switch to SQL view->enter the SQL statement above, using your own tablenames->save the query.

Now you can use this query instead of a table.
Notice, that you can't edit the data in the query since it is a Union-query.

But data will be updated if altered in the excel-file.
Avatar billede sturman Nybegynder
15. oktober 2004 - 11:41 #4
Having some problems finding my way around in this Danish version of Access, can you elaborate on the above so i can find my way round to run this query ?
15. oktober 2004 - 11:58 #5
okay :o)

Switch to the tab named "Forespørgsler" and create a new Query using the button "Ny"->Designvining->Luk
Go to menu Vis->SQL-visning and enter the SQL statement. Close and save.

Did that help?
Avatar billede sturman Nybegynder
15. oktober 2004 - 12:14 #6
ah "Forespørgsler" that seems to be the one. Now am just getting errors on the SQL statement but that was not the question. so here are the points minus 20 till i have checked to see if the updates in excel flow through ;) cheers for you help. and onwards with sql errors. just not my day today.
15. oktober 2004 - 12:18 #7
Could you paste your SQL statement here? Perhaps I can see the error right away?
Avatar billede sturman Nybegynder
15. oktober 2004 - 12:18 #8
did you get the points ? think i hit the correct button.
Avatar billede sturman Nybegynder
15. oktober 2004 - 12:20 #9
SELECT * FROM 12 md
UNION ALL
SELECT * FROM net

getting syntax error on from clause
15. oktober 2004 - 12:23 #10
It's your "12 md", that Access is having problems with. Names with spaces and special signs needs []:

SELECT * FROM [12 md]
UNION ALL
SELECT * FROM net

Or even shorter:
Table [12 md]
UNION ALL
Table Net

no I didn't get any points, but you properbly need to click on my name in the box to the left before hitting the "Accepter"-button (you are not the only one, having problems with that!)
Avatar billede sturman Nybegynder
15. oktober 2004 - 12:31 #11
yeah it just occured to me that was the problem. after askin all these questions i think me brain just shut off.

Can this query be linked to a database ? the final part of my problem, getting it linked to a database. or do i throw this query into another table and then link that table to the db ?
15. oktober 2004 - 12:33 #12
eh? Do you want to link the Linked Query to another Access database?? Why not link directly to the Excel-sheets?
Avatar billede sturman Nybegynder
15. oktober 2004 - 12:39 #13
another program will be pulling data out of this combined data. need all the data from the various sheets in one large table, that is updated when the excel is updated. then the other program will look in this large list for what it needs.
15. oktober 2004 - 12:43 #14
sorry, I didn't realize that! You can't link to a query from another program using direct link. Then you must use VB and ADO or DAO to do so. And thats more complicated :o(
Avatar billede sturman Nybegynder
15. oktober 2004 - 12:44 #15
maybe i can send the sql query back to an excel another excel sheet that updates accordingly ? so all the data is on one sheet... ???

you should have the points now :)
15. oktober 2004 - 12:45 #16
I got the points, thank you very much. But can you use my answer at all, then?

If not, i'll return your points...
Avatar billede sturman Nybegynder
15. oktober 2004 - 12:45 #17
if it can be done with VB then i can probably manage that. but easiest would be the thought i mentioned above.. know if i can do that ?
15. oktober 2004 - 12:51 #18
It sounds like, it should be possible. But I am not that good at Excel :o(

I see some problems moving each table dynamicly under each other....
Avatar billede sturman Nybegynder
15. oktober 2004 - 13:31 #19
i see problems everywhere :) well thanks for your help on the issue of getting it all together. guess i am gonna have to VB my way around the last issue.

Thanks again for you help and as far as the points go, they're all yours

have a good weekend.

maybe arsenal will loose this weekend, one can only hope.

S.
15. oktober 2004 - 13:32 #20
hehe, good luck on the game, and you have a good weekend too :o)
Avatar billede Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Loading billede Opret Preview
Kategori
Dyk ned i databasernes verden på et af vores praksisnære Access-kurser

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester