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
Solved

Sql server 2005 - Problem with Access 2010

Posted on 2013-12-05
16
476 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 49

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 35

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
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…

839 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