Question

Hamed_1983 on Fri, 04 Oct 2013 11:23:48


Hi
i want to import data from a ms access 2007 to my sql server db, but in wizard only queries display in the "Select Source Tables or Views" dialog box in the wizard and tables does not exists!!

can anybody help me how to work-around this problem ?
thanks in advance

http://www.codeproject.com/KB/codegen/DatabaseHelper.aspx

Replies

Stefan Hoffmann on Fri, 04 Oct 2013 11:34:04


What wizard? Access Upsizing or SSMA for Access?

Hamed_1983 on Fri, 04 Oct 2013 12:09:49


Hi
Sql Server Management Studio

Stefan Hoffmann on Fri, 04 Oct 2013 12:14:24


D'oh? Where can I find this wizard exactly? What SSMS version? Can you post a screenshout?

Hamed_1983 on Fri, 04 Oct 2013 12:18:00


Hi

Sql Server (2008) Management Studio :

Right Click on your db > Choose Tasks > Import Data

Select Microsoft Office Access 2012 from Data Source.

Is this enough ?

Stefan Hoffmann on Fri, 04 Oct 2013 13:09:46


Ahh, I see. Never used this one. I'd prefer the Access Upsizing wizard.

But from a quick comparision: Only local tables and queries which use only local tables are display in this wizard.

Hamed_1983 on Fri, 04 Oct 2013 13:32:07


in access 2007, i'm facing this error during move data :

The Upsizing only works with microsoft sql server (Versions 6.50 SP5 or higher). Please log in to a Sql Server data source.

KH MR on Fri, 04 Oct 2013 13:46:10


Hi,Please look at this link it may help you :

http://support.microsoft.com/kb/2012792/fr

Regards.

Stefan Hoffmann on Fri, 04 Oct 2013 13:52:32


I never had any problems with upsizing wizard.

But when I take your error message seriously, what SQL Server version do you use?

Tom Phillips on Fri, 04 Oct 2013 15:40:14


This is almost always a security issue. You are not authorized to see the tables.

However, try selecting the "Microsoft Access (Microsoft Jet Database Engine)" option as the source.  See if you get different results.

Candy_Zhou on Mon, 07 Oct 2013 05:10:33


in access 2007, i'm facing this error during move data :

The Upsizing only works with microsoft sql server (Versions 6.50 SP5 or higher). Please log in to a Sql Server data source.


http://www.codeproject.com/KB/codegen/DatabaseHelper.aspx

Hi,

This problem occurs when your Access database and the instance of SQL Server do not exist on the same computer.

To work around this problem, use a File DSN to connect to your instance of SQL Server when you upsize your Access database. To do this, follow these steps:
  1. Start Access.
  2. Open the problem database.
  3. On the Tools menu,  point to Database Utilities, and then click     Upsizing Wizard.

    Note In Access 2007, click SQL Server in the Move Data group on the Database Tools tab.
  4. In the Upsizing Wizard dialog box, click theUse existing database option, and then click Next.
  5. In the Select Data Source dialog box, select the appropriate DSN to connect to your instance of SQL Server on the File Data Source tab.
  6. Click OK.
  7. Enter the appropriate authentication information to connect to your instance of SQL Server if the SQL Server Login dialog box appears.
  8. Follow the instructions in the Upsizing Wizard.

    Notice that the Access database is successfully upsized without any errors.

Please refer to the article below for the detailed troubleshooting steps.

Error message when you try to upsize your Access database by using the Upsizing Wizard: "The Upsizing Wizard only works with Microsoft SQL Server (Versions 6.50 SP5 or higher). Please log in to a SQL Server data source."
http://support.microsoft.com/kb/838594

Thanks

Candy Zhou

Hamed_1983 on Mon, 07 Oct 2013 15:35:14


Thanks
I've follow your steps but i'm facing the same problem

Candy_Zhou on Wed, 09 Oct 2013 09:49:46


Hi,

Try to use linked server in SQL Server to connect to MS access, please refer to the articles below for the details.

SQL Server Linked Server to Microsoft Access
http://stackoverflow.com/questions/2200503/sql-server-linked-server-to-microsoft-access
From SQL Server how do I read from an MS Access database to UPDATE data in one or more table columns?
http://stackoverflow.com/questions/9055444/from-sql-server-how-do-i-read-from-an-ms-access-database-to-update-data-in-one-o
Create Linked Servers (SQL Server Database Engine)
http://technet.microsoft.com/en-us/library/ff772782.aspx

Thanks

Candy Zhou

Hamed_1983 on Wed, 09 Oct 2013 21:04:30


Hi again

I have not problem with connecting to ms access from sql server. my problem is in the importing wizard, tables does not exists!

Candy_Zhou on Thu, 10 Oct 2013 08:52:18


Hi,

Have you checked whether you have permission to check the objects in Access?

Did the User name exist in Access and has enough permission to see the related tables in Access?Please check it first.

Thanks

Candy Zhou

Hamed_1983 on Thu, 10 Oct 2013 17:19:30


Hi
If i have not permisson to access db, i could not to go to the next step in sql server importing wizard!

Candy_Zhou on Fri, 11 Oct 2013 09:09:35


Hi,

So you can logon to Access db directly (not  by SQL Server) and see the related objects on MS Access, right?
Thanks

Candy Zhou