VB Programming Best Practices (SQL Server Backend)

If your doing this for yourself and have no company standards, then I'd say whichever would scale the best. I personally never use the built in functions, as you never know when MS will change them.

You are now bass ackwards for a full DBMS.
Relational databases have always had relationships, there's a clue somewhere in the name. :p

Look up Foreign Key in T-SQL, the concept is the same, so is the necessity (or lack).

Best way to do CRUD? There are several schools of thought of that, personally I'm a big fan of stored procedures due to the level of abstraction they give you. Some consider them too proprietry, others feel they reduce flexibility (if you don't allow application users direct access to change data in the table).

At all costs try to avoid the select a record, twiddle with the fields and then update stuff.
Never do select all the records and then loop thru and twiddle with them in code based on some to no conditions. It's hideously expensive on a full dbms, in a multi user, or high transaction volume system, it ill give you all sorts of transactionality and locking problems.

With a full DBMS the trick is to get the absolute minimum amount of data you need for the operation (columns and rows), and need a lock on it for the shortest length of time.
For good code using a full DBMS, always look at needing your application having to know as little as possible about the database schema. There are even near none options if you go down the ObjectStore route, be careful with that though, no optimsation is free.

Make a point of looking up disconnected datasets in VB.Net, particularly using stored procedure to do the Insert, Update and Delete.

As a rule of thumb, if you did it that way in access, you do the complete opposite in Sql Server.

Happy learning, and when you get some specific how you should/could do X, feel free to ask, plenty of help about.

PS Other big differences to look up Messages, Queuing, Triggers, Permissions and Roles.
It's not Rocket science but it isn't jumping off a chair either if you see what I mean.

For learning I'd recomend you convert one of your access apps to .Net / SQL Server and leave the client side logic as is.
Then keep it to one side take a copy and move as much database processing as you can into SQL and use as ,any as seem useful of the extra features (without going barking mad) as you can. The differences in approach will teach you lot.

The things you have here are useful, and aim in a very narrow way at your question. Which is good.

I'd propose that there's a larger question, however, about the general structure of your project, that might make such issues a lot easier over time.

If you properly design your project, you'll wind up with all your data touches encapsulated... your button strategy is a good illustration of that sort of thing, in a minor way. The logic of things is one part. The actual details of touching the database should be carefully segregated and kept that way. If you insist on working directly to the data objects, it can easily get very confused about what part of the code is responsible for which thing.

'Functional integrity' is the key here. When you use ONE thing to get, then pass along, another thing to work, the pass along, another thing to put, etc etc, classic 'snippets' of code... then the stuff that has to happen to data becomes relatively mechanical, isolated and easily changed. It often seems like a waste. It's not.

Thinking of it in this way might help in getting and keeping a clear view of your whole project, and the DB questions then resolve much more easily at logical limits. Whether you use SPs or code in some specific instance is a lot less critical, except where performance might matter.

Is relationships between tables a necessity? No. For the most part they are not used in real-life. SMBs don't have the wher-with-all to have DBAs PLUS developers PLUS Network Admins. As the number of duties rises and the breadth of disciplines the one or two IT guys are expected to cover increases, the finer points of each discipline get lost and that which can be ignored usually is. Granted, foreign keys and updates that flow up/down are a great idea. They can make data integrity issues practically disappear. But you still have to write explicit JOINs and INDEXs and WHERE clauses to access that data regardless of the relationship. Since most DBAs are also the Network Admin AND the in-house Developer, the full relational capabilities of RDBs like Oracle or MS SQL Server are rarely put to use.

As for methodology of data communications between the application classes and the database, I personally like to use command objects to call stored procedures when reading and writing data because:
1. It makes code maintenance easier. I work with third party databases on a regular basis and when the data structure changes, it is very easy to update a SP and send a renewed script to my clients rather than creating a new application release.

2. Transactions are easier to manage. The core data is communicated to the database in a single call. If processing a block of data takes several steps and the application's connection dies in the middle of a process, it can leave you with half finished transactions and incomplete data. While there are coding steps to prevent this, it's much easier to not have to worry about it from the front end. Since all the data for a process is pushed to the back end in a single call, the process runs as a single step as far as the front end is concerned. You still need to maintain transactional blocks and check for data issues, but it is simpler to do it in one place than in two (or more).

When reading data, I usually use a datareader and then parse through it, storing individual items in local variables within an application/object class. I design my classes and processes to be minimalist and agile, striving to achieve as small a data footprint as possible.

All that being said, adapt your methodology to your environment, not the other way 'round!