Category: Windows Server 2008 R2


I have been tasked with working with an Access 2010 database. this database needs to be available to non-technical users for basic data entry, and the CEO of my company needs to be able to run reports off of dynamic data. My first thought was to make a custom ASP.NET solution off of the database back-end, but let’s face it- that’s a lot of work. So I decided upon another route.

  • The database will be split in Access into a front end and back-end.
  • The front-end’s will be turned binary and distributed to multiple users.
  • In the front-end’s, our designer will create the forms needed for users to input information.
  • We will link the back-end access file to an instance of SQL.
  • The back-end file will remain editable, for the database creator to add data and fields.
  • SQL Server Reporting Services will connect to the linked SQL database.
  • I will pre-configure reports for the CEO.

This article takes into account that you know a thing or two about computers.

  1. How to share and distribute an item.
  2. The differences between file formats (.accdb, .mdb, .accde)
  3. You have all the required software installed and configured (SQL Non-Express, Access, IIS 6+).
  4. You are willing to use new tools.
  5. You are familiar with connection strings and working with database connections.

My set up (And you should have most of the items here, or at least comparable).

Member server running Windows Server 2008 R2.

SQL Server 2008 R2 Enterprise- most full editions will work.

SQL Server Reporting Services- this will not run on some versions of Express, and I had trouble getting it installed on a Small Business Server 2008 server.

Micrososft Access 2010. Access 2010 Help.

We are going to download a few more items to make this easier. These items are not always necessary, but just the way I went about it with limited database knowledge.

Download and install SQL Server Migration Assistant 2008 for Access from Microsoft here.

Download and install Microsoft Access Runtime 2010 here. You should not need this if you have Access 2010 installed (which I did and do), but the installer for the first tool told me to do it, so I did 😛

Optionally, I also installed Microsoft SQL Server 2008 Reporting Services Add-in for Microsoft SharePoint Technologies.

You need to have your Reporting Server up and running, and configured. For help on doing this, visit this page. Once you are set up, make sure your Report services is running of an SQL instance. For this walk-through, mine is named

MEMBERSERVER\REPORTING

My websites (automatically configured by SSRS) are set as

http://memberserver/reports

-and-

http://memberserver/reporting

Lets begin.

Create a Shared Drive on your server or on a secure PC. Enable the drive to be accessible by Everyone, or Authenticated Users. I created my share on the member servers C: drive and named it theDatabase (UNC location: \\memberserver\thedatabase).

I am going to drop my Access 2010 database file there, which is still in .accdb form. The database has roughly 6 tables, 300 rows in the main table, and 20 columns. It will receive roughly 6 entries per week max, from no more than 2 users. As you can see- small scale project.

Inside of this shared folder, create a folder named Backup. At this time, before we do anything, you should make a backup of your Access database, make the filename into todays date and time, and place it in this folder. We will create several backups throughout this process, and they should all follow this format and location.

With the file in the right place, and backed up, open up the tool we installed SSMA 2008 for Access.

SSMA Wizard

SSMA Wizard

You are presented with a wizard (I love wizards) that tells you the upcoming steps. Let’s use it:

Click Next.

Enter a name for the project (not the database or connection). I named mine SQLMigration1. Make sure the last box is set on SQL (for this blog post. You can change this if you are doing other sources).

SSMA Project Name

SSMA Project Name

Click Next.

Click Add Database (or find database, but we know where it is). In the popup, browse to the folder we created and placed the database in, then select it and click Open.

Add Database File

Add Database File

You might at this point receive an error. Reading this error, you can upgrade your connectivity drivers, or you can re-run the program. Mine was running in 32-bit mode (shortcut on desktop). To run in 64-bit mode, go to the file location and double-click the .exe file without the 32 on it.

The default location is:

“C:\Microsoft SQL Server Migration Assistant 2008 for Access\bin\SSMAforAccess.exe”

Repeat the steps and it should complete successfully. Select the items that you are going to import or link (in my case) to SQL. I selected both of the queries and all the tables.

Select Table and Queries

Select Table and Queries

Now type in your destination SQL server information.

Server name: MEMBERSERVER\REPORTING

Server Port: [default]

Database: WIMS

Authentication: Windows Authentication.

Since there is no database named WIMS in that instance of SQL, the wizards asks about creating it. I select yes.

Create Database Error

Create Database Error

If you want to link it, check the box. Read the paragraph for more information.

Link Tables

Link Tables

Let the tool run. You will get a popup about metadata, triggers, indexes, etc. Just click ok and let it run.

After it finishes, I have a boatload of errors- so we will send the database back to the creator to fix, but we will continue the process with this database, today.

At this point if you open up the database in access, you should see all of the linked tables. You will also see your database does exist in SQL Management Studio.

Linked Tables in Access

Linked Tables in Access

Database Created in SQL

Database Created in SQL

Close out Access, and close out SSMA. Save your project. Now backup (copy) your database into the backup folder.

Now browse to your Reporting Server site (http://memberserver/reports).

I have created a few folders here, do not mind them. On the link bar in the middle of your screen click New Data Source.

New Data source

New Data source

Now customize this page to fit your set up. Here is a link on how to create the connection string portion. the security will be of your own choosing, I use Windows Integrated.

When you are finished, Test it (you will see a green success or a red error message), then click Ok.

You will then see an icon for the Database on your home view- oops! I do not want users to see that. click the little arrow next to the database, select manage. check the box for hide in tiled view and click apply changes, then navigate to home again.

Now click on Report Builder, and get to making your reports. I will get into that in my next post, mostly because I do not know how to use the tool well as of yet.

Now we are going to split our linked Access Database into a front and back-end. We will leave the back end in the shared folder, and distribute the front end. when users make changes to the front end, they trickle down to the back-end, which replicates to SQL, and is then displayed in your reporting.

Open up your Access database. Click on Database Tools Ribbon, and then select Move Data > Access database.

Access Move Data

Access Move Data

Select Split Database. Save it, I typically leave the default formatting alone- which is to add _be (back-end) to the file name. This is the file you leave alone. you now give out copies of the original file for people to access the database.

You can make this a compiled project by saving it as accde, in the file menu> Save and Send, then save as ACCDE.

Advertisements

This is information that exists all over the place- the problem is that it is spread around forum comments and the solutions are as plentiful as the questions. I am going to explain how to create connections strings in an ASP.NET web application. The app is hosted on IIS 7.5, Server 2008 R2, and created in Microsoft Visual Web Designer 2010 Express (VWD). For the back-end I use SQL 2008 R2 Standard. VWD uses SQL 2008 Express natively, but I am not going to downgrade for this app.

There is also a little issue of server architecture. I was running into all sorts of problems with IIS and VWD being x64, while the connections drivers were running as x32.

A couple of key points.

  • In order for your application to connect to a database of any sort, it needs a connection string, which is a bit of code inserted into the web.config file in the root of your site.
  • The connections string tells the app: the source of the data, the driver to use, and the data location.
  • Each connection to a database needs to use a specific driver.

The first situation in which you need a connection string is for your app to contain membership information. Most of this stuff is done through wizard-like windows in VWD, but you will often need to change the default settings. This database is created by default when you Configure ASP.NET from within VWD. But I would rather create my own database, and hook up my application to it.

So I first create my database. Open up SQL Server Management Studio. connect to the instance you wish to have your app use. You can create a new instance, but I had an old one not being used, named MyServer\SQLEXPRESS. Do not be confused- this is not actually and Express database, just the name of the instance.

Connect to the instance, use Windows Authentication– this is of course your choice.

Connect to SQL Instance

Connect to SQL Instance

I’m going to glaze over the rest, but here’s what has to happen. You need to create a database, the default settings are alright. You need to grant the account that runs your application pool access to the database. My apppool runs under its own identity, and that identity needs Connect, Read, Select, Update, and Delete rights on the database.

For your ASP.NET security database, it needs to be prepared with a tool for ASP.NET to access it. Make sure you run the right tool, there are many.

C:\Windows\Micrososft.NET has a Framework and a Framework64 folder. Choose the one which is the same architecture as your OS.

So C:\Windows\Micrososft.NET\Framework64\ and then choose the folder that corresponds to the app Framework Version. This can be found many different ways, but if you right-click on the address of your app in solution explorer, and select property pages, you can get an idea.

Framework

Framework

 

So  C:\Windows\Microsoft.NET\Framework64\v4.0.30319\ and click on the application named aspnet_regsql.exe. This will run you through a wizard that prepares the database to hold site membership information.

When you use the wizard in your ASP.NET configuration, it will create all the required connections strings and providers for you, I had to tweak mine a bit.

Here is an example of the connection string I used.

<add name=ApplicationServices connectionString=data source=.\SQLEXPRESS;Integrated Security=True;Initial Catalog=aspnet; providerName=System.Data.SqlClient/>

 The name is only the name of the connection, this san be whatever you wish. The connectionstring is important. this has more arguments such as username, password, security, etc.

 
Important: data source=.\SQLEXPRESS;
This is the source instance. .\ means the local box. You could also do:
 
localhost\instancename
servername\instancename
remoteservername\instancename
  
Then there is Initial catalog=aspnet
This is the database name that this connection uses. There are other ways to format this, but Initial catalog=databasename without prefix works the best for my app.
With the ASP.NET membership security comes some other entries in your web.config, and these need to point at the connectionstrings NAME, in this case AplicationSecurity.
Here are two more examples of connection strings:
<add name=TheDatabaseSQLConnectionString connectionString=Data Source=******MEMBER\SQLEXPRESS;Initial Catalog=TheDatabaseSQL;Integrated Security=True providerName=System.Data.SqlClient;

/> 

 

 <add name=ContactSQLConnectionString connectionString=Data Source=******MEMBER\SQLEXPRESS;Initial Catalog=ContactSQL;Integrated Security=True providerName=System.Data.SqlClient/>

<add name=ContactConnectionString connectionString=Data Source=C:\TheDatabase\thedatabase2\App_Data\Contact.mdb;User Id=Admin;Password=; providerName=Microsoft.Jet.OLEDB.4.0;Data/>

You will notice that the data sources on the second one points to a file location, .mdb. this is because it is a Microsoft Access database, which is not hosted, only a file. you also see the provider name is actually the driver that is used for the connection, Jet in this instance.

You can get these connections strings from inside of VWD.

Click Tools>Connect to Database.

Add Data Connection

Add Data Connection

 

The Data Source is the driver you will use for your connection. I use SQL Server and Microsoft Access.
Click the drop down box and select the server\instancename where your database is located.
 
Select your authentication, mine is Windows.
Select or enter database name is used mostly with SQL databases, pick the database you created or have for the link here. With Access,  you can use Attach a Database file.
 
Now Test Connection, if everything is configured and set up, you should get a success message.
Now click Advanced.
 
There are more things to customize here. In the bottom is a box- this is your connectionstring. You can copy this information and paste it into your web.config, then edit the formatting to match the examples above.
Advanced Properties

Advanced Properties

With these connectionstrings in your web.config, you can drag AccessDatasource and SqlDataSource controls from the VWD toolbox onto your application, and then point them at the correct databases.
 

<asp:SqlDataSource ID=”ListDataSource” runat=”server”ConnectionString=”<%$ ConnectionStrings:ContactSQLConnectionString %>SelectCommand=”select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = ‘Contact'”></asp:SqlDataSource>

 

 

<asp:SqlDataSource ID=”SqlDataSource1″ runat=”server”

ConnectionString=”<%$ ConnectionStrings:ContactSQLConnectionString %>

SelectCommand=”SELECT * FROM [Contact]”></asp:SqlDataSource>

 

Note: For Access database files, make sure to add them into your App_Data folder in the application, then point to them there.There is another thing to mess around with when talking about application connections. Well drivers and all that- but I won’t go into that here.

I was having one hell of a time getting my application to talk to my databases. that is because when you design in IIS 7.5 x64, and VWD x64, your application is ment for x64 machines. It does not talk well to x32 drivers (in theory it should, with Microsoft’s backwards compatible drivers). So I changed my application to x32. This can be done using the Build>Configuration Manager… menu in VWD. This can also be set on your application pools in IIS. I changed the apppool serving my web application to support x32, and everything is working great with my older databases and .JET drivers. Right click on your application pool, select Advanced Properties, then change the value for enable 32-bit Applications to True.

Enable 32-bit Applications

Enable 32-bit Applications

%d bloggers like this: