| Author |
Topic |
|
associates
Starting Member
31 Posts |
 Posted - 2006-05-17 : 09:03:34
|
| Hi,I was wondering if anyone might be able to help me. I'm new to SQL Server but i've been using the microsoft Access all these times. Just recently, i've been asked if it's possible to move access to SQL server as it's more stable and powerful than Access. My question is if it'd be difficult to migrate to SQL server from access from the point of view of data type, etc. (I'm sure that there must be some differences between them apart from the fact that SQL server uses client/server And my next question is how long it'd usually take to do that. I think this depends on how large the database is, doesn't it?I did some research there and found that access allows us to upsize it to sql server but at the same time, i was told that the sql server application does provide a way to migrate the access to sql server. Which way is a better way to go?Has anyone had a go with this? the reason i'm asking this is because i don't know where to start. I've been asked to see how long it'd take to migrate and how much it'd cost to shift to sql server. If anyone has done this before, would you mind sharing it with me?Your help is greatly appreciatedThank you very much in advance |
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2006-05-17 : 09:17:23
|
| We have HR System, the version II is on the MSAccess and Version III is on SQL Server. The clients who wnat to migrate from Version II to Version III i.e. for that we have devloped packages in the SQL Server..So using the packages we migrate from SQL Sever to Access. and they are quite simple but need to good testing, since many times there is some problem with the data in the Access and SQL Server.If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them. |
 |
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-05-17 : 09:29:02
|
| In Access 2002, in tools --> Database Utilities --> Upsizing Wizard, u can start(The same is in Access 2000 as well)The time of conversion depends on how complicated the database is, how many objects (tables, queries ...) are there. If there are Reports / Forms in Access, what are u going to do with those etc.Dealing with Data types differences is only a small part of it.First analyse ur existing system and try using the tool.Srinika |
|
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2006-05-17 : 17:12:38
|
| In our enviroment, we took about three months to do following.The app has180 tables --> moved to sql then linked back to them300 Querries --> rewrote 80% of them to SQL Stored Procedures50 Forms --> not major changes, reconnect to the new back end300 Reprots --> not major changes, reconnect to the new back end50 modules --> 50% changes, mostly data access related. |
|
|
|
associates
Starting Member
31 Posts |
Posted - 2006-05-19 : 10:52:02
|
| Thank you all for your replies.The database isn't that big. It's only 5 MB of file size and there are about 12 to 15 tables in there. Also, i'd like to be able to produce report just like those of access reports.In SQL server, does it allow us to produce reports like the access?I'll have a go with upsizing the access just to get a feel of it.Thank you very much in advance |
|
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2006-05-19 : 16:00:34
|
| SQL dosen't provide a programming environment where Access has it's own Database Application Programming environment.With SQL you need to use a programming language to accomplish something......anything really.So chose Borland's Delphi, javascript/html, Windows Forms(in any one of three different languages), Visual Basic 6, Flash, Cold Fushion, the list is endless as to the choices. A better word than endless is probably universal; you can use a "dataless" Access database as your programming environment. The circle completes.For reporting SQL does have a dedicated web based presentation layer called SQL Reporting Services."it's definitely useless and maybe harmful". |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2006-05-22 : 04:08:40
|
quote:
In SQL server, does it allow us to produce reports like the access?
SQL Server itself doesn't but as Sitka suggests there is SQL Server Reporting Services which can do the reporting. There are also other tools some of which will cost money.If you have the reports in Access already you also have the option to keep those reports and link the tables through as Hommer did. This is probably the quickest fix as the forms, reports and tables will hopefully need minimal changes. The queries and modules may be a different story. steve-----------Oh, so they have internet on computers now! |
|
|
|
cmdr_skywalker
Posting Yak Master
159 Posts |
Posted - 2006-05-22 : 08:40:54
|
| "how long it'd take to migrate and how much it'd cost to shift to sql server"This is a high level questions so first, you have to determine why you have to move to SQL Server in the first place. Sometimes people here its a good thing while their database is small and does not justify the cost of migration. Is it because of limited number of users and business is growing? limited capacity? Access has 2GB limit and has good for up to 5 users. Then, you have to determine what you have in Access database aside from the database tables(reports, queries, modules, applications using the database). The ones that you can migrate to SQL are the queries and the database. Here's a guide:MS Access SQL Implementation SELECT queries -> Either views, pass-through or retain the SELECT statement in Access UPDATE/DELETE/INSERT -> SQL Stored procedures or pass-through Link the tables into the SQL Server.If you decided to run the stored procedure in SQL, then update the codes to use the stored procedures in SQL (this is where the hidden cost will come up). So, before you jump in to SQL server, take a look at what you have and how you're using the Access database. The solution might be as easy as not doing anything at all or using the upgrade wizard just for the tables and link them.Or it can be as complex as creating another applications/reports platform in another programming languages and buying another COTS that cost more than the application and data itself :). May the Almighty God bless us all! |
|
|
|
|
access to sql server