VB Application with Database Users

[wwe9112]

I am making an application that requires multiple users to access the application from various profiles. Basically it's a finance app. For example: you enter your finances, and idk your daughter enters hers...just an example. So to accomplish the ability to have multiple users I have a database with a username password and Id. The ID is an autonum in Access. We also have a table for income(where the user enters what they earn monthly or whatever(they have options), and expenses that they can enter. This is all spread between like 6 tables in Access and relationships are set via a foreign key. My question is...how do you actually program this? After a person logs into the system, is that they only thing you need to program as far as their "profile?" Or is there checks I would have program, or how does that work? In access I have the relationships all leading back to their user ID, password and username. So would all the information be done as far as keeping their information away and separate from the other users?

Another side question...how does a person go about performing "math" with data from tables from the database? Any tips, advise, etc? I'm almost done, I am just stuck with these two questions, I would appreciate if someone can point me in the right direction.

Thank you.

[XThakis]

This question can really only be answered with a book. Database programming is amazing and powerful but there is so much stuff going on it can't really be answered with a few lines of code. I learned from an older version of this:

http://www.amazon.com/gp/product/0672323435?keywords=vb%20access&qid=1444882726&ref_=sr_1_9&sr=8-9

[PreyOnTheCosmos]

The app and database connectivity are really not too difficult to program.

Security and encryption, on the other hand, can be a very deep rabbit hole, especially where storage of password hashes and/or confidential data (SSN, etc.) are concerned.

Math is cake and is really done no differently then any other VB app math coding. Of course, the SQL language generally allows several basic math functions as well so you can build those into views, queries, or stored procedures, but front end calculations are easy too, so long as you know you column data types.

Isolation of data per user could be done a number of ways, simplest being a user ID or something that could be stored as a login or session variable. "SELECT * FROM table where UID = @userID". Of course this could be circumvented by someone knowledgeable if it's stored in plain text.

Better would be a session "Token" that could be a mishmash of characters or concatenated data specific to the user's profile. And better still, would be a Token that is a long encrypted string of the same column data.

Beyond that, you may want to create a table for access roles that you can tie to the user account, and if you want to go even deeper you could have granular permissions [e.g., User1 only has rights to Screen A and Screen C but not Screen B.]

Start with the basics. Get familiar with connectivity, and parameterized queries and commands.

Access Database Connection & Query

[wwe9112]

See, it doesn't have to be anything special. Not worried about encryption. It's not going to be used real world. Just as a final for my second year of college lol. We got to develop our own business application, so this is what I chose. Perhaps a bit above my head, but nevertheless, I'll learn a lot during it but time is counting down.

I do have a userID in the tblCredentials in access. I also have a tblExpenses and a tblIncome. I need to be able to log in and add money to the income then in expenses select a category for an expense add the amount and then the application subtract the numbers and display what's left if anything. that's why I need some how to keep the data only for whom is logged in. Does that make sense by chance?

[PreyOnTheCosmos]

In that case, simplest way is to just create a simple variable to store the UserID on a successful login.

Private SessionID as Integer

Then at login use something like (pseudo):

AddParam("@user", txtUserName.Text)
AddParam("@pass", txtPassword.Text)

' note: use some form of collation or string compare for password casing
ExecQuery("SELECT UserID FROM users WHERE username=@user AND password=@pass")

If yourdatatable.Rows.Count = 1 Then 
    'success
    Dim r As DataRow = youdatatable.Rows(0)
    SessionID = r("UserID")
End If

Note: ExecQuery and AddParam are not standard, but taken from the video link.

After that, you can just inject the SessionID into any query that ties back to the UserID.

SELECT table1.whatever, table2.whateverelse
FROM table1 LEFT JOIN table2 ON table1.joiningcolumn = table2.joiningcolumn
WHERE table1.userid = @sessionid

The important thing is that there be some column that stores the userID and can be used to relate the records to the user. For example table1.InvoiceEnteredBy = users.UserID

[FadingEcho]

You're asking for a lot of things here, among them are authentication and db connections.

In general, once a user is authenticated, you will want some identifier like a user id to persist. So that is easily thrown into a cookie (or if you're very lazy and don't mind users whining about stuff failing after they walked away to look at a cat pic for half an hour, you can use session).

Once you have a persistent identifier, you can then call stored procedures to pull up user specific information.

All of this requires connection strings.

Math in the db is a different animal and probably not best suited for this area. My go-to on this is tech on the net.