Storing unique data with a shared database

[tech.rawsteak]

Hello, I am developing an MS Access database application to be stored
on a server and accessed by many people. I would like to store some
unique, temporary information about each user accessing the database
however. For example, if user A is at his computer using the
database, I would like to store his userid, full name, employee role,
etc. temporarily so I don't have to keep calling a function to figure
out his user id, then looking up his full name, then parsing it from
"last, first" to "first last" and so forth. at the same time, if user
B accesses the database, I would like to store their information at
once as well, until they log out. All of the information that the
users interact with is in a separate back end database. Does anyone
have any ideas as to how I can do this?

 

[Guest]

You have an FE/BE configuration?

If so, you could store the information in a series of variables or you could
create your own custom type and store each piece of information within it (my
preference).

Do you need help with variables or creating a custom type?

Steve

 

[tech.rawsteak]

You have an FE/BE configuration?

If so, you could store the information in a series of variables or you could
create your own custom type and store each piece of information within it (my
preference).

Do you need help with variables or creating a custom type?

Steve

could you tell me a little more about this custom type? My only
concern with the variables is if the application has an error, all
global variables are cleared.

 

[Guest]

You will find more information by doing a search in the Access help for
'User-Defined Data Type'. Check the Type Statement in the Visual Basic
Documentation.

Basically, you create your own type (in a standard module) as follows:

Public Type MyType
MyString As String
MyInteger As Integer
End Type

Then you can set and get the values by using MyType.MyString or
MyType.MyInteger and so on...

With regard to application errors, you should endeavour to write code that
traps and handles errors. Errors do not wipe out your public
variables...anything critical will probably result in having to close the app
anyway...

Steve

 

[John W. Vinson]

Hello, I am developing an MS Access database application to be stored
on a server and accessed by many people. I would like to store some
unique, temporary information about each user accessing the database
however. For example, if user A is at his computer using the
database, I would like to store his userid, full name, employee role,
etc. temporarily so I don't have to keep calling a function to figure
out his user id, then looking up his full name, then parsing it from
"last, first" to "first last" and so forth. at the same time, if user
B accesses the database, I would like to store their information at
once as well, until they log out. All of the information that the
users interact with is in a separate back end database. Does anyone
have any ideas as to how I can do this?

You could use a VBA custom datatype as Steve suggests; but it might be simpler
to merely add a local (not linked) table within the frontend and store the
data there. If the data is truly temporary for the session, you could go a
step further and have a Form open as the startup form; put textboxes or other
suitable controls on it for this information, and simply refer to the form
from your code.

John W. Vinson [MVP]

 

[tech.rawsteak]

You could use a VBA custom datatype as Steve suggests; but it might be simpler
to merely add a local (not linked) table within the frontend and store the
data there. If the data is truly temporary for the session, you could go a
step further and have a Form open as the startup form; put textboxes or other
suitable controls on it for this information, and simply refer to the form
from your code.

John W. Vinson [MVP]

i tried adding a local table in the front end, but if someone else
uses the application, they become the current user and overwrite
whoever else is using the database. i'm just going to call the
functions needed and hold them in textboxes on the form, but i thought
that would slow the application down a bit, what with getting the
person's user name and then full name and such. further down the
road, i'm going to also look at loading an invisible form in the
background to hold that information, as well as handle exit procedures
if a user is in the database, but exits the ms access application
without exiting (and saving the current record properly) the
application.

 

[John W. Vinson]

i tried adding a local table in the front end, but if someone else
uses the application, they become the current user and overwrite
whoever else is using the database.

Ummmm... each user should have their own front end. Or do you mean two people
sharing the same frontend because they're sharing the same physical machine?
You *could* have two .mdb files on the computer, one on each person's logon,
if that's the issue.

i'm just going to call the
functions needed and hold them in textboxes on the form, but i thought
that would slow the application down a bit, what with getting the
person's user name and then full name and such.

If you have the person's personal information stored in a table, keyed by
their logon ID, it might bog things down for fifty milliseconds or so.

further down the
road, i'm going to also look at loading an invisible form in the
background to hold that information, as well as handle exit procedures
if a user is in the database, but exits the ms access application
without exiting (and saving the current record properly) the
application.

Now that's got me confused. Having a form bound to a backend table permanently
open is a good idea in any case for performance reasons (having an open
connection to the backend makes opening other tables faster), but I'm not sure
what you're saying about "saving the current record properly".

John W. Vinson [MVP]