?
Solved

Sql server 2005 - Problem with Access 2010

Posted on 2013-12-05
16
Medium Priority
?
490 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
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

 
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 51

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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
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 …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

770 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