February 17, 2010 at 2:46 pm
I have an application which is Access 2007 (accdb), i need to convert to Sql Server 2005.
Please let me know what is the best approach?
February 19, 2010 at 8:47 pm
Hi keywestfl9,
If you haven't already, then consider having a look at the SSMA (SQL Server Migration Assistant) for SQL Server 2005. I've only had the opportunity to use the 2008 equivalent as MS Access 2007's upsizing wizard didn't recognise the latest version of SQL Server!
I've admittedly only used SSMA the once, and even then I clicked my way through it without having to take much notice of what was happening - I expected to have to tweak the end result and I did - but from what I remember it seemed a relatively nice wizard as far as wizards go.
Here's the download link for the SSMA for SQL Server 2005 from the Microsoft website (it didn't seem to embed properly so you'll need to cut and paste it into your browser).
http://www.microsoft.com/downloads/details.aspx?FamilyId=4ECD1E67-C64E-49E6-821E-C4D83D9D5FED&displaylang=en
Have a play with it, and if you do, then let us know what you think of it, and also if you believe it's worth any of us others using it. Most importantly tell us the problems and irritations it gave you - I'm sure you'll have a few grumbles!.
Cheers,
RF
_____________________________________________________________
MAXIM 106:
"To know things well, we must know the details; and as they are almost infinite, our knowledge is always superficial and imperfect."
Francois De La Rochefoucauld (1613-1680)
March 13, 2010 at 9:20 pm
I have used an much h older version of the Migration Assistant.
I'm not sure if you are planning on moving the Forms to .NET or staying with Access.
A lot of Access programmers use Reversed Words for tier Column names and embed space.
Another consideration is are the tables normalized?
If you are moving to .NET know may be the time to correct these issues. If you tables are not that large you could use views with column aliases to reflect the Original Code until the GUI is migrated.
I clean up the Access queries because they look very messy.
I also pay close attention to the Indexes when performing the migration.
If there is a lot of VBA Code then you may need to create some SSIS Packages.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 15, 2010 at 6:59 am
We used the SSMA for Access 2003 mdb and SQL 2005, with excellent results.
If the app uses reserved words, or spaces quotes etc. in the table or column names, you can use Find and Replace from rickworld.com. The paid version is around $40 USD and has more features. You should do that before you run the SSMA & thoroughly test before converting.
Every table needs to have a primary key, otherwise you won't be able to add records. Likewise, bound forms need to have the primary key on the form in order to add records.
If you use VBA to open a recordset, and you have an Autonumber (IDENTITY) field you will need 3 arguments:
OpenRecordset("your table or sql string",dbOpenDynaset,dbSeeChanges)
Access allows you add / subtract dates, e.g.
[date_field] - 7
but for that to work with SQL back-end you will need
DateAdd("d",-7,[date_field])
March 15, 2010 at 8:58 am
I converted some Accounting Applications from Access to SQL Server several years ago for a Fortune 100 Company.
If you don't create a Primary Key or at least a Unique Index than you can't create a Foreign Key Constraint.
I took the time to go through each query and remove all the (), etc from each query.
I aligned the code to make it easier to read and in the process I tuned each query.
The migration tools are nice but in my opinion if you do not closely examine the entire Database you risk the potential of potential problems.
Please consider Security for Access Security is a joke.
I recently had an experience where this company hired a consultant to migrate an Access Database to SQL Server.
I examined the tables & it was not in 3rd Normal Form.
There were not primary Keys so I could not enforce referential integrity.
The Access Database was created by a user. They were very bright and the application worked well when it was used by 15 users but eventually it was accessed by over 100 users. The design was subpar & it was a nightmare to Administer.
I'm very picky about the alignment of code. Not nearly as good as many of the forum members but when you convert an Access Query to a Stored Procedure it is very sloppy & hard to read.
You may not think that it is worth the hassle to go through the extra effort but in the long term the ROI may be worth it.
Regards,
WC
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 15, 2010 at 9:18 am
If you are going to continue to use Access as the Front-End you may want to consider an Access Data Project as opposed to a regular Access Database.
http://www.fabalou.com/Access/Tutorials/ADP_UserManagement.asp
http://articles.techrepublic.com.com/5100-10878_11-5065669.html
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 16, 2010 at 6:05 am
Try to use DBConvert tool from http://www.dbconvert.com/convert-access-to-mssql-pro.php
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply