MS Access as front end and SQL server as backend

[kwebihaf]

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

 

[cevatyildiz]

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.

 

[G37Sam]

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

 

[cevatyildiz]

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.

 

[G37Sam]

Then the tables are probably imported rather than linked

 

[kwebihaf]

Thanks everyone for your help.

 

[cevatyildiz]

No definetively I selected the second option as Link tables.

 

[access2010]

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?

 

[bparkinson]

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/details.aspx?id=1695

 

[G37Sam]

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?

 

[bparkinson]

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.

 

[G37Sam]

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!

 

[1DMF]

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

=[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?

 

[G37Sam]

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)

 

[bparkinson]

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

=[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]

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)

Yes to both.

 

[1DMF]

Thanks guys. I am starting to find that triggers/SP's should only be used where appropriate.

I tried to bind an SP to my form and the app ground to a halt!

I certainly think string concatenation on the client side for basic GUI view updates, is better than SQL data transfer!

Also storing fullname requires constant updates if title,firstname,lastname changes (which is where the triggers come in) , but if you always concatenate on the fly as needed, fullname will always be correct

 

[bparkinson]

Thanks guys. I am starting to find that triggers/SP's should only be used where appropriate.

I tried to bind an SP to my form and the app ground to a halt!

I certainly think string concatenation on the client side for basic GUI view updates, is better than SQL data transfer!

Also storing fullname requires constant updates if title,firstname,lastname changes (which is where the triggers come in) , but if you always concatenate on the fly as needed, fullname will always be correct

Binding a stored procedure to a form is probably impossible. Here's how I'm using basic SQL Server objects from Access.

SQL Server tables I link to my Access front end. I use them as recordsources for forms. The same with views, although I mostly use views for reports, again to keep the data processing on the server as much as possible.

Triggers I use rarely, and never to enforce referential integrity.

Stored procedures I use extensively, for data operations that are separated from the user interface. Typically things that you would see coded in VBA in an AfterInsert event, for example.