create a table from Accessto SQL

Question

Post reply

create a table from Accessto SQL

nabajyoti.b

SSCrazy

Points: 2177
More actions
December 22, 2008 at 10:11 am

#72317

Hi
I have table in MSAccess which i to be in SQL Server 2005.
For that i want to put the whole table structure copied without me having to write the create table statement in SQL server.
Well, i know there is one option called upsizing wizard under database utilities from tools menu.
However, this does not seem to work well.
Do we have any alternative solution to this?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

if you add the access database as a linked server, you can treat it as any otehr table...

so you could SELECT...INTO NEWTABLENAME FROM ACCESS:

for example, if you created a linked server named AccessDb, and it had a table named Employees

SELECT *

INTO ACCESSCOPY

FROM AccessDb...[Employees]

WHERE 1=2 --the WHERE clauses finds no match, but builds the table with no rows

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jmoss111-506838 SSChasing Mays Points: 651 More actions · Answer 2

If you're still using Upsizing Wizard, then you should have a look at SQL Server Migration Assistant for Access, free download from Microsoft.

Regards,

Jim

Chris Quinn-821458 Hall of Fame Points: 3192 More actions · Answer 3

For a single table it is very easy using the import data wizard:

Start SQL Server Management Studio, connect to the server, browse to Databases and right click on the name of the database that you wish to copy the table into. From the popup menu choose "Tasks", then "Import Data".

In the first screen, change the Data Source to "Microsoft Access", then browse for the Access database, and add username/password if your access database is secured.

Click "Next", then select the destination database (it should already be populated) and set the authentication options as required, then click Next.

Choose "copy data from one or more tables or views" (unless you want to write a specific query) then click "next"

Select the table(s) to be copied, and edit the destinations if required, click "Next", then choose "execute immediately" to transfer the file.

rf44 SSCommitted Points: 1535 More actions · Answer 4

Why don't you want to write the CREATE TABLE script?

If I understand what you wrote correctly:

1) You don't want to write a script.

2) You don't want to use the wizard because "it does not seem to work well".

So, what are the remaining options? Use a magic wand perhaps? 😉

Have a nice day!

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 5

nabajyoti.b (12/22/2008)
Hi
I have table in MSAccess which i to be in SQL Server 2005.
For that i want to put the whole table structure copied without me having to write the create table statement in SQL server.
Well, i know there is one option called upsizing wizard under database utilities from tools menu.
However, this does not seem to work well.
Do we have any alternative solution to this?

Here is a VBA module that loops thru the Access tables & creates a script to create the same structures for SQL Server.

http://www.wvmitchell.com/tips/Script%20Tables%20from%20Access%20to%20SQL%20(generator).htm

The code Sub MakeTables() will create a file TEST.SQL on your desktop to create all the tables; The code Function MakeTable(T As String) As String returns the script for a single table as a string that you can paste into an SSMS query window. It handles tables, single-field indexes, and defaults, but not relationships.