Updating an excel spreadsheet to then update an access database

[EeOr]

Sorry for X-posting this I wasn't sure what newsgroup would give me the
clearest answer.

Hi I have an excel spreadsheet which links directly to access an pulls
information from a query, this spreadsheet does various things with the
information and then is used as a linked table in another access database.
my problem is that i would like for all this to happen in the background and
for the linked table to be populated with the most up to date information
available.

The data from the original access database is linked directly from a Sql
server and comes from tables which contain confidential information that I
do not want any other users to be able to access.

Just now I have to open Excel manually and click that i want the information
to be updated from the first database. When I open the table in the second
database the information is now up to date. Is there a way for the excel
spreadsheet to be opened updataed and closed all without the usere being
aware of it?

thanks

Jon

 

[Pat Hartman \(MVP\)]

You can use the TransferSpreadsheet Method/Action from Access to transfer
data to a spreadsheet. But why are you using Excel as the intermediary at
all? Why not link to a view on the server? The view can limit the data
retrieved so that sensitive fields are not included in the query.

 

[EeOr]

thanks for your response pat, The sql server database is managed by an
external company and we have been asked not to create views etc. The other
problem with doing this is that although I have security rights to access
the server other users will not have and will be prompted for the password.
Unless there is a way round this i.e. getting access to store the password
and have it hidden?

 

[Pat Hartman \(MVP\)]

I suppose you could copy the data into an Access table and link to that.
You could have the second database automate the first database to refresh
the local table. I don't like duplicating data unless it is absolutely
necessary.

You can silently log into the server by at least two methods but I doubt
that your DBA would approve of you using your id to use for the users. I
have done this by capturing the user's ID and Password in a logon screen and
then opening a connection to the server in the background. That makes the
DBA happy because all activity at the server level is restricted to whatever
permissions the user has and any activity is logged with that user's ID.