Query Help

[Suzanne]

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?

 

[FSt1]

hi
on the menu bar....
data>import external data>new 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]

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]

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