Sharing data via mdb in a network

[Diezz]

Hello,

I'm still thinking on the project and i would need some opinions to know how it would be the most suited.

I would like to build a database in acces and it would be used by more users in the same time.

There would be max 6 users, me, 4 of my collegues and my boss.

When my collegues would open the database, i would like a certain form to appear and they will store data (using the form) in the same table (maybe in the same time).

My boss would only see the reports based on the data that my colegues entered, and i, well i'll have admin rights.

We will use a shared folder on the server for this.

How it would be most suited to share the data?
If my colegues will enter data in the sametime, PK won't duplicate and create errors?

How can i create users (i ve saw the user statement and group statement in acces help, but i'm not sure where to put it) and would this really work, or should i stay with my shared workbood ideea?

If anyone could give me some answers or a link that could gave them to be, i'ld really appreciate it.

Thanks

 

[zoroaster]

Diezz

A brief couple of quick thoughts - don't know much about Access core security, never used it and am lead to understand it's quite hard to get right, but I'm sure there's a FAQ on this site regarding it somewhere.

One possibility is to give your boss one front-end and your colleagues another. Another thing you can do is use the environ function to get the windows userid to determine who has opened the DB, store them in a table and instruct the DB to open the required form based on who's opening it.

I'm pretty sure record-locking in Access will prevent duplicate PK's even in a network environment.

I know it isn't much, but I hope this helps


Laters, Z

"42??? We're going to get lynched!

 

[JerryKlmns]

Well the workgroup file could be of a nightmare but only for the first time. The next time you just modify a copy of it and apply the new settings of the mdb.

PK is also a nightmare if you use the DCount way to generate an incrementing one by yourself. Use something different take a look here:

http://www.tek-tips.com/faqs.cfm?fid=5106

http://www.tek-tips.com/faqs.cfm?fid=184

Split the mdb to FrontEnd(anything but tables) and BackEnd(tables only and one form to show nothing, just hidding the tables. Use the AllowBypassKey property thread181-773467 to view the tables)

Check the CurrentUser (I hope you are in Access2000 or above) to load a start up form for your users

 

[fneily]

If it's just six users as you say, why not make it simple and create a table with login's and passwords and use a Dlookup from a login form to check against it? Since you're new to Access, keep it simple. I created one for my state's Department of Transportation just to get them up and running and haven't needed to change it yet. I was going to implement Access security, but why complicate what's working.

 

[barny2006]

i have a similar application. the db is split into front end and backend. the front end was modified to give a different look for admin type people. so we have front_end1, front_end2, and the back end, which has tables only. the admin sees a different thing than the data entry people do.

 

[Diezz]

Hey,

Thanks for all your answers, they helped me alot.

To be honest i'm having some problems splitting the database and i have much more to learn before i can do it correctly.

I'll also try fneily's ideea, it seems interesting. I can use the dlookup function on a VB module so when i ll open the database it would show me a log in screen?

 

[fneily]

Have a little table with UserID and Password fields. Have a little form with two unbound textboxes to enter ID and password. The password textbox has an input mask of password. Have a command button when click will run this code:

Private Sub Command5_Click()
On Error GoTo Err_Command5_Click
Dim stLinkCriteria As String
Static UserID

If IsNull(Text11 = DLookup("[Textbox1]", "Usyslogoninfo", "[userid] = [Textbox1]")) Then
MsgBox "You MUST select a valid User ID. Please try again!"
[Textbox1] = Null
[Textbox2] = Null
[Textbox1].SetFocus
Exit Sub
ElseIf IsNull(Text13 = DLookup("[Textbox2]", "Usyslogoninfo", "[password] = [Textbox2] And [userid] = [Textbox1]")) Then
MsgBox "You have entered an invalid password. Please try again!"
[Textbox2] = Null
[Textbox2].SetFocus
Exit Sub
End If

Dim stDocName As String
stDocName = "YourNextForm"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, "LogonForm"

Exit_Command5_Click:
Exit Sub

Err_Command5_Click:
MsgBox Err.Description
Resume Exit_Command5_Click

End Sub

Change YourNextForm to your form name. Change the textbox names to yours.
You will notice that the table name is usyslogoninfo. Whenever you put usys in front of a table name it hides the table. You must click on Tools - Options and check show System files to see the table. Most people don't know this trick.

 

[Diezz]

This is great and it works very well.

Thank you.

 

[Diezz]

question what is text 11 in If IsNull(Text11 = DLookup("[Textbox1]", "Usyslogoninfo", "[userid] = [Textbox1]")) Then
MsgBox "You MUST select a valid User ID. Please try again!"

the same what is text13?

Do i have to build them?

 

[fneily]

There just variables to hold the dlookup result so you can test the IsNull. No you don't have to build them. You said it worked, right?

 

[Diezz]

yes, thank you very much, i was just curios, i like to understand the code and i wasn't sure about those.