Exporting tables from MS Access to SQL Server 2008 Express R2

[Philip Mark Hunt]

Windows 7 Home Premium
SQL Server 2008 R2 Express
Microsoft Office Access 2007 SP2 - 32 bit version

This post was originally put on SQL Server Tools, but somebody there
suggested my problem is more Access related, so I should post here instead.
I hope I've chosen the correct group as I couldn't really find one that
seemed totally correct.

I am trying to use the 32-bit SQL Import and Export 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.

The file permissions on the Access side are also the most open (highest)
possible.

One person on SQL Tools suggested I could also approach the data transfer
from another side. As Access can link to SQL Server tables, I could do the
data transfer within Access.

I have decided to try this approach of sending the data from Access. 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.

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

Please help.

With grateful thanks in anticipation.

Best regards

Philip Hunt
Medina, Kwinana
Perth, Western Australia

 

[Ed Ferrero]

Hi Philip,

Let's see if I can shed some light on this (I'm using Access 2010, but steps
should be pretty much the same).

In Access, right click a table, Export -> ODBC Database

Export table xxx to xxx in ODBC database. Click Ok. Where xxx is your table
name.

In the Select Data Source wizard, click 'New...'

Select driver
SQL Server Native Client (whatever looks like latest version on your PC,
10.0 on mine)
Next>

Enter a name e.g. MySQLserver, this will be the filename of your new DSN on
your PC. DSN = data source name.

Click Next, Click Finish

Enter description if you like, I don't.

Select a server (or just type in the name of your SQL Server) e.g. ASQLRPT01
Next>

Use Integrated Windows authentication if you are on a corporate network and
security has been set up for you. Otherwise use SQL Server authentication.

If using SQL Server authentication, you need to enter a login ID and
password. This is the same login and password that you have set up in SQL
Server Management Studio under Security -> Logins -> Enter New Login...

I suggest you try Windows authentication first.
Click 'Next>'

Check the 'Change the default database to:' option, select your SQL database
from the drop-down. (Note: if you can't do this, there is a problem with
your security setup, get help on how to set up SQL Server security).
Next>
Finish

Click the 'Test Data Source...' button. Click OK.

You are back at the Select Data Source wizard, but now the new DSN should be
selected. Click OK.

You should see a message that says 'Successfully exported xxx' where xxx is
your table name.

Next table you export, just select the DSN file you have created.

Ed Ferrero
www.edferrero.com