Importing Access database into Sql Server Express

Some facts:
1) I have an Access database (.mdb file) sitting on my harddrive.
2) I have Visual Studio 2005, Sql Server Express, and Sql Server
Management Studio Express.
3) I do *not* have Microsoft Access.
What I'm trying to do:
I simply want to import the Access database into Sql Server Express. In
other words, I want to end up with a Sql Server Express database that
has all the same tables, keys, and relationships as the Access database
as well as all the data from it. I can live without the queries stored
in the Access database, but those would be nice too.
What I've tried so far:
I'm able to connect to the Access database using the "Linked Servers"
features in Management Studio Express. From there, I was able to write
some simple Transact-SQL queries to find out what tables are in the
Access database and copy them, one at a time, into a Sql Server Express
database.
This is definitely a good start, but it doesn't take care of the
primary keys or foreign keys. There appear to be procedures for those
as well (sp_primarykeys, sp_foreignkeys), but I keep thinking there
must be an easier way.
Which brings me to...
Questions:
Without having to buy additional software/tools, can I import this
Access database without a lot of programming? If so, how?
Thanks in advance,
-DanDaniel
Actually I have not tried it by myself on SQL Server 2005, so try if this
works for you
SELECT *
FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="d:\northwind.mdb";
User ID=Admin;Password='
)...Customers
"Daniel Manes" <danthman@.cox.net> wrote in message
news:1139881255.986395.191440@.f14g2000cwb.googlegroups.com...
> Some facts:
> 1) I have an Access database (.mdb file) sitting on my harddrive.
> 2) I have Visual Studio 2005, Sql Server Express, and Sql Server
> Management Studio Express.
> 3) I do *not* have Microsoft Access.
> What I'm trying to do:
> I simply want to import the Access database into Sql Server Express. In
> other words, I want to end up with a Sql Server Express database that
> has all the same tables, keys, and relationships as the Access database
> as well as all the data from it. I can live without the queries stored
> in the Access database, but those would be nice too.
> What I've tried so far:
> I'm able to connect to the Access database using the "Linked Servers"
> features in Management Studio Express. From there, I was able to write
> some simple Transact-SQL queries to find out what tables are in the
> Access database and copy them, one at a time, into a Sql Server Express
> database.
> This is definitely a good start, but it doesn't take care of the
> primary keys or foreign keys. There appear to be procedures for those
> as well (sp_primarykeys, sp_foreignkeys), but I keep thinking there
> must be an easier way.
> Which brings me to...
> Questions:
> Without having to buy additional software/tools, can I import this
> Access database without a lot of programming? If so, how?
> Thanks in advance,
> -Dan
>|||Thanks for the answer, Uri, but what I'm really looking for is a way to
take all the tables, primary keys, foreign keys, constraints and the
data itself from the Access (mdb) file and place them in a Sql Server
Express database.
But I did try your SELECT statement in SQL Server Express 2005. Doesn't
work.Seems like you need to set up the Access file as a Linked Server
then just do "SELECT * FROM Northwind...Customers."
But that only gives me the data, not the keys, constraints,
relationships, etc.
Any other ideas for importing the *whole* database?
Thanks,
-Dan