Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Query Help
I have a MS Access (Access 2003) query that I need to be linked to excel
(Excel 2003) and vise versa, not everyone in my company has MS Access so they need to be able to enter information into this excel document that will then update my access query, i think this possible, but i just don't know how to do it. Can anyone help? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Query Help
hi
on the menu bar.... dataimport external datanew database query follow the wizard. set sorce = MS access db brouse and select your access db the query wizard has four screens. 1.scroll down and select your query select all of your fields. 2.filter if needed 3. sort if needed 4. return to excel or edit in ms query. note: if you have prompts for parameters(cirteria) in the query, you will have to move them to the excel side or you will be error messages ie too few parameters - expected 1(or whatever) you can have criteria such as date yesterday with no errors but if the query prompts for criteria(asks for input), these must be moved to the excel side. do this on screen 4 edit in ms query. post back if you have problems. Regards FSt1 "Suzanne" wrote: I have a MS Access (Access 2003) query that I need to be linked to excel (Excel 2003) and vise versa, not everyone in my company has MS Access so they need to be able to enter information into this excel document that will then update my access query, i think this possible, but i just don't know how to do it. Can anyone help? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Query Help
Hi, this works fine for getting information from Access to Excel and keeping
it updated, but when I enter information in my Excel spreadsheet it does not get updated in Access, maybe i'm doing something wrong? Is it possible to keep my Excel spreadsheet updated through Access and also keep Access updated through my excel spreadsheet, as i explained before not everyone in my company has Access, so they need to be able to enter information into Excel and have that information go into Access. Hope that made sense. "FSt1" wrote: hi on the menu bar.... dataimport external datanew database query follow the wizard. set sorce = MS access db brouse and select your access db the query wizard has four screens. 1.scroll down and select your query select all of your fields. 2.filter if needed 3. sort if needed 4. return to excel or edit in ms query. note: if you have prompts for parameters(cirteria) in the query, you will have to move them to the excel side or you will be error messages ie too few parameters - expected 1(or whatever) you can have criteria such as date yesterday with no errors but if the query prompts for criteria(asks for input), these must be moved to the excel side. do this on screen 4 edit in ms query. post back if you have problems. Regards FSt1 "Suzanne" wrote: I have a MS Access (Access 2003) query that I need to be linked to excel (Excel 2003) and vise versa, not everyone in my company has MS Access so they need to be able to enter information into this excel document that will then update my access query, i think this possible, but i just don't know how to do it. Can anyone help? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Query Help
hi
yes it does make sense but you have a problem. the way i told you is a one way street. sending info to access is the other one way street. and you are in danger of getting caught in a round robin where data goes from excel to access then back to excel. sort of like chasing your tail. and you'll have data stored in 2 places. most database managers think this is redundunt. work around.... if your excel data is layed out like a database ie. fields(columns) and records(rows) then you can declare it a named range. this named range can be linked to access as a table and access will recognize it as a table. then to up date the table in access all you have to do is re-declare the named range in excel. a before save event should do it. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim srng As Range Sheets("sheet 1").Select Range("A1").Select Set srng = Range(Range("A1"), _ .End(xlDown).Offset(0, 7)) 'last offset =#columns in your db. ActiveWorkbook.Names.Add Name:="yourrangename", _ RefersToR1C1:=srng End Sub now each time data is add to excel and saved, the linked table in access is updated. the access table you now have, is no longer needed unless you are currently using it in a millior queries. also ms query is no longer needed. post back if problems Regards FSt1 "Suzanne" wrote: Hi, this works fine for getting information from Access to Excel and keeping it updated, but when I enter information in my Excel spreadsheet it does not get updated in Access, maybe i'm doing something wrong? Is it possible to keep my Excel spreadsheet updated through Access and also keep Access updated through my excel spreadsheet, as i explained before not everyone in my company has Access, so they need to be able to enter information into Excel and have that information go into Access. Hope that made sense. "FSt1" wrote: hi on the menu bar.... dataimport external datanew database query follow the wizard. set sorce = MS access db brouse and select your access db the query wizard has four screens. 1.scroll down and select your query select all of your fields. 2.filter if needed 3. sort if needed 4. return to excel or edit in ms query. note: if you have prompts for parameters(cirteria) in the query, you will have to move them to the excel side or you will be error messages ie too few parameters - expected 1(or whatever) you can have criteria such as date yesterday with no errors but if the query prompts for criteria(asks for input), these must be moved to the excel side. do this on screen 4 edit in ms query. post back if you have problems. Regards FSt1 "Suzanne" wrote: I have a MS Access (Access 2003) query that I need to be linked to excel (Excel 2003) and vise versa, not everyone in my company has MS Access so they need to be able to enter information into this excel document that will then update my access query, i think this possible, but i just don't know how to do it. Can anyone help? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing Data via Web Query - Can values be passed to query? | Excel Discussion (Misc queries) | |||
Anyone Else Use Database Query to Query Another Sheet in the Same Excel Workbook? | Excel Discussion (Misc queries) | |||
Anyone Else Use Database Query to Query Another Sheet in the Same Excel Workbook? | Excel Discussion (Misc queries) | |||
Microsoft Query rejects "nz" function in Access Query | Excel Discussion (Misc queries) | |||
How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) |