Export 1 query to multiple ranges

[Locopete99]

Hi All,

I have a colleague who wants me to build an access database for him.

The aim is to use access to raise and store data on issues that are raised in the company, I.e a part has been manufactured wrong or sales have incorrectly specified parts to a customer.

That much in itself is a fairly straight forward operation.

Previously these were created and saved on excel spreadsheets, but there was no control over numbering (which we can use the auto number primary key field for.)

Not everyone in the company has MS Access though, so there is a requirement to export this data to excel to allow it to be sent to another department for action before being returned.


I have 2 questions.

Firstly, is there a way to export 1 query to Excel, but to different ranges (as the form is formatted). Or would this need to be multiple queries to export to each different range? To preempt some responses, I am familiar already with how to export to a single set range using VBA and TransferSpreadsheet.


Secondly, Is there a way to import from excel in which the record would just be updated with any changes?

I have a little experience with importing data into access, which currently just creates new records, which would cause primary key issues if I just want to update a record.

 

[isladogs]

Before considering how to export to Excel, have you thought about giving all users the free runtime version of Access. This would allow them to modify data but not the database design.

 

[Locopete99]

Hi,

Yes this wouldn't work as there is also permissions problems from the location it is hosted on. Whilst we need it to be stored in the location it is in, we also do not want to give all users access to that location due to sensitive information. Therefore it was deemed that the safest and best way to disseminate the information would be to be able to take just the information that the particular user needs and to send them that.

 

[isladogs]

Split the database (you should do that anyway!). Store the BE in the protected location
Give each user a copy of the FE on their own hard drive and provide the runtime for those that need it
Problem solved.