Search results

Yes the SQL will run fine if I copy and paste it. Yep, I can literally run the process multiple times in a row (the queries will be the same until the next day). The first few will work, then the last fails. Or it fails first time, then runs the next. There are between 15 and 40 queries for a...

Sorry, I missed those. In order: 1. Yes. The error is definitely at that line. 2. Yes, the debug.print prints what I would expect to see. No spaces, special characters, missing characters, etc. 3. Yes. This was the case even before refreshing the window. I also commented out the problematic line...

Alas, same result. If the data weren't confidential, I'd try copying a zipped version of the db itself.

Thanks for the suggestion. Unfortunately, it didn't work. I made the change and ran the process twice successfully, but on the third attempt I got the same error message as originally (albeit for a different query name).

Thanks again. This is the whole function. As I mentioned, frustratingly, it will run one minute but if tried a little while later will fail. Since the queries run will only change day-by-day, it's failing when running SQL that has previously worked. I can confirm this by looking at the debug...

Sadly, this results in the same error at the same point as before If QueryExists(strSheetNameNew) Then Debug.Print strSheetNameNew & " exists" DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, strSheetNameNew, strNewBook, True DoEvents...

This is the sort of thing I suspected I might need. I couldn't work out what phrase to search for. Will make the change and retest. I understand the confusion there, I was trying to keep the code to a minimum when posting. The loop problem doesn't happen - the code either works or it crashes...

The following section of code comes from an Access function. In brief, it checks if a query is present (using another function) and - if so - exports it to a new worksheet in a previously created Excel file. This code works perfectly some of the time. If QueryExists(strSheetNameNew)...

I'm on vacation next week and may not get this resolved before I leave. I'll stay on it, however, and will post back what I find, in case anyone else finds it helpful.

Aha! That sounds possible. In spite of all the laptops supposedly being the same, I do know some people have a much lower connection speeds. If there's a difference in one area, there may well be in another.

Thanks all for the feedback. This one clearly needs more investigation as, from everything that's said, a locked computer should not prevent the db from being shut. Something, however, is and I'll need to narrow down the cause. Edit: Really puzzled now. I just tested this on my own laptop and...

What do you mean by modifying table structure? The back end wouldn't be open, just locked by opening the front.

Thanks. I thought I was clutching at straws, but hoped someone might have found a workaround.

Thanks for the response. No, sorry, Access itself is still running, but the event that's meant to fire based in the form's timer property doesn't fire. One of the ways I shut down the db currently involve a hidden form checking some value in a linked table every three minutes. If the value is...

In order to prevent the pain that is an unknown user being in a database when I need to make a fix/upgrade, I've built the following into the various databases we use: 1. If a db is idle for 15 minutes, it closes 2. If I want to 'kick' someone, I can do it by setting a field in a table to true...

Thanks all for the tips. I've already got various tracking running on anything I built since coming here, it's the old databases that preceded my arrival where the (possible) problems exist. I found some code to check if a particular query is used as a data source by any objects and I can...

Thanks for that. Is it possible to detect the last time a table was referenced?

We have a number of databases that contain many, many queries and I'd like to delete those that nobody has used for a while. I've eliminated from consideration all those that are a source for some other object but I'm still left with a lot. I can identify various properties of Access objects...

Thanks for the response. I wouldn't know where to set them from there. I was hoping to duplicate the steps I'd use for a manual configuration.

A number of our databases connect to remote data sources via ODBC. These connections are configured using the IBM DB2 application. A recurring problem I have is that the department building the laptops for our staff often fail to do the configuring correctly, resulting in my needing to do it...