SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Microsoft Access 2010 & SQL Server 2008 Express


Microsoft Access 2010 & SQL Server 2008 Express

Author
Message
blackwell
blackwell
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 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?
Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67149 Visits: 9671
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 .
SSH
SSH
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 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.
SSH
SSH
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 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.
Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67149 Visits: 9671
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.
SSH
SSH
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 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
Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67149 Visits: 9671
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.
SSH
SSH
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 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
greg 65771
greg 65771
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 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.
kevaburg
kevaburg
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4463 Visits: 1025
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search