Access 2007 as Frontend, SQl Server 2005 Express as Backend

[w1fini]

Hi,

as more and more people are using the database, I decided to put all the data into a SQL Server backend and give the users only the frontend(Forms and Reports with linked tables).

From the Design point of view I have created queries that run on the linked tables. Forms and Reports are based on the queries and I try to filter as much as possible to reduce the network traffic. Because some users are supposed to connect via Internet/VPN to the SQL Server.
While I am testing on my local maschine everything seems to be running fast. But my concern is that when the users fills out forms the data will be populated with every update of a control. Do you think that this will generate a lot of traffic or does the linked table management handle form inputs?

Is there any way to suppress the save of data on every change and make the submit only on a click on a button event?

Any Ideas will be appreciated.

 

[Galaxiom]

Run the FE via Terminal Services over the VPN rather than simply running on the remote machine. Then just the keystroke and mouse movements are transmitted and the screen updates received. This is faster and much more reliable because the FE is running on the server and doesn't half do a task if the Internet connection fails.

 

[w1fini]

Thats an interesting design view. Will it implizit that I have only one accdb file?
If yes does Access manage the variables for every user opening a accdb database seperatly or are the variables shared?
To get Terminal Service working with Access do you need additional licenses?

Can you provide a good guide of how to set up terminal service and Access 2007? As this is a fairly new topic to me I would investigate the issue a little further before putting to much efford into applying it.

 

[Galaxiom]

Each user should still have a separate FE.
On Terminal Services each user is effectively separated like on their own PC except of course for the common SQL Server files.

Presumably if you run the FEs in the free Access 2007 Runtime there should be no need for extra Access licencing.

Terminal Services licencing is explained here.
http://www.microsoft.com/windowsserver2003/howtobuy/licensing/ts2003.mspx

The TS client software is mstsc.exe included in Windows.
The server is listed in Services as Terminal Services.

It is quite simple to get started. Make sure the service is running on the server.
Run the client and fill out the options. These can then be saved and the resulting file run from a shortcut.

 

[w1fini]

So I dont need a Access runtime license. But I will need a Windows Server Client Access License for everyone who will connect to the Terminal Server, right? What about if I call Excel to copy some data in Excel Sheets. That will definitly burst all the licensing scheme...

Assume I follow the the Terminal server approach(as I have never set up a Terminal Server so that might be a bloody newbee question):
Do I create different folders for every user containing the accdb file so they dont execute it from an other?

 

[Galaxiom]

Each concurrent TS user would need a licence.

Terminal services runs in a window on the client PC which is connected via the VPN. So provided you run your local copy of Excel on the PC there is no problem.

Several settings allow you to tune your expereince.
Local drives can be made available to the TS session.
The clipboard can be enabled between the TS and the PC.

Each user has their own account set up on the server like any other login.

 

[w1fini]

Well, I my specific case Access copies via VB some values into different Excel Sheets.

Will VB adress the local Excelapplication or will it stay within the Terminal Server?

 

[Galaxiom]

I don't think you can address the Excel session on your PC from the TS session.
However you could export the Access data to an Excel file and then open that with the local Excel application.

You could also use an ODBC connection to the SQL Server from Access. If you are not actually processing the data from the spreadsheet you would no be risking the integrity of the data on the server by just reading it.

The same would apply to the FE database if you are not writing back to the server. You could skip the TS provided you were to going to write. The worst that could happen is corrupting the remote display. All you would need to do is refresh. However you would want to use only well thought out pass through queries to keep down the traffic.