Go Back   Access World Forums > Apps and Windows > SQL Server

 
Reply
 
Thread Tools Rating: Thread Rating: 14 votes, 5.00 average. Display Modes
Old 10-15-2011, 06:22 AM   #1
kwebihaf
Newly Registered User
 
Join Date: Oct 2011
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
kwebihaf is on a distinguished road
MS Access as front end and SQL server as backend

Dear All.

I am new to the forums.

I would like some Practical Tutorials on using MS Access as the front end and SQL server as the backend server database.

I would be grateful.

FRED

kwebihaf is offline   Reply With Quote
Old 10-15-2011, 10:48 PM   #2
cevatyildiz
Newly Registered User
 
Join Date: Mar 2010
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
cevatyildiz is on a distinguished road
Re: MS Access as front end and SQL server as backend

Hello I need the same tutorials. I have used SQL upsize tool of Access 2010 but some form fields become invisible and some subform datasheets do not show the entire data rows why I don't know.

I have tried to link SQL database via ODBC but the changes didn't reflect to SQL server. owever I want to remove local tables and use just SQL Server database.

As you see I have many problems which need to be answered. Any helpful guidlines will be appreciated.
cevatyildiz is offline   Reply With Quote
Old 10-16-2011, 12:01 AM   #3
G37Sam
Car Geek
 
G37Sam's Avatar
 
Join Date: Apr 2008
Location: Dubai, UAE
Posts: 428
Thanks: 13
Thanked 51 Times in 49 Posts
G37Sam is on a distinguished road
Re: MS Access as front end and SQL server as backend

I moved to SQL Server backend a few months back, steep learning curve but very much worth it and here's how I did it.

Before you start working with MS Access, you need to make sure your SQL Server is up, running and properly setup. I ended up purchasing this very resourceful book (mind you, I knew zero SQL Server before this): Beginning SQL Server 2008 for Developers: From Novice to Professional (Expert's Voice in SQL Server)

Once that is done, select the External Data tab from MS Access -> More -> ODBC Database -> Link to datasource

A new window requesting a DSN name will pop up, hit New -> SQL Client

You will then be asked to enter your server name, login details and database. Once that is done, you'll choose what tables to link and voila, everything else from here is MS Access

Hope that helps

G37Sam is offline   Reply With Quote
Old 10-16-2011, 01:24 AM   #4
cevatyildiz
Newly Registered User
 
Join Date: Mar 2010
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
cevatyildiz is on a distinguished road
Re: MS Access as front end and SQL server as backend

Thanks for info Sam, I migrated database to SQL via Full Convert 5 Enterprise program. Then made the same import odbc selection, and dsn so on... But as I said the Access database is updating due to what I do in Access Front End but SQL is not updating. I will check the book what you told me.
cevatyildiz is offline   Reply With Quote
Old 10-16-2011, 04:04 AM   #5
G37Sam
Car Geek
 
G37Sam's Avatar
 
Join Date: Apr 2008
Location: Dubai, UAE
Posts: 428
Thanks: 13
Thanked 51 Times in 49 Posts
G37Sam is on a distinguished road
Re: MS Access as front end and SQL server as backend

Then the tables are probably imported rather than linked
G37Sam is offline   Reply With Quote
Old 10-16-2011, 06:02 AM   #6
kwebihaf
Newly Registered User
 
Join Date: Oct 2011
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
kwebihaf is on a distinguished road
Re: MS Access as front end and SQL server as backend

Thanks everyone for your help.
kwebihaf is offline   Reply With Quote
Old 10-16-2011, 07:14 AM   #7
cevatyildiz
Newly Registered User
 
Join Date: Mar 2010
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
cevatyildiz is on a distinguished road
Re: MS Access as front end and SQL server as backend

No definetively I selected the second option as Link tables.

cevatyildiz is offline   Reply With Quote
Old 10-18-2011, 04:21 PM   #8
access2010
Newly Registered User
 
Join Date: Dec 2009
Posts: 137
Thanks: 31
Thanked 0 Times in 0 Posts
access2010 is on a distinguished road
Re: MS Access as front end and SQL server as backend

Hello, the MsAccess database network in the small charity that I volunteer at for is down again and has not worked as a network for over 3 weeks.

Each Network and Access consultant that we have had come into their office is offered the same contract, "They will pay the charges, if the network is usable by 5 persons for 1 week".

All the consultants have worked on folder permissions and Access security settings without any success. The data server is a Windows 2000 server and the workstations are Windows 2000 work stations.

To enable some work in their office, I have setup one of our computers as a single Data Base user server, the people trying to help me use our databases suggest that I change our back end to SQL, which should solve our problem..

What do you think?
access2010 is offline   Reply With Quote
Old 10-19-2011, 01:17 PM   #9
bparkinson
Newly Registered User
 
Join Date: Nov 2010
Posts: 158
Thanks: 1
Thanked 19 Times in 19 Posts
bparkinson is on a distinguished road
Re: MS Access as front end and SQL server as backend

Quote:
Originally Posted by access2010 View Post
Hello, the MsAccess database network in the small charity that I volunteer at for is down again and has not worked as a network for over 3 weeks.

Each Network and Access consultant that we have had come into their office is offered the same contract, "They will pay the charges, if the network is usable by 5 persons for 1 week".

All the consultants have worked on folder permissions and Access security settings without any success. The data server is a Windows 2000 server and the workstations are Windows 2000 work stations.

To enable some work in their office, I have setup one of our computers as a single Data Base user server, the people trying to help me use our databases suggest that I change our back end to SQL, which should solve our problem..

What do you think?
Full disclosure: I was a SQL Server DBA long before I turned to Access as a development tool. I love Access as a RAD tool against SQL Server.

If reliability and uptime in a multi-user environment are what you need, move to SQL Server. I have had SQL Server database with 100% 24/7 uptime for months and months on end.

Not to mention you escape the 2GB limit on an Access DB, you get real programmability at the SQL level through stored procedures, and you get real triggers. Using these tools lets you move much more data processing to the database server, where it belongs.

SQL Server Express 2008, free:
http://www.microsoft.com/download/en...s.aspx?id=1695
bparkinson is offline   Reply With Quote
Old 10-19-2011, 01:28 PM   #10
G37Sam
Car Geek
 
G37Sam's Avatar
 
Join Date: Apr 2008
Location: Dubai, UAE
Posts: 428
Thanks: 13
Thanked 51 Times in 49 Posts
G37Sam is on a distinguished road
Re: MS Access as front end and SQL server as backend

Well now that your bring up the term "triggers", I'm curious to know, how would I benefit from using those over Access event procedures on my front-end?
G37Sam is offline   Reply With Quote
Old 10-19-2011, 03:10 PM   #11
bparkinson
Newly Registered User
 
Join Date: Nov 2010
Posts: 158
Thanks: 1
Thanked 19 Times in 19 Posts
bparkinson is on a distinguished road
Re: MS Access as front end and SQL server as backend

Quote:
Originally Posted by G37Sam View Post
Well now that your bring up the term "triggers", I'm curious to know, how would I benefit from using those over Access event procedures on my front-end?
Good question. Triggers run on the database server, in response to INSERTS, UPDATES, DELETES (actually a little more granualar). Since they run on the DB server rather than the client, they are much more efficient than, for example, VBA code in an AfterInsert event in Access. And they are asynchronous.

Sine they are written in Transact-SQL, they are procedural, which Access queries are not. You can include conditional logic in triggers and stored procedures, which means you can accomplish a lot more on the database server side. More efficient; faster; conditional processing; you can wrap data activities in transactions.

Let the UI be the UI, and let the data work happen in the database on a honking big server, is my philosophy. Or even a modest server. The fewer client-side recordsets used, the better.

Here's a trigger example, and one that violates 3rd normal form to boot: When a Person record in my database is inserted or updated, I have a trigger that fires that concatenates the LastName, a comma and a space, and the FirstName, and a space, and the first letter of their middle name, into a column called Person.Name. Another example would be re-calculating the total for an order when a new item is added to the order and writing it to the order table.
bparkinson is offline   Reply With Quote
Old 10-20-2011, 12:51 AM   #12
G37Sam
Car Geek
 
G37Sam's Avatar
 
Join Date: Apr 2008
Location: Dubai, UAE
Posts: 428
Thanks: 13
Thanked 51 Times in 49 Posts
G37Sam is on a distinguished road
Re: MS Access as front end and SQL server as backend

Quote:
Originally Posted by bparkinson View Post
Good question. Triggers run on the database server, in response to INSERTS, UPDATES, DELETES (actually a little more granualar). Since they run on the DB server rather than the client, they are much more efficient than, for example, VBA code in an AfterInsert event in Access. And they are asynchronous.

Sine they are written in Transact-SQL, they are procedural, which Access queries are not. You can include conditional logic in triggers and stored procedures, which means you can accomplish a lot more on the database server side. More efficient; faster; conditional processing; you can wrap data activities in transactions.

Let the UI be the UI, and let the data work happen in the database on a honking big server, is my philosophy. Or even a modest server. The fewer client-side recordsets used, the better.

Here's a trigger example, and one that violates 3rd normal form to boot: When a Person record in my database is inserted or updated, I have a trigger that fires that concatenates the LastName, a comma and a space, and the FirstName, and a space, and the first letter of their middle name, into a column called Person.Name. Another example would be re-calculating the total for an order when a new item is added to the order and writing it to the order table.
Thanks for clearing that up for me, I am in the process of designing my first SQL/Access database and that question took a lot of my brain space!
G37Sam is offline   Reply With Quote
Old 10-20-2011, 02:22 AM   #13
1DMF
Newly Registered User
 
Join Date: Oct 2011
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
1DMF is on a distinguished road
Re: MS Access as front end and SQL server as backend

I'm glad I'm not the only one pondering these questions when using a SQL backend with Access.

Hopefully I have come to the right place!

I am curious regarding triggers as i have been told that you shouldn't actually use them as they slow things down. Is this the case?

Also in the example for creating a fullname, I don't have a fullname field?

We have a firstname and lastname and I use an unbound field on a form
Code:
=[firstname] & " " & [lastname]
or logic in VBA to concatenate it to a fullname string from the two first/last name fields.

Is it better to have SQL create data and store it using triggers, rather than doing an on the fly string concatenation operation with already available fields?
1DMF is offline   Reply With Quote
Old 10-20-2011, 02:52 AM   #14
G37Sam
Car Geek
 
G37Sam's Avatar
 
Join Date: Apr 2008
Location: Dubai, UAE
Posts: 428
Thanks: 13
Thanked 51 Times in 49 Posts
G37Sam is on a distinguished road
Re: MS Access as front end and SQL server as backend

It isn't the normal thing to do as you'd be duplicating your data, I'd personally concatenate on need

But in general what bparkinson was trying to say is (according to my understanding at least), if it came down to choosing SQL Server on the Server (trigger) vs MS Acess on the Client Machine (event procedures) then go for SQL (let the big man do the tough load sort of thing)
G37Sam is offline   Reply With Quote
Old 10-20-2011, 01:34 PM   #15
bparkinson
Newly Registered User
 
Join Date: Nov 2010
Posts: 158
Thanks: 1
Thanked 19 Times in 19 Posts
bparkinson is on a distinguished road
Re: MS Access as front end and SQL server as backend

Quote:
Originally Posted by 1DMF View Post
I'm glad I'm not the only one pondering these questions when using a SQL backend with Access.

Hopefully I have come to the right place!

I am curious regarding triggers as i have been told that you shouldn't actually use them as they slow things down. Is this the case?

Also in the example for creating a fullname, I don't have a fullname field?

We have a firstname and lastname and I use an unbound field on a form
Code:
=[firstname] & " " & [lastname]
or logic in VBA to concatenate it to a fullname string from the two first/last name fields.

Is it better to have SQL create data and store it using triggers, rather than doing an on the fly string concatenation operation with already available fields?
What you are doing is correct IMO.

In the case where I am storing concatenated lastname and firstname, it makes sense. It's a core business object in our app, referenced all over the place, and I think it would end up being less efficient to be forever writing code to do the concatenation on the fly. I like my Access forms to be pretty much code-free, other than calling VB classes.

That said, it's better in nearly every case to do this on the fly rather than storing the concatenated or derived value in the database.

Overuse of triggers, or worse, misuse, can cause performance problems, but I've never encountered it.

bparkinson is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Access front-end query design view with backend SQL server Lifeseeker Queries 4 10-12-2011 08:48 AM
access 2003 front-end with SQL server as the backend Lifeseeker SQL Server 2 08-20-2011 01:36 PM
Front-end Access 2003 linked to backend SQL server Lifeseeker General 1 07-23-2011 09:37 PM
access 2003 front-end with SQL server as the backend Lifeseeker SQL Server 1 07-23-2011 09:36 PM
MS Access Front End and SQL server Backend ? saleemMSMS SQL Server 3 02-24-2010 06:33 PM




All times are GMT -8. The time now is 11:51 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
(c) copyright 2010 Access World