June 15, 2015 at 3:53 pm
Hello,
I need to convert an MS Access 2013 database to an MS SQL database (2008 R2 or 2012).
I appreciate any help.
Thanks!
MK
June 15, 2015 at 4:38 pm
Getting the data from Access to SQL Server is pretty easy. From SQL Server you wI'll right click on the DB where you want to import the access data, select import data, then choose access as your data source. Follow the wizard from there.
If you have any access queries you will need to create views or stored procedures to emulate that logic. It can be tricky depending on your Access query logic because T-SQL a Access' s query language have some pretty notable differences.
-- Itzik Ben-Gan 2001
June 16, 2015 at 5:49 am
Watch if you use Microsoft's conversion. We had to fight with a ZIP code column as INT which should have been a char and other cases where Microsoft helped us out.
I am glad the person who did the conversion left before I started.
July 7, 2015 at 9:21 am
djj (6/16/2015)
Watch if you use Microsoft's conversion. We had to fight with a ZIP code column as INT which should have been a char and other cases where Microsoft helped us out.I am glad the person who did the conversion left before I started.
Accepting defaults for a process as complex as moving from MS Access to MS SQL Server should NOT be considered prudent. Unless the data is quite simple, then even using the SSMS import wizard isn't really a good idea. However, Microsoft has made a number of versions of SSMA (aka SQL Server Migration Assistant), where you can specify exactly what your data types need to be and perform the migration in pieces. If the SQL Server side is new, then you can easily wipe out all the new tables data with a series of TRUNCATE statments (or you prep a query or proc to do it), and you can re-do the data part of the migration as many times as needed until you get it right. It's not always obvious as to how to get certain things to happen, but it's not really rocket science, either. NOT using that tool is probably a bad idea.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 7, 2015 at 1:58 pm
+1 for what Steve said.
SSMA is the way to go here.
July 7, 2015 at 2:07 pm
Thanks a lot for your reply!
July 7, 2015 at 2:09 pm
Thank you for your suggestion.
B.
July 7, 2015 at 2:19 pm
+ 1000 for what Steve said.
As an example, the import wizard will convert every number to a float. You really do not want to use floats in your database.
It will likely make most character field a nvarchar(255). The wizard only looks at the first 50 rows (I think) and makes a decision that will guarantee the import will work.
Run the import wizard, without the data, and do some analysis of the structure it creates.
Query the system tables.
SELECT T.name, C.*
FROM sys.columns C
INNER JOIN sys.types T ON C.system_type_id = T.system_type_id
Then ask the questions. Do you need unicode types? Are the character fields the right length? Are they the right type?
Also, the import wizard will not pick up any indexes, etc. from Access. You will have some tuning to do once the import occurs.
Do not take this lightly.
Blindly running the wizard or the upgrade tool is not the final step in the process. This upgrade is a good opportunity to correct design mistakes, data anomalies, and a lack of best practices. Put in the time.
There are far too many SQL databases that started as Access DB's. The "shortcut" of the wizards put the system in a bad place for a long time.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
July 7, 2015 at 2:37 pm
BallaKet (6/15/2015)
Hello,I need to convert an MS Access 2013 database to an MS SQL database (2008 R2 or 2012).
I appreciate any help.
Thanks!
MK
Why?
you dont provide any details on how this decision was reached and what you expect to acomplish
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 8, 2015 at 5:56 am
On top of the many excellent points mentioned, it is best to consider Access-to-Sql Server as more of a migration than a conversion (take it from someone who's been there many times). Unless it is very carefully and purposefully designed, the data structure is very tightly bound to the user interface (forms, reports, etc.) which will make any future changes a royal pain if you plan on keeping the user interface in Access.
We have an application, parts of which are mission-critical, that began life as a number of separate and badly-designed Access databases that all got lumped into one big one. Then the data got "converted" to SQL Server and Access still provided the front end.
I've spent the last five years sorting it out and I'm nowhere near finished.
Plan, plan and then plan some more. Then test, test and test even more.
____________
Just my $0.02 from over here in the cheap seats of the peanut gallery - please adjust for inflation and/or your local currency.
July 9, 2015 at 9:49 am
I've definitely been burned by letting wizards, etc. do this ..I suggest creating (via SSMS?) your tables in SQL the right way (and empty). Then put the Access data into them (fairly easy if you leverage Access' ability to link to SQL Server via ODBC , etc).
Some things will not go in or will not function the way you expect. Fix those issues.
Then test like hell.
SQL Views (similar to queries) can also be attached to in Access and be useful. Compare the Access query results to the SQL Server view to see if they are what you expect.
Assuming you will use Access as a front-end, I've found that a timestamp column is very useful in preventing Access burps. And Primary keys in each SQL table are a must.
Good luck.
July 9, 2015 at 9:35 pm
One thing that nobody has mentioned is the fact that you may have to rewrite VBA code. Can work great if your front end and back end are Access, but might not if you're using SQL Server as a back end.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply