January 20, 2011 at 8:34 am
What's the best way for clients to connect to an sql server? Should all clients have their own seperate Access data project or can one be shared on a network drive amongst all the clients that need access to sql server?
Also, how can I share the reports created within the access data project?
January 20, 2011 at 9:10 am
What I used to do is create a shared drive.
Then make a bat file that ran each morning or on demand to get the latest version of the application (make a local copy).
That way I could upgrade the production code without having to kick everybody out .
July 19, 2011 at 8:52 am
Hi Guys,
I'm looking to use SQL Server 2008 Express with Access 2010.
I am using SQL Server to store all the database objects and use Access 2010 as an Application front end.
Can anyone for see any problems with this?
Im guessing its a common scenario.
July 19, 2011 at 8:53 am
Also can you recommend any decent books for Access 2010 application development, i'm reasonably well versed in SQL Server development but am looking for something to help me along with the front-end forms based access app.
July 19, 2011 at 9:03 am
This is a decent setup. The only limits you'll have is the 10 GB database max size 1 CPU and 1 GB of ram (used by the server).
I've had this setup work well for years on a in-house ERP with 10 users before they "upgraded" to SAP.
A good tuning tip for access is to have a timestamp column on all the tables (see the difference in the query generated when you don't have it on a table with lots of columns). The difference on CPU is really noticable both on query execution and plan compile.
July 19, 2011 at 9:21 am
Ok Thanks, thats good to know that it works well.
Did you use a ADP? I've read conflicting sources around using them.
Also, the reason of not impementing the whole thing within Access was due to needing concurrent access for up to 20 users, and I'd read although microsoft states it can handle 255 concurrent users it starts flaging well before that number. Am i right in thinking that using SQL Server Express 2008 as the back-end would alleviate/overcome this problem?
Many thanks
July 19, 2011 at 9:31 am
SSH (7/19/2011)
Ok Thanks, thats good to know that it works well.Did you use a ADP? I've read conflicting sources around using them.
Also, the reason of not impementing the whole thing within Access was due to needing concurrent access for up to 20 users, and I'd read although microsoft states it can handle 255 concurrent users it starts flaging well before that number. Am i right in thinking that using SQL Server Express 2008 as the back-end would alleviate/overcome this problem?
Many thanks
If your coding practices are good then you should be fine.
Like I said my in-house ERP was a low volume DB so even with 10 users there was no conflit, ever.
For 20 users I'd never use Access to save the DB. I'd certainly use ADPs with sql server to host the data.
July 19, 2011 at 9:40 am
Good stuff and thanks for the prompt reply.
Just wanted to hear it from someone who'd already implemented it.
Many thanks again
March 13, 2013 at 1:05 pm
Grasshopper - Hi. Have you implemented Access 2010 ADP to SQL Server 2008 r2 Express? I am trying to do the same thing but don't seem to be able to connect client PC to server. When I try to create a new AGP project and make it create new DB on SQL Server I get "...Server Error 262: CREATE DATABASE permission denied in database 'master'...).
On the server side:
- installed all updates and patches
- enabled TSPIP
- Set Server Browser to 'Automatic'
- I have created a user/password and attached it to 'master'
- Security is set to mixed SQL Server / Windows Auth. mode
- no firewall running
- shared directory where local instance is installed ( I can see the directory on the service in file manager on the client)
On my client, via local LAN
- no firewall
- Try to create AGP in Access with choosing to create new database
- enter server and user/login info
- get the error message
Any help would be greatly appreciated.
March 17, 2013 at 7:12 am
I use SQL Server in varying editions across my network and use the Access Switchboard to allow simplified access for clients to the database. All each client requires is the Access Runtime Environment and the Switchboard complete with the connection string.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply