Reference another database

[AccessHawaii]

I'm trying to do a dlookup function for a table that's located in another database. Does anyone know if this is possible and if so, how to do it?

 

[dhookom]

DLookup can only reference tables and queries in the current mdb.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[sfm6s524]

You could write your own function to return a value from another mdb:

Function CustName(CustomerID As Long) As Variant
Dim RS As Recordset
Dim SQL As String
Const CustDB = "C:\Cust\CustData.mdb"

SQL = "SELECT Name " & _
"AS CustName " & _
"FROM tblCustomer IN '' [" & CustDB & "] " & _
"WHERE ((([CustomerID])=" & CustomerID & &quot);"
Set RS = CurrentDb.OpenRecordset(SQL)
If RS.EOF Then
CustName = Null
Else
CustName = RS![CustName]
End If
End Function

 

[AccessHawaii]

Really? You can't do that? What I'm trying to do is find out which users are logged onto a database. I know theres programs out there that will show you the computer name of the users logged on but that doesen't tell me much. I have a form that I keep open and hidden so the users can't see it that is atttached to a table with the user's logins and it does a DLookup function to match the current user with the record and will check a yes/no field if there is a match and unchecks it when the user logs out. I thought about just linking to the table and creating a form and checking for the user's login on the form's current event by setting the timer interval so the form will continue to refresh but I'm afraid if I do that, that it will affect the performance of the database because it's not split so all the tables are in the same file as the forms and everything else. What do you think? Would it be safe to do that and it wouldn't affect the performance?

 

[AccessHawaii]

sfm6s524,

Thanks for the tip. I tried it but I couldn't get it to do what I want. I think I'm going to just end up linking the table to my Admin database so I can see the users online but I'm not crazy about doing it that way because I'm afraid that it might decrease the performance and also I'll be doing this for several different databases with different workgroups so I won't be able to do all this in one database, I'll have to use different databases for each each workgroup.

 

[dhookom]

You should just state what your requirement is. The ldb file contains information regarding who is currently logged into an mdb. There is sample code at

http://www.mvps.org/access/modules/mdl0055.htm.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[sfm6s524]

I use a somewhat simple process to see who has what open, stored in a separate table, which also acts as an audit trail of adds, edits, deletes, and whatever else you want to track. tblFormActivity has fields:
ID - Autonumber field
FormName
UserWrkStn
OpenDateTime
Activity
CloseDateTime

When app starts, I store UserID and workstation name into global variable. On each form I declare a long FormRecID variable, which is set on the form open event by calling a function named AddFormRec, which adds user, form name, open time, and type of activity they are doing. On form close event, function EditFormRec edits tblFormActivity record added on open event, changes activity if needed, and set close time (use Seek method in this one). So, by building a form on tblFormActivity, I can see who has what open, when, and what they are doing to what record, etc. Sometimes our accounting folks want to find who made a stupid entry, or deleted something, etc. Also helps me determine who has backend DB open, and from what workstation, if I need to ask them to exit a few minutes to make table changes. It's not foolproof, and occasionally a tblFormActivity record is left without a close time. It's a pretty active table, about 400-500 records a day, so I have a job setup to delete records older than 2 weeks. I did some performance measurements with feature both on and off, and overhead was negligible, at least on our LAN. Sorry for the long-winded note...