Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Sql server 2005 - Problem with Access 2010

Posted on 2013-12-05
16
Medium Priority
?
497 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 500 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 52

Assisted Solution

by:Gustav Brock
Gustav Brock earned 500 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 1000 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 39

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 1000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

604 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