Apr 26, 2012
tom

How Do I Configure SQL Server 2005 Backend?

Question

I am trying to develop an inventory/payroll system for a factory. The information will be stored on a server, Windows Server 2008 R2, and SQL Server 2005. I’m going to store the records on the server, but will develop several front-end client forms in MS Access 2010. The information is currently in an MS Access 2010 database(local), and I’ll eventually need to get this data on the server. I currently have a remote desktop access with username and password, however I can’t figure out how to get things set up. I have SQL Server 2005 installed on my computer as well. How do I start to make a few test tables, things like that, etc.

Could one of you guys recommend an SQL Server 2005 tutorial? I’m just having a lot of trouble getting started on this project, not really sure what to do. I need to configure the database in such a way that it will allow MS Access 2010 to be the front end.

Thanks for any help I receive, if I need to clarify something please just ask.

Asked by Scriptonaut

Answer

Ok, I don’t normally do this (post tutorials or hand-hold), but it sounds like you’re at least trying to do the right thing by your employer, and I can respect that.

I suggest you start with a free version of SQL Server. Unless your software is wildly popular or you generate massive amounts of payroll data, you will probably never out-grow it.

The free version is called SQL Server 2008 R2 Express.

Now, SQL Server Express ships by default with TCP/IP connections disabled (don’t stress too much about what that is, just know you need to enable them). You can find out how to do that here.

Now, what to do about that tutorial? Well, to be honest, a tutorial for SQL Server doesn’t really cut it. Databases are very complex, and you need to understand at a bare minimum 3rd normal form and primary keys. Indexes and foriegn keys come a very close second.

There’s an eBook from MSDN that might help, but it might be a bit too hardcore for a beginner and has a bit of assumed knowledge. Otherwise, Google might be able to help.


Accessing SQL Server from within Microsoft Access is actually very straight forward. It’s even got a very useful scale-out wizard that will even copy your Access database into SQL Server and update all your links and copy all your data. Your first point of call (after installing SQL and enabling TCP/IP connections) might actually be looking at SQL server through the eyes of Access. It’s likely to make things a bit less fuzzy.

The point of that is, although I could never condone using an Access back-end database in a work environment (I’ve just seen too many of those projects outgrow what an Access back-end can handle), if you need to hit the ground running, you can just start in Access and get developing, and then let Access take care of the migration to SQL for you.

Answered by Mark Henderson

Related posts:

  1. Is it possible to configure SQL Server 2005 to auto-compress backups?
  2. Configure SQL Server 2005 to take advantage of 32GB of RAM on 64-bit version of Windows Server 2003
  3. how to configure ms sql server 2005
  4. How do I configure SQL Server 2005 to accept a connection to a private or internal IP address on a private network?
  5. How to install SQL Server 2000, SQL Server 2005 and SQL Server 2008 on a single machine

Leave a comment