Combine different Access Databases to one SQL database.

[jp2468]

I created different Access Databases from time to time for different departments. Each database is used for different purposes, has different functionalities, different fields, and for different end user/users.

Recent discussion from our company (We are small company with only me as a database person) is to combine all of those different Access databases to one SQL database. I think since each database is unique, there is no duplicated information needed to combine, so that may not be a good idea to combine all of those Access databases together as one SQL database. It is not only just unnecessary, make thing harder for IT staff, but also will confuse end users.

I know at here, I can find a lot of Access database experts. Does anyone can give me your input that if combining different Access Databases to one SQL database is a right thing I should do, or I shouldn’t do? If I should not do, beside the reasons I mentioned above, would you please give me more reasons which I can use to convince other co-workers?

Thnaks a lot for your input.

 

[Galaxiom]

SQL Server is a database engine. It is used as a backend only and has no facilies to provide a user interface. As such it is not a complete substitute for Access

There is not a lot of point to moving the backend to SQL Sever unless you are going to take advange of its extra capabilities. These include the ability to host larger databases, effective finely grained security and stored procedures. If you are just going to link tables to SQL Server then don't bother.

 

[jp2468]

Galaxiom,

Thank you so much for your input, I totally agree wiht you.


Thanks.

 

[spikepl]

Cost/benefit: You have a working setup. Shifting all to SQL-server can be a lot of work. And when done, what is the actual gain? Ask the proponents that.

 

[jp2468]

SpikePL,

Thanks for your input. I am much appreciated.

 

[bparkinson]

If you have the opportunity to move to SQL Server, consider that the benefits go on forever, and the cost is a one-time thing. Using Access as a RAD tool against SQL Server is SO MUCH better than a pure Access environment. Views, Stored Procedures, Triggers, Scheduled Jobs, real batch processing, Transact-SQL. Many things of real value. It's actually possible to build a two-tier client server app with a fairly light client.

 

[spikepl]

@bparkinson What are the benefits of moving applications that already work as-is? If it ain't broke, don't fix it. "Fixing" unbroken applications can consume a lot of resources, for some benefits that the applications do not need, which is proven by their happy SQL-server-less existence so far. Unless there are some new needs, but those do not appear from OP's post.

 

[bparkinson]

The benefits are just what I said - Views, Stored Procedures, Triggers, Scheduled Jobs, real batch processing, Transact-SQL. Many things of real value. It's actually possible to build a two-tier client server app with a fairly light client.

Those benefits don't necessarily go to an existing working app, and I agree that there is a cost. The benefits are realized in all future development.

If I had a small client with a working Access-based app, and no plans for growth and developing new apps to support that growth, I'd leave them on their Access-only app.

My clients all plan to grow their businesses, though, and it's much easier for someone who knows SQL Server to build complex apps against SQL Server DB's than Access DBs. It's just a much richer environment.

 

[Galaxiom]

It should also be mentioned that changing to an SQL Server backend is not as straightforward as it might seem to someone who has heard of the benefits but doesn't realise the full extent what is involved.

Performance is not automatically enhanced and in some cases may be degraded. It all depends on several factors that need to be understood before embarking on this exercise. It is possible to encounter things that stop working even if you simply move the tables to the server and relink them.

http://msdn.microsoft.com/en-us/library/bb188204.aspx

 

[bparkinson]

It should also be mentioned that changing to an SQL Server backend is not as straightforward as it might seem to someone who has heard of the benefits but doesn't realise the full extent what is involved.

Performance is not automatically enhanced and in some cases may be degraded. It all depends on several factors that need to be understood before embarking on this exercise. It is possible to encounter things that stop working even if you simply move the tables to the server and relink them.

http://msdn.microsoft.com/en-us/library/bb188204.aspx

As an Access developer who uses SQL Server exclusively, and is a big proponent in this forum of using it, everything Galaxiom has said is true. You have to learn to think differently about how to architect an app. You have to learn T-SQL, and how and when to use the various SQL Server things like stored procedures, etc. If your SQL Server DB is in the cloud, it can be slower than a local Access DB (but overall faster, if you use it right).