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

miffe

New Member
Joined
May 3, 2006
Messages
15
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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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

New Member
Joined
May 3, 2006
Messages
15
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

Board Regular
Joined
Aug 9, 2006
Messages
217
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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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...
Code:
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

New Member
Joined
May 3, 2006
Messages
15
Excellent Ideas, I'll try implementing today.
Many many thanks for the help!
 

Forum statistics

Threads
1,136,616
Messages
5,676,835
Members
419,653
Latest member
analyticalchemist94

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top