Access to SQL

Does anyone know how to import an Access db to SQL.

I need to do this programmatically.

The user selects the path to the Access.mdb clicks a button and the Access data and schema are imported to a SQL db.

I can do SQL to SQL with DTS.TransferObjectsTask2 and it works perfectly but I need to be able to do Access to SQL.

Thanks

You can do it in DTS package or SSIS package just select Access database in dts and ODBC data source in SSIS package. You can also use .Net and connect to Access using ODBC set of database objects and next save results into SQL using SQL objects you can use Dataset to store your tables or just read from ODBC and save into SQL

Thanks

Access to another database via a stored procedure

I have a stored procedure on a database that selects information from
another database, and the users running the stored procedure are not users
on the 2nd database. I don't want to have to enter all users individually
into the 2nd database, I just want anyone who has execute rights to that
stored procedure to be able to select information from the 2nd database.
Do I have to somehow in the stored procedure log into the 2nd database as a
public user?
Right now I am getting errors that says the user is not a user of the 2nd
database. The 2nd database is a public database, so I shouldn't need to be
a user on it.Users need a security context in all databases accessed. If you don't want
to add users to the 2nd database, one method:
1) enable the 'guest' user in the second database (sp_adduser 'guest')
2) enable 'db chaining on both databases (sp_dboption 'DB2', 'db chaining',
true)
If you objects are objects are owned by 'dbo', both databases need to have
the same owner so that the dbo user ownership chain is unbroken. You can
execute sp_changedbowner, if necessary. No permissions need be granted to
guest. Guest permissions are limited to those granted to public.
Note that you should fully trust those users that can create dbo-owned
objects before you enable cross-database chaining ('db chaining'). If the
databases are owned by 'sa', ensure only symin role members should have
permissions to create db-owned objects.
Hope this helps.
Dan Guzman
SQL Server MVP
"et" <eagletender2001@.yahoo.com> wrote in message
news:eO3CPC1AGHA.4080@.TK2MSFTNGP14.phx.gbl...
>I have a stored procedure on a database that selects information from
>another database, and the users running the stored procedure are not users
>on the 2nd database. I don't want to have to enter all users individually
>into the 2nd database, I just want anyone who has execute rights to that
>stored procedure to be able to select information from the 2nd database. Do
>I have to somehow in the stored procedure log into the 2nd database as a
>public user?
> Right now I am getting errors that says the user is not a user of the 2nd
> database. The 2nd database is a public database, so I shouldn't need to
> be a user on it.
>|||Thanks so much, this is very helpful information. I think the guest account
will work just fine, as I agree that I don't think I want to do the cross
ownership until I see the need. Thanks.
Thanks!
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OsaivR1AGHA.2512@.TK2MSFTNGP09.phx.gbl...
> Users need a security context in all databases accessed. If you don't
> want to add users to the 2nd database, one method:
> 1) enable the 'guest' user in the second database (sp_adduser 'guest')
> 2) enable 'db chaining on both databases (sp_dboption 'DB2', 'db
> chaining', true)
> If you objects are objects are owned by 'dbo', both databases need to have
> the same owner so that the dbo user ownership chain is unbroken. You can
> execute sp_changedbowner, if necessary. No permissions need be granted to
> guest. Guest permissions are limited to those granted to public.
> Note that you should fully trust those users that can create dbo-owned
> objects before you enable cross-database chaining ('db chaining'). If the
> databases are owned by 'sa', ensure only symin role members should have
> permissions to create db-owned objects.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "et" <eagletender2001@.yahoo.com> wrote in message
> news:eO3CPC1AGHA.4080@.TK2MSFTNGP14.phx.gbl...
>|||I'm glad you found the information useful.
Just to be clear, if you don't enable cross database chaining, you will then
need to grant SELECT permissions to guest (or public) in the second database
because the ownership chain is broken. This will effectively allow all
server users not already in the second database to select from the table
directly. This might be ok in your situation but I want to make sure you
are aware of the ramifications.
Hope this helps.
Dan Guzman
SQL Server MVP
"et" <eagletender2001@.yahoo.com> wrote in message
news:uh%23icI2AGHA.2788@.TK2MSFTNGP14.phx.gbl...
> Thanks so much, this is very helpful information. I think the guest
> account will work just fine, as I agree that I don't think I want to do
> the cross ownership until I see the need. Thanks.
> Thanks!
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OsaivR1AGHA.2512@.TK2MSFTNGP09.phx.gbl...
>