Attach Access Database to SQLServer

Attach Access Database to SQLServer?

It seems it is necessary if I want to put it on the internet through IIS.

I tried add data source through tools and tried most combinations, but nothing led in that direction.

I also did a search.

If not, the alternative is importing the data into sqlserver 2005. What worries me about this is incrementally importing new tables, views, etc., and new rows. Is this later possible?

dennist685

> It seems it is necessary if I want to put it on the internet through IIS.

No. My guess is that you might security issues. The account of which runs your statement (IIS account or ASP account, I guess) to have the rights to read your mdb file. Should be mentioned in numerous threads.

> If not, the alternative is importing the data into sqlserver 2005. What worries me about this is incrementally importing new tables, views, etc., and new rows. Is this later possible?

This would be my soulution. I would use SQL Server (maybe Express) to serve data.

You can access Access tables from within SQL Server queries. I would keep away from doing this for normal processing. For import of data, it is OK, I guess.

-- Sample to SELECT against object in access db:

EXEC sp_configure 'show advanced options', 1

GO

RECONFIGURE

GO

EXEC sp_configure 'Ad Hoc Distributed Queries', 1

GO

RECONFIGURE

GO

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'x:\dir\mymdb.mdb';'admin';'',objectname)

GO

The sp_configure statments are neccessary because distributed queries are turned off by default for security reasons

Hope this helps

sql