Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Microsoft Access 2010 & SQL Server 2008 Express Expand / Collapse
Author
Message
Posted Thursday, January 20, 2011 8:34 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 01, 2011 6:25 PM
Points: 18, Visits: 33
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?
Post #1050862
Posted Thursday, January 20, 2011 9:10 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, January 16, 2014 12:03 PM
Points: 21,376, Visits: 9,594
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 .
Post #1050884
Posted Tuesday, July 19, 2011 8:51 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 11, 2013 3:56 AM
Points: 21, Visits: 149
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.
Post #1144165
Posted Tuesday, July 19, 2011 8:53 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 11, 2013 3:56 AM
Points: 21, Visits: 149
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.
Post #1144169
Posted Tuesday, July 19, 2011 9:03 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, January 16, 2014 12:03 PM
Points: 21,376, Visits: 9,594
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.
Post #1144178
Posted Tuesday, July 19, 2011 9:21 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 11, 2013 3:56 AM
Points: 21, Visits: 149
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
Post #1144200
Posted Tuesday, July 19, 2011 9:31 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, January 16, 2014 12:03 PM
Points: 21,376, Visits: 9,594
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.
Post #1144211
Posted Tuesday, July 19, 2011 9:40 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 11, 2013 3:56 AM
Points: 21, Visits: 149
Good stuff and thanks for the prompt reply.

Just wanted to hear it from someone who'd already implemented it.

Many thanks again
Post #1144218
Posted Wednesday, March 13, 2013 1:05 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 20, 2013 3:15 PM
Points: 3, Visits: 4
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.
Post #1430592
Posted Sunday, March 17, 2013 7:12 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, April 14, 2014 8:19 AM
Points: 306, Visits: 461
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.
Post #1431979
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse