Merging data from multiple Tables to one table

[Tempuser]

Hi!

I want to design a database for 14 users with individual access only to thier
data.The catch is i want the data entered by all 14 users to reflect in one
database.I tried to use update query but I succeeded in merging two tables
only..

any help is highly appreciated.

Thank you.

 

[Klatuu]

Why are you doing that?
Why not just use a multi user split database application?
Any other jury rigged way is just folly.

If you can explain you logic for the approach, I will be happy to help you
through it.

 

[Piet Linden]

Hi!

I want to design a database for 14 users with individual access only to thier
data.The catch is i want the data entered by all 14 users to reflect in one
database.I tried to use update query but I succeeded in merging two tables
only..

any help is highly appreciated.

Thank you.

If you really want that, the easy way is to include a field in the
table that includes the username that the record belongs to. You can
use the person's NT Logon if you want. Then you can filter all your
forms based on that. The 14 tables plan is a disaster waiting to
happen. You would have to write insane union queries to get your plan
to work. I've had to deal with that. Performance absolutely sucks.
(I don't think union queries can use indexes...) Try it if you want,
but it'll make you nuts. And what happens if you add another person
(another table). Then you have to rewrite your union queries. Very
bad plan.

 

[Tempuser via AccessMonster.com]

Thank You for the below reply.

I have 14 users with the same data as mentioned below:
(Sr No
Account Handled by
Customer Name
Customer requirements
OrderDate
Customer contract start date
customer region) what I want is the individual user must have edit rights
only to his records and readonly access to the remaining 13 users details.Can
u help with some other solution how should I proceed.Your help in this
regards is appreciable

Why are you doing that?
Why not just use a multi user split database application?
Any other jury rigged way is just folly.

If you can explain you logic for the approach, I will be happy to help you
through it.

Hi!

[quoted text clipped - 8 lines]

Thank you.

 

[PieterLinden via AccessMonster.com]

The easiest way to do this is to base your forms on queries, so the filter
cannot be removed.

SELECT ...
FROM

WHERE RecordOwner =fOSUserName();

the code for the fOSUserName() function is here:
http://www.mvps.org/access/api/api0008.htm

The catch is that you have to disallow access to the tables for this to work.

Thank You for the below reply.

I have 14 users with the same data as mentioned below:
(Sr No
Account Handled by
Customer Name
Customer requirements
OrderDate
Customer contract start date
customer region) what I want is the individual user must have edit rights
only to his records and readonly access to the remaining 13 users details.Can
u help with some other solution how should I proceed.Your help in this
regards is appreciable

Why are you doing that?
Why not just use a multi user split database application?

[quoted text clipped - 8 lines]

 

[Klatuu]

Yes. You will want to have a field in your table that identifies the user
who created the record. Then in the Form Current Event, you can set the
form's properties to either allow or disallow certain functions. I have
also included an API call that will return the user's Windows login name,
not the Access Security name.

This code goes in a standard module by itself. Mine is named modGetUserAPI.

Private Declare Function GetUserNameA Lib "Advapi32" (ByVal strN As String,
ByRef intN As Long) As Long
Public Function GetUserID()

Dim Buffer As String * 20
Dim Length As Long
Dim lngresult As Long, userid As String

Length = 20

lngresult = GetUserNameA(Buffer, Length)
If lngresult <> 0 Then
userid = Left(Buffer, Length - 1)
Else
userid = "xxxxxxx"
End If
GetUserID = UCase(userid)

End Function
------------

Now, the current event.

Dim blnMyRecord As Boolean

blnMyRecord = GetUserID = Me.txtUser
With Me
.AllowEdits = blnMyRecord
.AllowDeletions = blnMyRecord
End With

Thank You for the below reply.

I have 14 users with the same data as mentioned below:
(Sr No
Account Handled by
Customer Name
Customer requirements
OrderDate
Customer contract start date
customer region) what I want is the individual user must have edit rights
only to his records and readonly access to the remaining 13 users
details.Can
u help with some other solution how should I proceed.Your help in this
regards is appreciable

Why are you doing that?
Why not just use a multi user split database application?
Any other jury rigged way is just folly.

If you can explain you logic for the approach, I will be happy to help you
through it.

Hi!

[quoted text clipped - 8 lines]

Thank you.