I've been trying to make the transition from MS Access to VB.NET 2008 coupled with MS SQL Server 2008. So far it's been slow going. It seems like there are a lot of major differences in how you have to think when using VB.NET.

I'm wondering if there are any good resources that compare these two development environments. Here are some of the topics I would love to see covered:
Why you might choose VB.NET (or C#) over MS Access and Vice Versa
Helpful hints on how to get the same or similar functionality from VB.NET as MS Access (example: updateable datasheet form in Access compares to what in VB.NET?)
Information on how VB.NET data adapters, datasets, data tables, etc, compare to MS Access Recordsets
Whether or not to use Visual Studio's data tools to create adapters, datasets, data tables, etc.
When and why you might write your own UpdateCommand, DeleteCommand, or AddCommmand in VB.NET
Bound vs. Unbound forms in VB.NET
When to choose unbound forms in VB.NET
Using DAO or ADO in VB.NET

Hi,

Not too long back I was playing around with VC2010, looking to do more or less the same thing you are: get Access-like functionality from a .NET environment. I don't have a lot to say on it (I don't have the time I would like to spend with it, so haven't gotten very far), but maybe some comments will help:



Indeed. So many differences, in fact, that you need to learn .net with a clean slate. Most of what you may know from using Access will not apply.



The power and level of detail that you can get using .NET far, far suprasses that of the Access interface. But it certainly comes at a price: what might take 10 minutes in the Access environment may take the novice .NET programming 10 hours. And then you have the 10 hour Access projects... it takes a lot longer to get things done in .NET. So, for me, when I absolutely need to have something done quickly and efficiently and not screw around with hours upon hours of testing/debugging, Access is the way to go. When I want to have a complete control over everything going on, whether it be UI or Data handling, .NET may be the way to go, so long as I don't need it done in a hurry.



I bought Microsoft ADO.NET 4.0 Step by Step and found it extremely useful. In fact, I had purchased two or three other books looking for this information before I found this one, and wish I had found this first. It gives everything you need to know about how to handle data in ADO.NET. With this information, you can construct your own classes that act/feel like a traditional DAO or ADO recordset. Note that there is no "comparison" per say. With Access, you use the DAO and ADO models. With .NET, you use ADO.NET with the option to build your own model to replicate that behavior, if you wish. DAO and ADO are "dumbed down", so to speak, where ADO.NET gives you control over every piece of data, in more ways than you might ever want or need.

Also, LINQ and EF (ADO.NET Entity Framework) are highly recommended tools for working with data, though I would advise that a good understanding of ADO.NET before moving into these may be helpful.

Note that traditional ADO and ADO.NET are nothing alike. ADO.NET is not a new version of ADO. It just happens to share the first three letters of the name.




I don't recommend this. I think that we are far better off using code to do what we need to do... the VS interface creates many... issues... along with the data. This is a thread I started when I was working with similar issues, where Brent and Anthony chime in with some very helpful advise regarding working with ADO.NET. It certainly made me feel more comfortable having people who know the environment advise on how to handle the situations. If I can quote Anthony from the aforementioned thread:



I agree with this wholeheartedly.





The SQL Commands are there for your use, and may be required to perform certain tasks when handling data. Similar to using SQL in Access with an UPDATE, INSERT or DELETE: use them when you need to.

Bound vs. Unbound is also a hard one to "compare" persay... it depends on your need. You can handle data binding with a form in any number of ways, some controls you can bind, some you wont, some will be bound only at times, etc etc etc. You can set binding levels in grouped controls, or for the form as a whole, or really in any way you see fit. .NET gives the felxibility to handle data in just about any way you can think of, so you have those kinds of options.



Well, there's my take on it. I spend quite some time looking for a "how to make a .NET program work like Access", and what it brought me around to was learning how to use ADO.NET to handle data, and how to do the data binding to the form. It really is two different worlds, and direct comparison from one to the other is difficult. If you picture yourself trying to re-build all of Access's internal workings, it becomes a little easier. At that point you can say ok, I want DAO-like functionality, let's start designing the classes required to make a data handler that acts like DAO does.

Hope this helps some... sorry I wasn't able to provide more feedback on specific books for the subject. And, please take all this with a grain of salt... by no means to I claim to be proficient even in the slightest bit with .NET, but I thought this might help as I went through the same excersize some months ago.

Good luck,

I agree that it's probably easier in the long run to use code.

The VB equivalent of a datasheet is the datagridview, not to be confused with a datagrid or a gridview, which are earlier controls. It's still possible to use code with a datagridview.

Did either of you ever drag a field from your datasource onto a form and create an instant form that way? It uses table adapters which as has been said end up limiting you but it's interesting to see how that works.

Robert

I did try that, and for a little while it seemed to work well enough, but the problem I was having was revolving around the fact that I was using the dataset designer to drag from, and the way the dataset designer was implementing the ADO really had me confused. I ended up using code in the window class during it's initialization to define the data structures for the form rather than the dataset designer, which put a damper on drag/dropping the field.

Of course, I could have missed something entirely... (IMG:style_emoticons/default/dazed.gif)

Thanks Robert

If you do it correctly then you have an almost-Access-like form. I just think it's really cool the way it does that. But for most forms, it's oledbdataadapter or sqldataadpter.

You wrote about the additional time it takes to do things in .Net as compared to Access. It seems to me that you can actually create more user-friendly, responsive forms in MS Access using lots of "native" options combined with some VBA code. Is this just my perception or is this what you're getting at in the additional time needed in .NET? Do you feel that you can get a very "similar" user experience in .NET? (By "similar" I do not mean that it looks similar but rather that it is as user-friendly and as responsive.)

I've ran into a couple things recently where it looks like .NET has some additional code features/options that would be nice to have in MS Access. For example, to my knowledge there is no way to encrypt a user's password in MS Access with SHA512 using VBA code with a native class/function.

I guess I'm still trying to compile a mental list of reasons why it's important to learn .NET and migrate to it for my projects instead of using MS Access. But the truth of it is that the best reasons I can't present really don't call for me to move away from MS Access right now. I would mostly be pleasing that nagging voice in my head that says Access is inferior to [you name it here]. I was recently very dismayed by a brief exchange I had on another forum where numerous users indicated that C# is far, far superior to VB.NET. However, my research shows that there is really no difference between the two other than the syntax. Now I'm wondering if I should bother to listen to the "Access haters" at all. I think Access gets a lot of bad rap that it doesn't deserve, somewhat because of the way that people misuse it and somewhat because of the misunderstanding within the programming community. I think many programmers that poo on Access have actually never used it except maybe when they took a basic course in college. It's cool to hate Access and act as though it's the statue, euphemistically speaking. And it's also cool to hate anything VB and swear by anything that uses curly braces in it's syntax.

This post has been edited by hk1: Feb 19 2011, 07:46 PM

Just to chime in my experience:

I did some .NET programming and do see that it has lot of power to offer but it also made me come away with a newfound respect for Access. Where I stand, I would have no qualm saying to a client that had a project where Access couldn't quite meet the requirement, "Sure, we can do this in .NET but I'd bet you I can build the whole shebang for much less in Access and outsource the undoable part to .NET"

I think using .NET as a library or a way to extend applications does makes good sense but I just can't see it as the first to reach for an average business application. The requirement are just going to change too often and sometime the client doesn't know what they want. Best that they put their money to good use in a RAD tool than months of uncertainty then crushing disappointment when the sparkling new application fails their expectation miserably.

As for hating... my response would be something along this line:
(IMG:http://www.maniacworld.com/four-popped-collars-cool.jpg)

(IMG:style_emoticons/default/wink.gif)

I did end up using C# but not because VB.NET was horribly flawed. It was only that I found it easier to learn C# coming from VBA because I had less unlearning to do with C# than I would have with VB.NET and it also seems that when you google for something, the samples are going to be in C#. Both are of course totally non-technical reasons and reflect my personal prejudices much more than it does reflect VB.NET. But hey, if hating it is cool....

(IMG:style_emoticons/default/giveup.gif)

>> You wrote about the additional time it takes to do things in .Net as compared to Access. It seems to me that you can actually create more user-friendly, responsive forms in MS Access using lots of "native" options combined with some VBA code. Is this just my perception or is this what you're getting at in the additional time needed in .NET? Do you feel that you can get a very "similar" user experience in .NET? (By "similar" I do not mean that it looks similar but rather that it is as user-friendly and as responsive.) <<

I once spend my work days coding in Access and felt it to be the best tool around for internal apps where data ('auto' generated and user generated), forms, and reports were the main components of the app.

I now spend my work days coding in C# (and VB.Net) ... My brain now thinks in C# and VBA (VB.Net just seems clunky -- for lack of a better term :-s)

.... my opinion has not changed that much (IMG:style_emoticons/default/smirk.gif) , however, the speed with which I can do either is not too terribly different. Granted you can create forms and reports in a snap with Access and thus be editing, analyzing and reporting very quickly. But once you commit to distributing that Access app, you pour more thought --- and code --- into it and likely will not accept a "wizard created Form object with a recordsource set to a table" as your deliverable product.

In short a well thought out app requires development time, regardless of the tool used to implement the specs needed by the client. Access is indeed a rapid application development environment, but C# (VS2010) is an awesome development environment that a proficient developer can develop applications rapidly.

Also, if you are ever required to use C# for your data and forms applications, you should check out Visual Studio LightSwitch. I personally find it amazing how "revolutionary" the VS2010 advertising team makes it sound, especially when Access does most (or all) of that stuff anyway.

While I love Access and what it can do, I just don't feel as though the product as a development enviroment will ever garner as much respect as something like VS2010. Unfortunately, I think most of reason for that fact is a result of well meaning folks thinking that the creation of form that can edit data qualifies them to be called an "Access Developer", which is far from the truth. As has been said by Bannana and others the perception is this (paraphrased): If a C# app is bad, the developer is the problem; if an Access app is bad, Access is the problem. I soooo wish that Access would compile to an EXE --- I think that along would propel Access's reputation amongst the "Developer" community.

In the end, to me Access, VS2010, etc. are tools developers can use to implement an application's goals for a client. The developer should choose the best tool for the job, with out fear or ego!. This concept is reflected inside of Microsoft itself in the sense that Excel is used, get this, as part of the user interface to a corporate application solution --- apparently because Excel was the best tool for the job and user base of the product.

FWIW, while I do agree that deployment is one of Access' bugbear, I also tend to think that Access is not really all that appropriate as a shrinkwrapped product. It is definitely the perfect thing for a bespoken application for which deployment is much less of a factor in relative. I am aware of people trying to build and sell shrinkwrapped Access database and if they can, more power to them but I would personally reach for C# in that scenario myself.

>> I also tend to think that Access is not really all that appropriate as a shrinkwrapped product. <<

As with many things ... from my perspective it depends. In general though, I agree (IMG:style_emoticons/default/thumbup.gif) . But some target markets do just fine with a shrink wrapped Access app. The organization I work for actually uses two such products. One of the products uses the default Jet/ACE database format, while the other (which has tighter security) utilizes SQL Server.

One thing I like about the shrink wrapped Access apps that we use is the fact that their license allows us to implement custom code through a referenced db. Also, all their code is pretty much "Open Source". But, that same functionality can be a detriment "in the wild".

That sounds a lot like "Yes, I can run my business!" which is also a shrinkwrapped Access but also open source. I think the fact that it's open source does make more sense, especially if you plan around on providing support mainly as opposed to selling & trying to block people from customizing. Of course, not all developers are willing to do this openly and insist on protecting their intellectual property, which is a whole new debate in itself.

Don't touch my intellect. I don't have much, and your hands might be dirty.