Replies

Back to this problem after putting it on the backburner for a while. Tried a number of different approaches, but nothing that I could devise using a conventional SQL and JOIN worked with acceptable performance. All were absurdly slow. So I resorted to stepping thru the one table with a DAO loop, setting some parameters in a parameter query, then executing the query to update the second table. It was contrary to my basic approach that an SQL approach was always better than brute-force DAO, but I was proven wrong. One process was ontrack to take multiple dozens of hours to complete. The DAO loop approach was completed in 20 minutes.

I also changed my tactic of building an SQL statement from scratch in every loop, and replaced it with the parameter query. Each time an SQL statement is executed, the Jet engine must compile it. By contrast, the parameter query is compiled once, then just executed within the loop. There is a significant reduction in processing time with the pre-defined query.

Anyway, the program works now with acceptable performance. Just thought I would wrap up this thread…

Sorry for the confusion. I forget how difficult it is to look at someone else’s work.

Yes, you are correct about the objective.

The ZoneID and DayNo fields are used to pare down the list to a more manageable size that processes more quickly. If all the records are processed at once, there is no feedback to the user that anything is happening. By running the query multiple times, at least there can be some on-screen confirmation provided by the VBA loop that the computer has not hung up. Through experimentation, I found that a tables with about 7500 records process at about 50-60 records per second, while the tables with 150000 records process at about 5 records per second. Huge difference by running the query multiple times on a subset of the data versus all of the data at once.

Other than the GPS.ATIIDfk Is Not Null clause that you added, your query is essentially the same as what I had in a previous version. Grasping at straws, I converted it from the INNER JOIN syntax to the WHERE syntax in case there was some weird interaction. However, both syntaxes work the same with the same processing speed.

I will try your query to see if it makes any difference, however, my similar query also took forever to run.

I really appreciate your looking at this.

Mark
It is an update query. I’ve tried all sorts of stuff to speed it up, including exporting the critical data into new tables in a new, temporary MDB file so it is always compacted when the query is run. I am really starting to question the underlying logic of my query. Perhaps you would be willing to look at my logic.

The tables are generated automatically by data loggers installed on various heavy-duty mobile equipment. One table records GPS information and is typically characterized by a “TimeTag” field which records when the GPS point was acquired. A second table records a TimeLine — what the machine was doing at various times during the day. Each record is characterized by StartTime and StopTime fields. The two tables are not related in the original database, and that’s the purpose of this query — to populate the tables with the appropriate foreign key to the other table.

Here is the SQL of the query that is executed in a VBA loop that supplies the various parameters:

PARAMETERS CurrentMachineID Value, CurrentDayNo Value, CurrentZoneID Value;
UPDATE tmpTimelineSelected AS TL, tmpGPSPosSelected AS GPS SET GPS.ATIIDfk = tl.Recnum
WHERE (((GPS.TimeTag)>=[starttime] And (GPS.TimeTag)<[stoptime]) AND ((GPS.MachineID)=[CurrentMachineID]) AND tl.zoneID=[currentzoneid] and ((TL.MachineID)=[CurrentMachineID]) AND ((TL.DayNo)=[CurrentDayNo]));

The query updates the ATIIDfk field in the GPS table with the RecNum value from the appropriate record of the TimeLine table. It relates the two tables by finding the TimeLine interval that contains the TimeTag of each GPS record. The VBA loop cycles by day and by ZoneID to break the problem into more bite-sized pieces that provide visual feedback that the process has not stalled. All the fields are indexed, and I've used the JETSHOWPLAN routine to confirm that the indexes are being used.

The attached file shows the table structures and some sample data. The actual GPS table contains up to 250000 records and the TimeLine table contains up to 150000 records. Processing time is in the tens of hours.

Is there a better way to write the query? I would appreciate any feedback.

It’s “wait till the end of the workday and set it up for overnight processing” absurd!
I’ll take your point about SQL Server under advisement, but this particular process is governed by a third-party application that uses MDB database. I have very limited experience with SQL server — perhaps exporting to SQL, doing this particular processing, then importing the results back to MDB might be faster.

Thanks for the input.

Details… The Int(StartTime) function was the culprit. Worked OK with the hardwired version, but failed with the parameter version. Changing it to CLng made it work.

Might be exactly the opposite problem. Using the JETSHOWPLAN item in the Registry shows that the optimizer is being invoked every time the db.execute statement is run. Looks like it might be too many calls to the optimizer that is taking so much time.

Thanks, Hans. You’ve come thru as usual!
Now I have to dissect that formula into its components so I can deal with the other parts of the worksheet that I did not include in the simplified example.

Ooops – overlooked the “Upload File” button.

OK – got it figured out with some expert help at the community centre. There is a odd-ball situation where a network has been (incorrectly) configured and not yet successfully connected-to. In this situation, the network does NOT appear on the list of networks in the “Manage Wireless Network” dialog, so it is invisible from that perspective. However, clicking on the Networks icon in the System Tray (Notification Area) displays a list of the currently-active access points to choose which one to connect to… Rather than left-clicking, you must right-click on the access point and choose Properties. From there, you can change its password. Once I had corrected the password, it connected flawlessly. If a network has NOT been configured, then right-clicking on its name does NOT provide the Properties choice.

Failing to right-click and fix the properties, the only thing you can do is to click the Connect button. Since the password is incorrect, it fails to connect, and takes you to a trouble-shooting mode. NOTHING in the troubleshooting talks about incorrect passwords; in fact, it advises you to reboot the access point. That’s kinda impossible when connecting to a public access point!

I guess it’s just one of those things that you have to know about. The guidance from Windows (Vista and 7) is less than helpful in this situation.

In the Network and Sharing Centre there is a link on the left to “Manage Wireless Networks”.
This should allow you to delete any existing network, or to change the password.

Sorry – I wasn’t explicit. I’ve already been using “Manage Wireless Network” – the community centre network does not appear in the list. I’ve had no difficult deleting *other* networks — it’s just this one that’s causing me problems.

Again, I am writing this on my home network, but I will take the netbook with me again tonight to the community centre and try again when I’m within range of the culprit wifi. I’ve tried that method unsuccessfully in the past.

Found the answer. Inplace upgrade moves the files into a hidden folder named $inplace.~TR, and it is supposed to move them back to their correct location. Something failed during the install process that left the files in the wrong folder. Just needed to show System files (Explorer Options), then move the files manually to their correct location.

Dunno what caused it to fail during installation…

Hans – finally tracked it down on the VLC website. Turns out that Windows requires both an OPEN and PLAY command to be fully registered. The VLC installer only creates a PLAY command, which works from Explorer, but omits the OPEN command which is required for Hyperlink to work properly.

Isn’t that just a lovely detail…??? The forum’s solution is to create a .REG file for each file extension that is required to be hyperlinked.

After my initial success with KMPlayer, it started behaving strangely. The application would launch successfully, open the file, then immediately close. No idea what changed from its initial success.

Does it help if you re-associate .mp4 files with VLC Player? (I use KMPlayer, and hyperlinks to .mp4 files open KMPlayer without problems)

Thanks, Hans. It seems to be an issue with VLC. I removed VLC from the computer, then the MP4s opened with QuickTime. Re-installed VLC and the hyperlinks failed. Downloaded and installed KMPlayer, and that works properly. Go figure…

You put me onto the right path to find the solution.

And your other answer fixed the annoying messages. Thanks for that, too!

Which application opens MP4 files if you double-click them in Windows Explorer?

It opens with VLC Media Player. I’ve explicitly installed it on this computer.

Great idea. Thanks.