Better way than DLookup

[randolphoralph]

I have Access database in a multi-user environment over a 10/100Mb network. I am using a DLookup on the Employee ID field to return a employee's workload for a certain date and employee ID.

I have noticed the database takes 3-10 minutes on this field for users not located at the same location as the server where the database is.

The database has 6,000 records in it, and growing every day.

Here is the code I suspect is causing the issue. Any thoughts on a better way to do this would be appreciated.

Private Sub EmployeeID_BeforeUpdate(Cancel As Integer)
'Looks up the Workload if the EmployeeID and Dateofwork is the same as the current record.
Dim Workload4 As Long
If Len(Me.EmployeeID & "") > "0" Then
Workload4 = Nz(DLookup("[Workload]", "[Audits1]", "[EmployeeID] = " & Forms![Audits1]![EmployeeID] & " AND [DateofWork] =#" & Forms![Audits1]![DateofWork] & "#"), 0)
Me.Workload = Workload4
Else
Me.Workload = ""
End If
End Sub

 

[pbaldy]

If you're saying that those users are not on the LAN, but a WAN:

http://www.members.shaw.ca/albertkallal/Wan/Wans.html

 

[randolphoralph]

Yes they are on a WAN.

 

[randolphoralph]

After reading the article it looks like I have one option...to create a replica of the database.

Thanks

 

[Steve R.]

We had a similar problem at work. We resolved our slowness issue a few months back by moving the back-end to Microsoft SQLserver. Our database just crossed 5,100 records and it works much better. You may want to look into SQL Server Express. I am not familiar with SQL server or SQL Server Express, so I can't provide any further advise.

Alternatively, you may want to look into MySQL . Again, I am not familiar with this program, but the benefit is that it is both free and open source.

I also have found retrieving information through a DAO recordset is faster than Dlookup.

 

[randolphoralph]

I looked through the info on DAO recordset and would like to try this. I am not sure how to write the code for DAO recordset based on the code I posted.

I wish I could use SQL, but I am limited due to the IT department.

I read somewhere another way to improve speed is Extended DLookup, but have not used it before.

 

[randolphoralph]

I would need it to search the table Audits1 and return the Workload when EmployeeID of the current record is > 0 based on the EmployeeID and DateofWork being the same as the current record. Otherwise not return anything.

 

[MarkK]

1) It can take windows a long time to connect to shares on a network. To ensure this works optimally map a network drive on the local machine to the server share containing the BE. Link the local FE directly to the tables in the BE over this mapped drive. Windows then maintains this path rather than having to re-resolve it for each query.
2) Hold a table in the BE open in the FE. This saves the system from having to open and close the BE for every data operation, and create and destroy the ldb file. Create a dummy table, open a hidden form that's bound to it. Done.
3) Make sure any fields that you search or sort are indexed. In the case of your DLookUp(), is the Audits1.DateOfWork field indexed?
4) That a query takes minutes to complete is not caused by a table having +/-6000 records. I just ran a DLookup() on a table on my network, over 133,000 records, searching for the ID and a non-indexed string field and it finds it in 0.015625 seconds.
HTH

 

[randolphoralph]

Is this the process for using DAO recordset to search through the table?

 

[pbaldy]

I don't think lagbolt noticed we're talking about a WAN here. A DAO recordset is typically faster than DLookup(), but only incrementally so. I don't personally think it will make a significant difference here. Of the options discussed on that link, I'd lean towards Terminal Server or Remote Desktop type solutions.

 

[dfenton]

After reading the article it looks like I have one option...to create a replica of the database.

I don't think this is a good idea. You're just trading one set of problems for another.

As someone who has been creating and maintaining replicated apps since 1997, I would never do that, but would instead use Windows Terminal Server to host the app and users on the WAN would use Remote Desktop to run it.

Jet replication is complicated when used in anything but a LAN environment and has a lot of moving parts, so it's simply not the best solution to the WAN problem (and has not been since WTS started being included in all versions of Windows Server, i.e., starting with Windows 2000 Server).

 

[randolphoralph]

The problem I have is that the IT department where I work will not allow Terminal Server or Remote Desktop due to security.

So I am back to either doing a DAO recordset or using a saved query to return the value of a form field.

Can anyone direct me on doing one of the above.

 

[pbaldy]

Well, here's the Extended DLookup you probably read about:

http://allenbrowne.com/ser-42.html

You can use it as is or extract the DAO recordset method out of it, since that is what is uses. I'll be interested to hear if it performs measurably better.

I'm both developer and IT manager. I'm also concerned about security, but in my research using TS or RDP after creating a VPN connection to the network is very secure. I use that setup myself. I even have applications that work very well over the WAN without TS, but I use SQL Server as the BE.

 

[SOS]

Yeah, I'll bet the IT department (I'm part of one where I work) is just using that as an excuse to not do the work. Someone has their head up their...

 

[randolphoralph]

pbaldy,

That is the ELookup I was reading about and spending some time looking over the example I was not sure where to began editing the example to make it work for my situation. I guess I am having a off week or it is way over my head.

 

[SOS]

For elookup just copy the code from Allen's website into a new standard module and then you save the module with a name like basELook and then you can then just use the function ELookup everywhere you would use DLookup.

 

[randolphoralph]

I have replaced the DLookup with ELookup and will see what happens come Monday when users began using this. I will post back to let everyone know if I see an improvement in performance.

I just wanted to say "Thank You" to everyone for taking the time to help on this.

 

[dfenton]

If they won't allow WTS and RDP, then they aren't going to allow you to install the components necessary to use Jet replication.

You probably know this better than anyone else, but the "security" excuse for not using WTS and RDP is complete hogwash. It's actually more secure (and more easily manageable) to do it that way than any other method across a WAN. They just don't want to support it -- they want to make it your problem not theirs.

 

[DCrake]

I am not saying that DAO will improve it but as nobody has offered you the syntax here it is

Dim Rs As DAO.Recordset

Set Rs = CurrentDb.OpenRecordset("Select * From Audits1 Where EmployeeID = " & Me.EmployeeID & " And DateOfwork = #" & Me.DateOfWork & "#")

If Not Rs.EOF And Not Rs.BOF Then
    Me.WorkLoad = Rs("WorkLoad")
Else
   Me.WorkLoad = ""
End If

Set Rs = Nothing

Word of advice do not control names on your form the same name as the tble field names. Access can get unduly confused.

 

[randolphoralph]

dfenton...you are correct. I have been back and forth with them on this and I have dealt with plenty to know what they are doing.

DCrake...thank you for the syntax. I will try this out.