Import from Data from Microsoft Access

In SQL Server 2005, Im trying to append data to tables which already exist. Im importing the data through the import wizard.

The source from is Microsoft Access with no username and password.

The source to is SQL Server 2005 using OLE DB Provider SQL Server with the login information of the schema I wish to use.

I click through and the tables appear in the source. When I select all, they appear in the destination but they appear with the dbo. prefix which would regard them as new tables since the tables dont exist under that schema. I can click on the first destination table drop down text box and see all the tables under the schema there suppose to be under but its not the default. There are a lot of tables and I don't feel like using the drop down text box hundreds of times. Is there a solution to this problem?

It worked in Sql Server 200

Thanks

Scott

use dts or ssis|||

But why is it defaulting to dbo. when the table doesnt even exist and I can dropdown and see the proper table. Im even connected as the user I want to the destination database and the user is a db_owner. Creating a package wont work because we;re constantly adding tables and DTS may work but the preferred method is just to be able to import data into the proper schema

|||

dbo is the default schema.

how about qualifying the destination table with shcemaname.tablename in

the import process

|||

But if Im loggin in as Another user I would figure it would default to that user. When you say qualify in the import process do you mean change the [dbo]. to [proper schema owner].

I tried to create a package and then took the file and cut and paste dbo with proper name however since the table never existed its trying to create the table and it already exists so I get an error when I run it. When I use the drop down text box and change the table to the proper table with the proper owner it changes the option to append which is correct.

Im kinda at a loss as I feel there is nothing I Can do but hit the drop down text box for 200+ tables every time.

What I really need is a solution in the import/export wizard to show up with the destination tables as the proper schema owner?

Scott

|||

For my sake and everyone else's, Im not crazy. In SQL Server 2005 SP1 Microsoft has fixed this issue and now allows you to choose a destination schema. woooohoooo!!!!

However ... I am now getting the following error on appending data. The table structure exists and Im trying to append all data from Access tables into SQL Server tables. Not all Access tables have data. If I do one individual table it works. If I do 200 I get the following error:

- Prepare for Execute (Error)

Messages

Error 0xc0202009: {8DD4F4CE-2DD7-4856-A251-71D4206EC6DC}: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Unspecified error".
(SQL Server Import and Export Wizard)

Error 0xc020801c: Data Flow Task: The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0202009.
(SQL Server Import and Export Wizard)

Error 0xc004701a: Data Flow Task: component "Source 64 - DP_ROUTE_JURISDICTION" (6998)failed the pre-execute phase and returned error code 0xC020801C.
(SQL Server Import and Export Wizard)

Does anyone have a solution or can point me in the right direction. Does it have something to do with the Access buffer size? Ive see some posts for this error but no solid solutions. Any help would be greatly appreciated.

Thanks

Scott

|||

Can you publish the Access database anywhere so that we can reproduce the problem?

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/