Windows 7 Home Premium
SQL Server 2008 R2 Express

I am trying to use the 32-bit wizard to transfer my Access data to a new SQL
Server database.

On choosing the Data Source option -

Microsoft Office 12.0 Database Access Engine OLE DB Provider and clicking on
Next>, I receive the error -

TITLE: SQL Server Import and Export Wizard
------------------------------

The operation could not be completed.

------------------------------
ADDITIONAL INFORMATION:

No error message available, result code: DB_SEC_E_AUTH_FAILED(0x80040E4D).
(System.Data)

------------------------------
BUTTONS:

OK
------------------------------


I have searched Help and found that this has something to do with
permissions, so I have gone into Server Management Studio and set my
permissions to the highest possible on everything. At the moment I am just
doing this work on my home PC. I have then retried and still get the error.

Any suggestions as to what I should be setting, where I should be looking
etc. would be welcome.

Best regards

Philip Hunt
Medina, Kwinana
Perth, Western Australia

"Philip Mark Hunt" <PhilipMarkHunt (AT) discussions (DOT) microsoft.com> wrote in
message news:02393E8B-21E7-4356-8048-E9351A0ED48E (AT) microsoft (DOT) com...
Assuming the Access db is not secured and not password protected, in the
connection settings you can try setting Admin as the username with a blank
password. If SQL Server is a 64-bit version, you might search for issues
related to loading Access data into 64-bit SQL. If SQL is 32-bit and your os
is 64-bit, there are separate, independent settings for 32-bit data sources
and 64-bit data sources, and again a search might help. I've done data
transfer with SSIS, and not the data import wizard, so I'm not sure how much
these suggestions might help.

You could also approach the data transfer from another side. Access can link
to SQL Server tables, and then you could do the data transfer within Access,
assuming it's a one-time transfer and you don't need a permanent sql
server-side solution. You could look at linked servers in SQL Server, and
setup a link to the Access database.

Philip Mark Hunt (PhilipMarkHunt (AT) discussions (DOT) microsoft.com) writes:
Obviously, the problem is with the Access database. I don't know much
about Access, but I believe it is a file-based database, thus you need
to review the permissions on the Access file. An easy way out is probably
to give Everyone read permissions on the file.

If that does not help, you may have more success in an Access forum,
since the problem is with Access.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Dear Paul

I thank you for your guidance. I have decided to try the route of sending
the data from Access but I have hit a snag. I am no beginner at computing
but I have always been on the apps side, not systems, so I am just getting
very confused with the terminology.

I have defined my database and a table in SQL Server using SQL Server
Management Studio.

I have then gone to Access and tried to Export Data but this is where I need
some really basic instruction, please.

Under Export I have chosen

ODBC Database
Export selected object to an ODBC database, such as SQL Server

I then get to a screen which is asking for a DSN Name, with the prompt -

Select the file data source that describes the driver that you wish to
connect to. You can use any file data source that refers to an ODBC driver
which is installed on your machine.

This is where I get lost. I find no file with a DSN extension anywhere on
my PC. I don't even really know what a DSN is. This is where I wish the SQL
Import and Export wizard would work, but since that avenue is blocked for me,
I need to find a way through this other maze. Please help.

With thanks in anticipation

Best regards

Philip
--
Graewood Business Services, Kwinana, Perth, Western Australia


"Paul Shapiro" wrote:

Dear Paul

Thank you for your guidance. I have decided to try to use the approach of
sending the data from Access, because, as you have correctly surmised, this
is a once-off occurrence. I have though hit a snag, as I am just not sure
of terminology etcetera; I've always been apps not systems, and this is
getting very hard for me - to be frank I am nearly in tears, because I have
been held up with this problem for 6 months, and even bought a new computer
along the way to try to get things working, as my SQL didn't even seem to be
working properly on my old XP machine.

I have used SQL Server Management Studio to define the database and, for
now, I have added just one table.

I have then gone into Access and sought to Export the data of one table from
there to SQL, but I have not been able to do so, as I am just not able to
work out what I need to answer along the way. This why I wish the Wizard
would work for me, but as that route seems blocked to me, I have to find a
way through this maze.

Under Export I have chosen

ODBC Database
Export selected object to an ODBC database, such as SQL Server.

That takes me to a screen that is asking for a DSN Name. The annotation on
the screen says -

Select the file data source that describes the driver that you wish to
connect to. You can use any file source that refers to an ODBC driver which
is installed on your machine.

I do not even know what a DSN is. I have been unable to find any file with
the extension dsn on my PC.

Please help.

With grateful thanks in anticipation.

Best regards

Philip Hunt
Medina, Kwinana
Perth, Western Australia
--


"Paul Shapiro" wrote:

Maybe the simplest approach is to just copy and paste the data if you only
have one table. If the tables have the same data structure open the Access
table in Access. Click the top-left corner box to select all rows and all
columns. Press <Ctrl>C to copy the data. If the tables have different data
structures, use an Access query to create a data set that matches the SQL
Server table columns, and use that instead of the table.

Open the SQL Server table in SSMS. Select the empty new row and press
<Ctrl>V to paste.

If that doesn't work, or you have more data, here are some instructions for
creating a linked table in Access so you can use Access to do the data
transfer.

You didn't say what Access version, SQL version and OS version you're using,
but here are instructions that will work with Access 2007.

First, make sure your SQL Server tables all have a primary key defined.
Without one Access cannot write data to SQL Server.

On the Access ribbon, click on the External Data ribbon.

On the Import tab, click on ODBC database from the More dropdown list.

Select the radio button for 'Link to the data source by creating a linked
table', and click OK.

This brings up the Select Data Source dialog. You can use either the File
Data Source or Machine Data Source. I prefer Machine, but either should
work.

Click the New button to create a new data source, and then choose User Data
Source and click Next.

In the driver list, select either SQL Server Native Client, if that option
is in the list, or SQL Server if not. Click Next, and then Finish. You will
be prompted in a new dialog for more information.

Enter a meaningful Name for your data source, like My SQL Data, and whatever
you want for the Description.

For the server, choose (Local) from the dropdown or enter just a period: .
Click Next.

If your SQL installation is supporting Integrated Windows Authentication,
choose that radio button. Click Next.

Check the box to change the default database and select your SQL Server
database. Other default settings are fine. Click Next, and then Finish.

In the summary dialog box, click the Test Data Source button. If the test is
successful, click OK and you now have created the ODBC data source.

"Philip Mark Hunt" <PhilipMarkHunt (AT) discussions (DOT) microsoft.com> wrote in
message news:5611DDE3-FD63-4293-98DA-6EA56AE8333F (AT) microsoft (DOT) com...