Search results

I have tested FE/BE over a VPN, both ends on the same provider fiber FE home, BE Business. It was much slower and prone to corruption. VPN was added to the disallowed connection type the FE checks for at startup and monitors while open.

I would do the following. Start with a new accdb BE database Configure it Build new tables fixing the normalization problems. Add a Function to import the old data into the new tables. Add a Function to empty the new tables to allow for another import during testing. Start with a new accdb FE...

That is a meaningless question without knowing the procedure. My 1st example of a CPU centric process that could benefit from a CPU upgrade. Most Access procedures are data centric that may benefit from an IO upgrade, but what IO? Is the BE on a server or is it local? Does you process require...

A poor design may benefit, but likely not enough to make a difference. A form that is taking 30 seconds to open that now opens 20 seconds after adding $2000 in hardware on one computer is a failure. Spending $2000 in refactoring letting the form open in 3 seconds is a win for all your users.

Put a label behind each sub-form with the message you want when the sub-form is empty. If the sub-form is empty, set it's Visible property to False.

I think you are looking for some hardware magic bullet that will run software fast no matter how it's written. There is no such thing! For example, an earlier post with sample code to calculate perfect numbers for 2 to 10000 ran in a 5 seconds on a 4 year old i7 with 32 GB memory and P2 drive...

The second opening of query did not wipe out the parameters, it is completely independent of the first. The only way to set parameters of a query opened with docmd is form variables or temp variables.

Your function could be as simple as this. Dim strQueryName As String TempVars![parExamFreq] = "Weekly" TempVars![parExamPeriod] = Date strQueryName = "qry_Par_Start_Date_In_Range" e with CurrentDb with .QueryDefs(strQueryName) with .OpenRecordset(dbOpenDynaset)...

The easiest is with temp vars per my example in post #15. You will always need to pre-fill the temp vars before opening a query referencing a temp var.

This should work using temp vars. Dim db As DAO.Database Dim qdf As DAO.QueryDef Dim strQueryName As String Dim rs As DAO.RecordSet Dim prm As DAO.Parameter Dim sFreq As String Dim dDate As Date sFreq = "Weekly" dDate = Date TempVars![parExamFreq] = sFreq...

Those are 2 independent instances of the same query; setting parameters in 1 instance has no affect on another.

A 2nd look at your function, you opened the query 2 times, the 2nd time without providing values for the parameters. 1st time as a record set with the parameters pre-filled. Set db = CurrentDb Set qdf = db.QueryDefs(strQueryName) qdf.Parameters(sPar1).Value = sFreq...

Ken said "Another problem is that the IN operator does not accept a parameter as its argument." Instead of a function, wouldn't it be great if Microsoft allow us to pass IN as a parameter.

So did adding the defined parameters help? Adding line breaks makes it much easer to see what you are doing. PARAMETERS [parExamFreq] Text ( 255 ), [parExamPeriod] Text ( 255 ); SELECT tblEquipment.EquipmentID FROM tblEquipment WHERE ((([parExamFreq])="Weekly") AND...

x64 will give you a large memory space. P2, one of the single board style drive that connects directly to the motherboard as opposed to an SSD that plugs in like a HHD. Core speed would be more important than count with Access.

so you are saying ([parExamFreq]) = "Weekly") will not exist in your final query? I would think something like ([tblEquipment].[ExamFreq] =[parExamFreq]) would be more likely.

Try defining the parameters at the top of your query. PARAMETERS [parExamFreq] Text ( 255 ), [parExamPeriod] Text ( 255 ) SELECT tblEquipment.EquipmentID, ( DatePart("yyyy", [tblEquipment].[StartOfServiceDate]) * 1000 ) + DatePart("ww", [tblEquipment].[StartOfServiceDate])...

Leave out the brackets in the parameter name string. sPar1 = "parExamFreq" sPar2 = "parExamPeriod"

You can use a sub-select to git rid of the district SELECT tblCompanies.CompanyName FROM tblCompanies WHERE tblCompanies.CompanyID In (SELECT JobTypeID.CompanyID FROM JobTypeID WHERE JobTypeID.JobTypeName IN ("Plumbing","HVAC"));

Stable hardwired connection 1gb or better to the server hosting your FE. Optimize your application performance for core users. Probably do more for performance on modern computers then high end hardware Access x64 Fast P2 hard drive 16gb or more memory Fast single core speed on your processor.