Question Database application for multi user environment

[Sayed12]

Hi all,

I have a few questions to ask regarding a database application that i am working on to use on a LAN network. I am using access as the database and vb2008 express as the UI.The database has only one table . the I have connected the db with the program using database connection wizard.

There are all together 5 different forms.Form1: this is a log in form(not db bound , just a set value based. it gives different access level to the main program.i.e enabling /disabling certain features depending on the password). there will be different users with two levels of permissions(supervisor/normal user) who will be using the software at the same time.if it is a normal user they will have the access to only the new record form(Form3)(all they will do is input the data into the form and then save the record ).Now the supervisor has access to the search form(Form4) to find out the saved records and open them then print out reports.

1.What i am looking for is, if i could alert the supervisor with an alert message when a record saved by a normal user.So a message box will pop up saying this user(user Id) saved this record. I am not quite sure how to go about this.

2.Secondly to deploy it in a LAN Network (where i need to centralize the database. so the database will be on a server and the front end will be installed to the workstations).What needs to be done at my end as a programmer. What i did is i build the program and then used inno setup to make a setup file. so it has the database in it. when i added the db using wizard i chose it to be inside my program(now it resides in the bin folder).Do i just give the setup file to the client or what should i do. The connection string for the db is the local directory of my computer. Do i need to change that before publish the software once again. I am not sure how to make the access db a back end and only install the program into different workstations.

3. How can I add a new column to an existing access db table without using codes, is it possible? when i added the data source i done it through the wizard. i am using binding source to query the db. I am not sure how to add a new column to the access table. I know the sql cmd to add new column. since i have not used any codes for the connection to the db, I am not sure where to put the ALTER TABLE cmd and also how it gonna work in my case..I need some examples pls.


any help is greatly appreciated.Looking forward to hearing from the experts. Thanks again.

 

[jmcilhinney]

You really should be using something like SQL Server for this. Access is a poor choice for multi-user applications and a server-based database will make what you're doing significantly easier and more robust.

 

[Sayed12]

Thanks for the reply jmcilhinney. I have read many of your posts before and i am glad that you replied. I have already made the project in access. but it is certainly possible to switch to SQL server. Being a newbie access looked easier to me. but I do understand SQL server is the better choice and that is the one which was designed for this purpose and jet engine is good for single user.

if i use SQL server compact 3.5(built in vb8) will it do the same trick? Do i need to do the whole project from scratch or just switch the database to SQL server compact(if switching how?any tips).

if i am using SQL server compact than i know how to alter a table.(question 3 is solved in that case). But how about question 1 and 2!!!!

My main concerns are the deployment issue (more precisely what are the files i need to give to client(is that only set up file, which will eventually include the db file in it). as they are looking for a centralized split database, what should be my steps. and how do i send the alarm message to another user?

I appreciate your suggestion. thank you in advance.

 

[jmcilhinney]

I'm not suggesting using SQL Server CE. It is also better for single-user apps. I'm talking about using a server-based database, i.e. one that you install. You install SQL Server (or SQL Server Express, which is free) on one machine on the network, which would generally be a server machine but doesn't have to be, and then each client connects to the database on that machine.

You actually don't really have to change much. Just delete the Data Source and DataSet from your project but leave behind all the code that uses it. Once you've built the new database in SQL Server, you can simply add a new Data Source. The generated DataSet will use all the same names, as long as you've used the same names in the database. All the code form before will then just work. You may have to make some changes in the designer but that's not too difficult.

 

[cjard]

1.What i am looking for is, if i could alert the supervisor with an alert message when a record saved by a normal user.So a message box will pop up saying this user(user Id) saved this record. I am not quite sure how to go about this.

You'd have to roll your own, by saving the modified date and username along with the data.. Periodically the supervisor machine can query the date and see if it's changed

2.Secondly to deploy it in a LAN Network (where i need to centralize the database. so the database will be on a server and the front end will be installed to the workstations).What needs to be done at my end as a programmer. What i did is i build the program and then used inno setup to make a setup file. so it has the database in it. when i added the db using wizard i chose it to be inside my program(now it resides in the bin folder).Do i just give the setup file to the client or what should i do. The connection string for the db is the local directory of my computer. Do i need to change that before publish the software once again. I am not sure how to make the access db a back end and only install the program into different workstations.

File sharing the access db will work. I echo jmc's senitment sabout using access over a network; it can be a nightmare of locked tables and hung applications

3. How can I add a new column to an existing access db table without using codes, is it possible? when i added the data source i done it through the wizard. i am using binding source to query the db. I am not sure how to add a new column to the access table. I know the sql cmd to add new column. since i have not used any codes for the connection to the db, I am not sure where to put the ALTER TABLE cmd and also how it gonna work in my case..I need some examples pls.

Use microsoft access.. Or possibly the Server Explorer of visual studio after you add a connection to the database

 

[Sayed12]

Hi mate thanks for the reply

i have made the same project in sql server 2008 back end (as you advised). Need to focus on only one thing now "the alert message"

how the software works

when the program starts it will ask for a user id and password(which is fixed; for normal user(user:agent, pass: pass) for supervisor(user:supervisor pass: pass)). depending on the user id and pass the program will give different access levels to users(normal agent will be able to only see one form(record input form). supervisor has access to all the forms). what i want is when a normal agent click on save button to save the record, it will save the record as well as send an alert message about the update to the supervisors machine(the normal agent and the supervisor running the same program in different computers in a LAN). this is my code

form1= new record form
button1= save button

code:


Imports System.Net.Sockets
Imports System.IO


Public Class Form1


Dim listener As New TcpListener(44443)
Dim client As TcpClient
Dim message As String


Private Sub Listening()
listener.Start()
End Sub


Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick
Try
If listener.Pending = True Then
message = ""
client = listener.AcceptTcpClient()
Dim reader As New StreamReader(client.GetStream())
While reader.Peek > -1
message = message + Convert.ToChar(reader.Read()).ToString
End While
Me.Focus()
MsgBox("")
End If
Catch ex As Exception
MsgBox(ex.Message)
End Try


End Sub


Private Sub Form1_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
Listener.Stop()
End Sub








Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load


listener.Start()
Timer1.Enabled = True
Timer1.Start()


TextBox1.Text = "127.0.0.1"
Me.testtableTableAdapter.Fill(Me.testdbDataSet.testtable)
ComputernameTextBox.Text = My.Computer.Name
AgentIDTextBox.Text = Environment.UserName
End Sub






Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Me.Validate()
Me.testtableBindingSource.EndEdit()
Me.TableAdapterManager.UpdateAll(Me.testdbDataSet)
Try
If AgentIDTextBox.Text = "" Or TextBox1.Text = "" Then
MsgBox("")
Else
Client = New TcpClient(TextBox1.Text, 44443)
Dim writer As New StreamWriter(Client.GetStream())
writer.Write(MsgBox(AgentIDTextBox.Text + "saved a report for" + productTextBox.Text))
writer.Flush()


End If
Catch ex As Exception


MsgBox(ex.Message, MsgBoxStyle.Critical)
End Try


End Sub





End Class

question:

I am using IP address 127.0.0.1 for testing the software.In realty the supervisors will be running the program in 5 different computers(I have the IP for all those 5 computers).should i just put those IP addresses in above code just like the one now.I am clue less......

please help with your right advise.

 

[Sayed12]

I didn't mention the main thing The output of the above code. When starting all it does is make a beep sound and thats it!!!!! instead it should have sent a message on a pop up message box with relevant details to the supervisors machines....

what is going wrong ? could you give me some idea or an example would be a great help....

thx again