Access to SQL Server to Web Based Intranet

[EddieN1]

I'm probably not in the right forum for this, so let me know where I should post this.



I have developed a modest, database application using Access 2007 and SQL Server that is maintained within a particular department. Now, we want to make parts of the data accessible on a corporate-wide basis using the company intranet. I am looking for options to do this. Would Sharepoint allow me to display formatted reports/forms using the data in SQL Server? Is there a way I could "call" Access to format the report/form? For example, could I call Access with parameters to generate a report/form, save it as a pdf file, then show the pdf file to the user? Any suggestions would be appreciated.

[Robeen]

A couple of years ago I investigated getting Access 2010 and Sharepoint working together but soon realized that there are limitations to working with Web Databases in Access 2010. I have a feeling that Access 2007 is even more inhibiting.

Also - the question of which version of Sharepoint you will be using is pertinent. When I was trying it out - I was using Access 2010 - but we didn't have Sharepoint Server 2010 yet - and that was a no-go for me because of the limitations. Even after we got Sharepoint 2010, I found the experience of working with an Access Web DB [. . . I believe that is required to tie in with Sharepoint?] to be limiting.

If your Access DB is working fine - could you perhaps set up your report in Access - create an AutoExec Macro that outputs your report to the network - and then set a Windows Scheduled Task to run the DB? Just a suggestion. If your parameters are stored in a spreadsheet . . . or a text file . . . you can have functionality in Access that will read the parameters into the DB and then use them to create the report before it is saved as a pdf to the network.

Several years ago, I worked with Visual Studio to create a front end that was available Company-wide -> that retrieved data from SQLServer, FoxPro and MSAccess databases. Worked like a charm.
If VisualStudio is an option, I can recommend it - esp since you speak of company intranet. Gives you a LOT of control over the environment and the data - that Sharepoint - or Access Web DB will rob you of.

[EddieN1]

Robeen... thanks for your input. I kinda thought a Sharepoint/Access solution was a stretch, but I wanted to hear from someone with more experience. The Access AutoExec option is intriguing. I might play with that a little. When you refer to the VisualStudio option, was that using a Windows Presentation Foundation or is that obsolete now? Thanks again, Eddie

[Robeen]

Access 2010 and Sharepoint 2010 can work together - but there are limitations to what you can do - and you hit the limitations pretty quick. T
here are some pretty basic things that you can do with Access that you hit brickwalls with if you are working in a Web Database.

I use AutoExec Macros in several of my Access DBs at work - and then just use Windows Task Scheduler to Open the DB early in the morning. When the DB is opened - if there is an AutoExec Macro in it - the Actions in there will be executed. You can also include a 'Quit' Action in the Macro that will close Access when all the Actions are run.
However . . . if you have a messagebox pop up . . . everything will have to wait till someone acknowledges the msgbox & clicks OK . . .

Microsoft Visual Studio is a really powerful tool to create Windows or Web applications. It's like the part of Access that you use to design Forms, connect to data and write VBA etc. It ships with a light version of SQL Server [Express?] - but I've mainly used it with data in external databases. There's a learning curve but once you've done a few applications - it is nice.