Search results

Hi The Doc Man, I've compared the options/properties of original version and new version (where I first had the issue) of the database and both are identical, including using double click to open an object. We've done some more tests and the issue occurs on other databases when choosing to run...

Hi The Doc Man, Thanks for your advice. To clarify, the processes are always running without errors through Task Scheduler. As far as I've determined, the team has never had an issue before and so it is only because I'm using a different method to execute the macro (right clicking/run)...

Hi Ranman, Thank you for taking a look. The quit is happening after other actions. In each database a macro runs a VBA function which exports data from pass through queries into an Excel template, then saves as a new excel file, closes the spreadsheet and quits the application, followed by...

Hecronis, JHB and arnelgp have made good suggestions. To these I would add: In order for you to understand what is going wrong, it would be helpful if you can see the SQL that is actually being run. '// add a string variable to hold the SQL dim strSQL as string '// assign the SQL into this...

Hi, I've come across a strange problem in a new contract, wondering if anyone else has discovered the cause or solution. My client has a series of Access databases that are managed through Windows Task Scheduler on some dedicated desktop PCs. Task scheduler opens each database on a frequent...

Hi, Just thought I'd share this. Am not certain I know why I fixed this, but would be grateful for any pointers if I've got it wrong. Am developing an Excel forms front end Access backend application. Got an error this morning while loading a form that has loaded fine for months. Error was...

Re: Run-time error '3183': Fixed Thanks again to The Doc Man, Dave and SpikePL who posted advice on this thread. Several things have made a huge difference to the process including refactoring queries to reduce use of sub queries and reducing use of multiple tables to store temporary data. The...

Hi Dave, Thanks for pointing out my error re Pat. I guess I know more Patricks than Patricias... I've been told my own choice of name also belongs to a Mexican dog... So much for my attempt to be unique... The dates are stored in the table as dates. I have taken to passing in date parameters...

Hi I was going to write: "The ballooning database / database bloat may be resolved sufficiently. Thanks to Pat Hartman for her reply on this topic: http://www.access-programmers.co.uk/forums/showthread.php?t=48759 I had replaced standard With rst .AddNew !EMPID = rst2!EMPID...

Hi Dave, I'm importing anywhere between 200Mb and 1Gb data into the initial backend db for each month prior to processing. As an employee schedule may be set months/years previously, or may run through the month being processed, I decided I needed to store the max schedule from previous months...

Doc Man, Thanks for sharing that scenario. I don't think it is applicable here though as I'm simply passing values to an INSERT statement: cdb.Execute "INSERT INTO Table ( EMPID, BEGINDATE, DAY_NO, " _ & "HOURS, STARTTIME, ENDTIME, DateOfSchedule) " _ & "VALUES ('" & rst!EMPID & "', '" &...

Hi all, Valuable comments again. Thank you all! I've set the Union query to Union all; have added indexes on joining columns and have timed the whole process through several loops to get an idea of performance; also have checked at what point the database is increasing in size. Interestingly...

Update as of Wednesday AM. I've further refactored queries being used in the process to minimize use of sub queries and replaced the ultimate code used to write the schedule (using recordset.add / .update) with a db.execute sql where the sql is a dynamic append/insert values string. I set this...

Hi Dave, That step suggestion is very close to the process I'm following. I'm indeed only looking at one employee at a time, writing their schedules to a separate table and closing it. That final table/Db is the only one that is not getting bloated.

SpikePL, Turns out I only had two union queries in the whole application. One wasn't relevant to the process causing the error as it runs at an earlier stage. Changed the other to UNION ALL. Not sure what effect this will have... My understanding of UNION ALL is that it returns ALL records as...

Hi Doc Man, That is a useful reply, thank you. While none of the backend databases are approaching 2Gb in size while running the process, together they are greater than 1Gb just with the data. I would like to know and be able to test whether the query result sets are increasing the front end...

Hi SpikePL, Yeah, that's what I'm wondering. I've been careful to close all open recordsets as I finished looping through employees/months, but is it possible that closing them and setting them to nothing does not free up memory - so since I'm processing large numbers of records, the space used...

Dave, Getting off topic now, but I've been refactoring as I've developed and maybe it would be possible to make large parts of the process redundant. One possibility would be to use some form of cartesian join. Return the schedule start date, day of schedule and the date of the next schedule...

Hi Dave, The client requirement is for a report providing schedule hours for each employee over two years. Some employees have a schedule for a week which once set never changes. (some going back a decade or more) That employee may have a maximum of seven rows of data. Other employees have...

Hi Spikepl, Thanks for the reply. The query I'm trying to execute when I get the error is a simple select query, but there are union queries in other parts of the process. I'll try modifying them.