Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 965
  • Last Modified:

SQL Server Management Studio 2005 - How to import database such as Access

This seems easy but is becoming very difficult to find.  In SQL Server Management Studio 2005 I can not find where to import a database such as Access.  It is not under "Tasks" when you right click on the database folder as documentation has stated.  I am frustrated enough to come here and see if I am missing something obvious.  I had no problem with SQL 2000

Thanks,
John
0
JohnMac328
Asked:
JohnMac328
  • 3
  • 3
  • 2
  • +1
2 Solutions
 
SQL_SERVER_DBACommented:
export the database from access into .xls from there import it into Management Studio.
0
 
JohnMac328Author Commented:
Where is the import command in SQL Server Management Studio 2005, that is the whole problem.
0
 
MrRobotCommented:
you can import from an access database also.

just create a new database, right click on it, on the tasks, you'll see import and export data.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
MrRobotCommented:
from there you'll find access and excel options.

good luck john =)
0
 
JohnMac328Author Commented:
Your not getting it, when I right click on it, the tasks does not show a import option.  It has Detach, Shrink, Backup, Restore and Generate Scripts. That is why I am here asking the question.  If it was that easy I wouldn't be here.
0
 
ZberteocCommented:
Try to repair Management Studio from the Set Up Disk. This is a problem and you might have others if it wasn't installed properly.
0
 
JohnMac328Author Commented:
This is the Express version, is it possible that I have to have the full blown option for that?
0
 
MrRobotCommented:
ah, import / export wizard depends on the integration services hence doesn't exist in the express edition. you need a standard edition in order to use it. you can however, use access to export the data to a sql database. just right click on a table and choose export.



0
 
ZberteocCommented:
You can use an ad-hoc query to access database from SQL:

SELECT
      *
FROM
      OPENROWSET('Microsoft.Jet.OLEDB.4.0',
      'C:\ACCESS_FILES\acesss_file.mdb';'admin';'', yourtable) AS yourtable

the 'C:\ACCESS_FILES\acesss_file.mdb access file has to be on the same machine with the SQL server. Of course replace with your file and table names. You can use such a query to insert into a table in SQL. You can also use this syntax in joins.

If SQL server is not set for adhoc query run this first:
sp_configure 'show advanced options',1
RECONFIGURE WITH override  
go
sp_configure 'Ad Hoc Distributed Queries',1
RECONFIGURE WITH override
go

0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now