0

I would want to migrate the data from SQL Server 2008 to MS access 2007. How can this be done? Also, how to connect MS access to ASP.NET web application? The code behind the pages is in C#. The IDE is VS 2008. Any pointers appreciated.

Thanks in advance.

User52396
  • 1
  • 8
    Look out! You're going the wrong way! – Abe Miessler Apr 22 '11 at 20:59
  • 3
    You ought to provide some guidance on WHY you're doing this. Taking an app in this direction is almost unheard of. Have you considered Sql Express? – Chris B. Behrens Apr 22 '11 at 21:01
  • 2
    I've done this and for a very good purpose: a user needs a data file that she can take to a different office and analyze with her stats package. She doesn't have access to the SQL Server in the location where she needs to work, so I export the data from SQL Server to an MDB, copy it to her USB thumb drive, and she's golden. Folks, get over your anti-Access bigotry. – David-W-Fenton Apr 26 '11 at 00:24

3 Answers3

3

My advice. Don't migrate... use SQL Server Express 2008 (free) if you're worried about licensing. MS Access only supports 1 concurrent user at a time. You won't be able to modify your data while the application is using it.

EDIT: Let me clarify the "only supports 1 concurrent user at a time": When using access within an ASP.NET application, IIS will lock it and your users will not be able to make edits to the data it is using.

capdragon
  • 14,565
  • 24
  • 107
  • 153
  • 1
    Hmmm. I wonder how the 6 people in my MD's office who use it for scheduling get around these restrictions? – dkretz Apr 22 '11 at 21:22
  • that's wrong. MS Access can support 255 concurrent users, technically. In reality, it can support around 10 users concurrently if the database is set up correctly and maintained well. Though to be fair, it is not supported by asp.net since it's multi-threaded and either way, i still wouldn't choose access as this type of back-end. – Chris Conway Apr 23 '11 at 01:35
  • I agree with Chris, if you're an awesome Database admin, you'll know how to make it support 255 users READ-ONLY. – capdragon Apr 25 '11 at 14:17
  • @le dorfier: if all users are given Windows "Full control" security permissions on the network shared directory and users are only READING the records (instead of inserting, updating and deleting records) then the limit of 255 concurrent users could be reached. But in practice, it doesn't always pan out like that especially when IIS is involved. IIS will lock the database and your users will not be able edit it. The limit on users really depends upon the needs of the application, how the network handles traffic and amount of record lock contention. – capdragon Apr 25 '11 at 14:26
  • 1
    The limit of 10 connections is not a limit of the database, but an XP concurrent connections limitation. – capdragon Apr 25 '11 at 14:27
  • 1
    I would say that Jet/ACE is not an appropriate data store for a web-based application at all. – David-W-Fenton Apr 26 '11 at 00:22
1

Accessing accdb from ASP.NET is not officially supported. You shouldn't do it.

http://www.microsoft.com/DOWNLOADS/en/details.aspx?FamilyID=c06b8369-60dd-4b64-a44b-84b371ede16d&displaylang=en

The Office System Drivers are only supported under certain scenarios, including:

  • Desktop applications which read from and write to various files formats including Microsoft Office Access, Microsoft Office Excel and text files.
  • To transfer data between supported file formats and a database repository such as SQL Server; in the context of a desktop application.

For alternatives to SQL Server with ASP.NET look at SQL Server Express or the myriad of other available options like SQLite, MySQL, Postgres.

Community
  • 1
  • 1
Samuel Neff
  • 73,278
  • 17
  • 138
  • 182
1

Just use the "Import" feature in access, connect to your SQL Server, and import all the data. There are potential incompatibilities but chances are good that you won't run into them.

You'll notice that most database jocks have little regard for access but it's not as dysfunctional as some of them seem to think, when you use it for appropriate purposes.

Is MS Access (JET) suitable for multiuser access?

Community
  • 1
  • 1
dkretz
  • 37,399
  • 13
  • 80
  • 138