Solved

Sql server 2005 - Problem with Access 2010

Posted on 2013-12-05
16
488 Views
Last Modified: 2014-03-12
I'm using SQL Server 2005 Import Wizard to import a table from Access 2010 and I'm getting a message that said "Unrecognized database format..... .accdb." I didn't have any problem when importing tables from Access 2003, but starting to get this problem with Access 2010. Would you please let me know how do I resolve this issue?
0
Comment
Question by:HNA071252
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
  • 2
  • +2
16 Comments
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 125 total points
ID: 39699303
SQL Server 2005 was released prior to Access 2007, which was the first version having the accdb format - so it does not support the accdb format.

Try making a backup copy of your database, and saving it in the .mdb (Access 2003) format as follows:

- In access 2010, click the File Tab
- Click Save and Publish
- Choose the Access 2002-2003 format option for .mdb files

Any Access 2010 specific features in your database will be lost (but they wouldn't be supported in SQL 2005)
0
 

Author Comment

by:HNA071252
ID: 39699340
Thanks for a quick response. Is there another option if I want to keep it as Access 2010 because Access 2003 version will be uninstalled completely. Another word, there's no option in keeping Access 2003 in my machine.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39699397
My thought was to keep your production version in Access 2010, but try the import with a backup copy saved in 2003 format -- assuming that this is a one-time thing with just that table.

You can also try the Upsizing Wizard from Access, by clicking the SQL Server Icon in the Move Data group located on the ribbon in the Database Tools tab.

Details here:
http://databases.about.com/od/tutorials/ht/Converting-An-Access-Database-To-SQL-Server.htm
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 15

Expert Comment

by:JimFive
ID: 39699578
You could Export from Access to the SQL Server instead.

From the External Data menu select ODBC Database and go from there.
0
 
LVL 15

Expert Comment

by:JimFive
ID: 39699581
Just to clarify, select ODBC Database from the Export section of the Ribbon (On my computer I have to click More to see it)  not the Import & Link section.
0
 

Author Comment

by:HNA071252
ID: 39699616
I have to do this with many large tables every month and it takes an awful long time to export from Access to the Server. Is there another option to import it from the SQL server instead of exporting from Access? Or I would need to upgrade the SQL Server to a newer version? If so, which version of the SQL Server would be compatible with Access 2010?
0
 
LVL 15

Expert Comment

by:JimFive
ID: 39699663
You could try creating an ODBC Data Source on the Server that points at the Access Database and importing using ODBC.

You could do a similar thing and try to make the Access Database a "Linked Server" on the SQL Server.

If the time issue is that you don't want to sit at an access window you can use the TransferDatabase Macro to run the exports.
0
 

Author Comment

by:HNA071252
ID: 39699803
I would not want to export from Access whether sitting or not sitting at an Access window I just can't wait hours to get the data exported to the server.

How do I "creating an ODBC Data Source on the Server that points at the Access Database and importing using ODBC."? Can you please help me with this in details?
0
 

Author Comment

by:HNA071252
ID: 39699958
Most likely we will upgrade the SQL Server to a newer version. Would you know which version of the SQL Server is compatible with Access 2010?
0
 
LVL 50

Assisted Solution

by:Gustav Brock
Gustav Brock earned 125 total points
ID: 39700462
> How do I "creating an ODBC Data Source on the Server that points at the
> Access Database and importing using ODBC."?

You will in SSMS go to Server Object, Linked Servers and create a New link to your Access file.
However, not even SQL Server 2008 supports accdb files, so you may have to create an ODBC link to the mdb file.

/gustav
0
 
LVL 15

Assisted Solution

by:JimFive
JimFive earned 250 total points
ID: 39700829
My install of SQL Server 2008 R2 can import from an Access accdb using the Microsoft Access 12.0 OLE DB Connection.

According to http://technet.microsoft.com/en-us/library/ms175866(v=sql.105).aspx
You can install the driver on SQL Server 2005.
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 39702730
Why don't you just keep the tables in SQL Server and link to them from Access?
0
 

Author Comment

by:HNA071252
ID: 39707440
I have to keep the table in Access because I have to update this table every month. And the process that I have to update this table is in Access. I don't have a process to update this table in the Server. And I keep this table a linked table from the Server then the update takes a much longer time in Access to update a linked table.

Can anyone tell me if I have to upgrade to a newer SQL Server 2008 or is there a work around without updating but still be able to use it with Access 2010?
0
 
LVL 15

Expert Comment

by:JimFive
ID: 39708923
Did you try the OLEDB Driver that I referenced above?
0
 

Author Comment

by:HNA071252
ID: 39709378
I couldn't figure out how to install the OLEDB driver on the SQL Server 2005. I need more help.
0
 
LVL 15

Accepted Solution

by:
JimFive earned 250 total points
ID: 39709650
From your SQL Server go to this link:
http://technet.microsoft.com/en-us/library/ms175866(v=sql.105).aspx

Click on 2007 Office System Driver: Data Connectivity Components.

Download the drivers and execute the downloaded file.
Then, in your SQL Server Management Studio
Start an Import Task and check to see if:
Microsoft Office 12.0 Access Database Engine OLE DB Provider is now listed.

You may also need to install the Connectivity components on your workstation.
0

Featured Post

Turn Insights Into Action

You’ve already invested in ITSM tools, chat applications, automation utilities, and more. Fortify these solutions with intelligent communications so you can drive business processes forward.

With xMatters, you'll never miss a beat.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question