-
1
- #1
VBADesigner
Programmer
- Mar 16, 2011
- 1
- 0
- 0
In Response to: thread962-1645677
"I am trying to import an access table to sql server 2008 express database using the import wizard. I am getting the error: DB_SEC_E_AUTH_FAILED(0x80040E4D)"
I had the same problem using the SQL Server Import and Export Wizard
This is how I resolved it:
When using the data import Wizard to import data from an Access Table, choose the "Microsoft Office 12.0 Access Database Engine OLE DB Provider" as the Database Source.
Once you have made this selction, a Properties button will appear below the drop-down box.
Click the Properties button.
In the First field, called Data Source, type in the path and name of the database (why a Browse button is not available, beats me).
For example, type: D:\Databases\ImportDB.accdb
You should of course type in your own details, in the format
<Drive:>\<Path>\<DatabaseName>
It also accepts MDB as a type.
To see if it finds your DB, click Test Connection.
Leave the database user as Admin and leave the password blank.
This is the default for Access databases, unless you changed it.
Then click OK to close this Window.
Now you can click Next.
The DB_SEC_E_AUTH_FAILED(0x80040E4D) error will no longer appear.
Now simply select the source and destination tables.
Interestingly and to my greatest joy, you can keep selecting many different source tables (one at a time) and by selecting the same destination table, it will append the data to the destination table at lightning speed.
I could not find this answer to this in any post. Just a lot of links to very complicated connection strings.
There would have been no problem if the import Wizard simply asked what the name of the source database was, instead of just this Properties button that has no way to browse to the database and select it. So Microsoft, please if you see this, please make it a little easier. Thank you.
Thanks to everyone who have posted other solutions.
I spent the entire past weekend to find fast ways to bulk import data into SQL. Linked ODBC tables, BULK import statements, BCP utilities, even upsizing Access to SQL to become an ADP project (then it only sees 10,000 records in the SQL tables).
The Microsoft SQL Migration Assistant for Access is also very very fast, but it does not have a function to append data to existing SQL tables (at least not an easy one that I could find). But it is very handy to get the database converted to start with, and it is totally free.
Using the SQL Data Import and Export Utility (64Bit) is by far the fastest and easiest way, since it uses SSIS. I have spent days testing every alternative, so spare yourself the hassle.
It supports flat file import and everything.
If importing from flat files, rather import them into Access first, and then use the Import Wizard to import from Access.
I had some issues where the SQL Import Wizard would not import flat file data when there were duplicate records in the import file.
Importing into a temp Access table first and then appending to a table with the same fields having a primary key defined, Access would promt you that x number of records could not be appended because they are duplicates. Just click OK to append anyway.
That gets rid of the duplicates in one go, after which you could use the SQL Import wizard to pump the data into SQL.
By the way, do not bother appending data using a linked ODBC table in Access to bulk import data to SQL. It works, but takes forever. But do use the ODBC linked tables afterwards to pull results into Access, since an Access ADP database only returns 10,000 results. There is probably a way to overcome this, but I will figure this out later. Looks like with SQL nothing is straight forward.
Hope this helps at least one other person!!
"I am trying to import an access table to sql server 2008 express database using the import wizard. I am getting the error: DB_SEC_E_AUTH_FAILED(0x80040E4D)"
I had the same problem using the SQL Server Import and Export Wizard
This is how I resolved it:
When using the data import Wizard to import data from an Access Table, choose the "Microsoft Office 12.0 Access Database Engine OLE DB Provider" as the Database Source.
Once you have made this selction, a Properties button will appear below the drop-down box.
Click the Properties button.
In the First field, called Data Source, type in the path and name of the database (why a Browse button is not available, beats me).
For example, type: D:\Databases\ImportDB.accdb
You should of course type in your own details, in the format
<Drive:>\<Path>\<DatabaseName>
It also accepts MDB as a type.
To see if it finds your DB, click Test Connection.
Leave the database user as Admin and leave the password blank.
This is the default for Access databases, unless you changed it.
Then click OK to close this Window.
Now you can click Next.
The DB_SEC_E_AUTH_FAILED(0x80040E4D) error will no longer appear.
Now simply select the source and destination tables.
Interestingly and to my greatest joy, you can keep selecting many different source tables (one at a time) and by selecting the same destination table, it will append the data to the destination table at lightning speed.
I could not find this answer to this in any post. Just a lot of links to very complicated connection strings.
There would have been no problem if the import Wizard simply asked what the name of the source database was, instead of just this Properties button that has no way to browse to the database and select it. So Microsoft, please if you see this, please make it a little easier. Thank you.
Thanks to everyone who have posted other solutions.
I spent the entire past weekend to find fast ways to bulk import data into SQL. Linked ODBC tables, BULK import statements, BCP utilities, even upsizing Access to SQL to become an ADP project (then it only sees 10,000 records in the SQL tables).
The Microsoft SQL Migration Assistant for Access is also very very fast, but it does not have a function to append data to existing SQL tables (at least not an easy one that I could find). But it is very handy to get the database converted to start with, and it is totally free.
Using the SQL Data Import and Export Utility (64Bit) is by far the fastest and easiest way, since it uses SSIS. I have spent days testing every alternative, so spare yourself the hassle.
It supports flat file import and everything.
If importing from flat files, rather import them into Access first, and then use the Import Wizard to import from Access.
I had some issues where the SQL Import Wizard would not import flat file data when there were duplicate records in the import file.
Importing into a temp Access table first and then appending to a table with the same fields having a primary key defined, Access would promt you that x number of records could not be appended because they are duplicates. Just click OK to append anyway.
That gets rid of the duplicates in one go, after which you could use the SQL Import wizard to pump the data into SQL.
By the way, do not bother appending data using a linked ODBC table in Access to bulk import data to SQL. It works, but takes forever. But do use the ODBC linked tables afterwards to pull results into Access, since an Access ADP database only returns 10,000 results. There is probably a way to overcome this, but I will figure this out later. Looks like with SQL nothing is straight forward.
Hope this helps at least one other person!!
