Access Front End to SQL Server: The Next Step !!

[ECEK]

I have an all singing all dancing Access front end that is linked to a live SQL Server database.

I would like to know what the best way to start replicating the Access part from within SQL.

Is this SSRS development?

Your thoughts are much appreciated.

 

[Minty]

A SQL database is a storage medium that you provide a front end for.
SSRS is good for pretty reports but can't manipulate data.

What are you trying to achieve, and for who?

 

[ECEK]

I want to build a front end reporting suite utilising Access. I was wondering if SQL Management Studio does everything that Access can do?

 

[Minty]

Who is this for? Are your users local to the SQL server, office based staff or road warriors who are out and about. If they are local simply use your access database.

SSMS is a tool for managing SQL Server - you can write stored procedures, create views and queries but it has no ability to create a user interface front end for end users.

Access is exactly that, but doesn't work well over a WAN unless you are pretty skilled in VBA coding, or want to use remote access technology.

The other route would be to create a web based reporting suite, SSRS is such a tool. Or create some web pages that pull content from your SQL server.

 

[ECEK]

Thanks for the reply:

The sql server is live data. I want to build something that periodically reviews the data and build a front end automation that drags the data in and has forms etc to manipulate the pulled data.

It's over a LAN network but I was under the impression that SQL Studio could do the same as Acess.

it's looking like I may as well build what I want in Access and stick to it !!

 

[kevlray]

Another thing to remember about SSRS. It works best if you have a Sharepoint server to host the reports.

 

[Minty]

My advice would be to simply link to the live data as you have, and make your manipulations as query results that present the data in reports or non editable forms so you don't have keep importing and refreshing the copied data.

 

[AccessBlaster]

Thanks for the reply:

The sql server is live data. I want to build something that periodically reviews the data and build a front end automation that drags the data in and has forms etc to manipulate the pulled data.

It's over a LAN network but I was under the impression that SQL Studio could do the same as Acess.

it's looking like I may as well build what I want in Access and stick to it !!

I use SQL Server Management Studio 12 to maintain backend tables, for example adding a new table, fields or indexing. I use Microsoft Access to develop the frontend. If you have both, along with the right permissions life is a lot easier.

 

[ECEK]

Thanks all. I think it's clear to stick to Access for the front end. As soon as I have dragged my data from the server I want to store and manipulate the data via forms for end users. It also needs to produce reports. Presently this is done in the same way utilising Excel but the manual exercise is getting too laborious.