Switching from an attached SQL DB to Access

[agroom]

I recently finished my first program in VS2005 and used an attached SQL database. Personally i'm much more familiar with running an access database and wanted to know how difficult it would be to switch over.

I've created an identical database in Access and changed the connection string to run from the Access file, but when i run the program it's still pulling data from the SQL database. Does this have to do with the fact that I attached the SQL database, and if so, how would I go about removing it?

I'm still pretty green to this, so if it's a complicated process, just leet me know and I'll live with it (or start over). Otherwise, if it's a relatively easy process, any help would be appreciated!

Thanks!

 

[jmcilhinney]

How did you create the connection in the first place? Did you have the IDE create a Data Source and a typed DataSet? If so then you'd have to do that again for the Access database and then go through and add instances of the new DataSet to your forms and change your code to use that DataSet and its associated TableAdapters. If it's a sizable app then it's a sizable job.

If you created all your data access objects in code then it's not such a big job, but if you used SqlClient to connect then you'll have to change everything to OleDb.

If there is no reason other than familiarity with Access to change then I'd suggest that you don't. Use this as an opportunity to get familiar with SQL Server. You'll be glad you did.

 

[agroom]

Okay, that's kind of what I thought, but didn't want to start tinkering and have everything come out FUBAR

I do agree that it's a good opportunity to get familiar with SQL, that's why I did it in the first place, but I'd really like to be a able to do a few things I know in Access (I do know access quite well) and then work with SQL on a less important project.

Really I can keep it with SQL, but then I'd have another question: how do I seperate the DB from my application? I want to have the .mdb on a shared network drive that several PC's can access.

 

[jmcilhinney]

SQL Server is a better option in that case too. Having a remote Access database accessible from multiple clients is certainly possible but inviting trouble. SQL Server 2005 is much more reliable in that type of situation. You can create a database on the server itself or you can attach an MDF file. You can even do both. I recently used an MDF file for development purposes but used a Setup project to create a database on the server when the app was installed. All that's required in code is an adjustment to the connection string.

Also, what are these things that you want to do that you know how to do in Access? Once you've created the database structure ADO.NET is exactly the same for SQL Server as it is for Access. Also, creating an SQL Server database through VS 2005 or Management Studio Express is just as easy as it is in Access.

 

[cjard]

Migrating SQLS to Access? That's like going from the.. er.. nice warm living room, into the fire.. We have a decidated SQL forum here where you can ask some experts how to do in SQLS the things you know how to do in access. Feel free to use it!

 

[agroom]

Okay okay, SQLS is by far the better route and It's really just my unfamiliarity with the IDE editing the database, setting up relations, creating views, etc. I've been playing around with it more now and I'm getting the hang of it, though it's still a little rough

Thanks cjard, If I have any questions specifically about SQLS I'll ask the guru's in the SQL forums.

As for changing it from an attached database, I'll play with the connection string and see what I come up with.

Question though:
I've never created a networked application before, only stand alone. If I install SQL server on the host PC and save the database there, I can modify the connection string to point across the network (granded the network drive is mapped correctly) and run the program as normal? Or is there a lot more to this and I'm just opening a can of worms for someone who's extremely green to this

This is primarily why I'd wanted to switch to access because I've done this in the past before with a test program.

 

[jmcilhinney]

The connection string handles all those details, although having an MDF file on the local machine attached to a remote SQL Server instance is not recommended. You can use the same application with exactly the same code connected to a database on a remote server or with an MDF file attached to a local instance of SQL Server simply by changing the connection string, which you can store in a config file. See www.connectionstrings.com for examples of different formats for different situations.

 

[cjard]

Okay okay, SQLS is by far the better route and It's really just my unfamiliarity with the IDE editing the database, setting up relations, creating views, etc. I've been playing around with it more now and I'm getting the hang of it, though it's still a little rough

AFAIWA the IDE coouldnt edit a database, only view it. For editing it, you need SQL Server Management Studio Express 2005 (who thinks up these names anyways) - a free download form microsoft that combines a db editor, t-sql editor and query runner in one tool.

Question though:
I've never created a networked application before, only stand alone. If I install SQL server on the host PC and save the database there, I can modify the connection string to point across the network (granded the network drive is mapped correctly) and run the program as normal? Or is there a lot more to this and I'm just opening a can of worms for someone who's extremely green to this

OK, here's the way it works:
SQL server is like a web server. It opens a listening port and accepts conenctions, interprets queries sent to the port, scans MDF datafiles for the results, and sends them back down the port.

Network mapped drives and file sharing is not used at all, unless you take the very unwise route of telling SQL server to use an MDF file that is on another machine. SQLS needs fast, random access to those files, and the mapped drive route, (unless it's a fibre optic backed SAN in a huge server configuration and hence *rapid*), is NOT recommended because it is slow and inefficient compared with local disk access.

This is primarily why I'd wanted to switch to access because I've done this in the past before with a test program.

The way access works is at a file level. The driver is local to the machine and knows how to seek and scan through an MDB and retrieve records. To do this remotely, a network share needs to be set up because it's a file only based system, with no network intelligence or server capability. Two users using the same file both have a network mapping to the file and it probably wont be that quick. For long operations involving huge amounts of data scanning (like grouping) the entire database needs to be transferred over the network link just to provide a few aggregated statistics. Microsoft actually kludged a new bit in in later versions where the jet engine had some way of activating the database drivers on the server where the MDB was stored, and getting them to do the grouping instead, reducing the network load but it still didnt make it a proper, multi-user, network intelligent database.

Hopefully this will help you see that access and SQLS are very different, and why SQLS is much better for networked use - it was designed for it!