Solved

Sql server 2005 - Problem with Access 2010

Posted on 2013-12-05
16
479 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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 36

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

756 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