Updating data from a remote PC (remove error) HELP please :)

[miffe]

Greetings,

I've got an access database on a server,I've got an excel spreadsheet on a client PC... I run a query from the client PC involving some maths to develop different price lists from values stored in the DB on the server...

Problem #1: I dont want the client PC to be able to control the queries, is there a way to store the access query on the server PC and have the excel worksheet just access it on the server each time?

Problem #2: The server is shut down sometimes, but the client pc may run at times when the server is down and will still need to print reports and invoices, thats why I load it in excel and it keeps the data there even when I close it... When the worksheet loads I run a macro to 'update' all data from the Server DB... If the server is down then it says connecting to db... for a while and then comes up with an error and prompts to look for another DB...

Is there any way I can just cancel and do not update if the server is down? (The way I'm updating is that I just ran a 'get external data' query the first time, and I just use the 'Update' command when I need new info, and I recorded the action in a macro to use every startup.

Thanks a lot in advance -
miffe

 

[SydneyGeek]

The quick answer to this is Yes, you can control when users download, but you need to change your approach.

To answer #1, you can use code to pull the data from Access to Excel. Instead of behaving like a data query, the user will see the data in the worksheet and will need to click a button to refresh it. The user need now know where the data comes from, and they can't edit the query without going into code.

To answer #2, it's possible to see if the data source exists. If it does, refresh the data: if not, display a message and get out.

Denis

 

[miffe]

excellent, that worked I could manage to establish the kind of query via means of code from excel, the problem I have now is that I don't know how to check to see if the server is up and running before trying to refresh... I know I can get the user to push a button, but how will I detect if the server is running? can I do it simple? or do I have to set up a winsock system or some lengthy way like that?

thanks again -
miffe

 

[MyBoo]

excellent, that worked I could manage to establish the kind of query via means of code from excel, the problem I have now is that I don't know how to check to see if the server is up and running before trying to refresh... I know I can get the user to push a button, but how will I detect if the server is running? can I do it simple? or do I have to set up a winsock system or some lengthy way like that?

thanks again -
miffe

Why not create a running.flg on the server. Then have code on the PC check for said remote file... if exists then refresh data, otherwise exit.

 

[SydneyGeek]

One way to do it (assuming that you created a recordset based on the data you want to pull down) is to check the recordcount BEFORE downloading the data. If 0, don't refresh.

Something like...

Set rst = dbs.OpenRecordset("qrySomeQuery")
If Not rst.EOF Then
  'process
Else
  'exit
  MsgBox "Server not available -- try again later"
  Exit Sub
End If

Denis

 

[miffe]

Excellent Ideas, I'll try implementing today.
Many many thanks for the help!