April 27, 2007 at 5:30 am
What is the best way to copy access database to SQL Server 2005 as a new database?
April 27, 2007 at 6:53 am
using MS Access-2003 and the Tools menu to launch Upsizing Wizard, the first page of the wizard offers "use existing" or "create new" db. Later pages ask about table attributes, timestamp fields etc
Convenient to have the wizard iterate each table but you may want to do some finetuning afterwards. Hence best to copy the .MDB first (eg compress) and create new SS db rather than write into your actual live production db !
In particular, you may find datatype varchar(255) etc that you want to change, and add/change check constraints, triggers, add sprocs and functions etc
Previous versions had an external Add-In but this looks like built-in nowadays.
HTH
April 27, 2007 at 7:31 am
Is it not posible from SQL Server side i.e., using any SQL tool or wizard?
I don't have access database tools installed on my box.
April 27, 2007 at 9:59 am
I had assumed that you had MS Access [on your PC] so you could do client-side uploads
- ie all part of MS Access product (or part of Office/Professional suite)
Yes, it is possible to do DTS (SQL2000) or SSIS (SQL2005) so you could author such packages
- and would be able to schedule as a SQLAgent job on SQL server-side
The downside is that if you changed the format of any Access table the process may break
- the metadata would be discovered at design-time, not run-time
for example if you changed the name, datatype or ordinal position of the columns things would break.
if you merely added additional columns the process would still work, but those columns would be ignored
Are you trying to do a one-off or a recurring [every day/week/month/year] exercise ?
April 28, 2007 at 9:16 pm
this might be of use:
http://www.microsoft.com/sql/solutions/migration/access/default.mspx
---------------------------------------
elsasoft.org
April 30, 2007 at 4:28 pm
I would agree with Jezemine. The SQL Server Migration Assistant for Access allows the most control.
May 2, 2007 at 10:43 am
Have you tried using the Import wizard from ssms?
May 2, 2007 at 10:45 pm
You will find the following Technet article helpful
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply