This is very obviously a quite concise descrption of the error. So I don't even kown where to start with this. I have a class that is simulation some of the .NET ADO functions (ExecuteNonQuery, ExecuteRecordset and ExecuteScalar). I can simply pass some SQL to these methods and it does its thing. The issue I am encountering right now is as follows.
An object is created (lets call it M)
M is then given it's ID
M then creates a new Object called mSQL.
mSQL then creates a new Object called Table (lets call it T)
mSQL then hits a second DB to pull the respective table information and stores it in T
With this information mSQL then pulls the respective fields from the respective tables for the respective object. and stores a new Field Object (lets call it F) for each item. F has the Object variable name, the Field attribute name and a variable to store the initial value (this allows for easy checking to dynamically build the update command)
lets recap. Now we have a 'rosetta stone' of information. A field object is created stores the Object's variable names, the associated Field name for a respective table and an empty variable to store the intial value.
Now that we have this we can dynamically build a SELECT statement to pull the values using the same methods in mSQL.
The problem happens at this point. When trying to pass the following SQL command rs.Open(SQL) line generates the error. where SQL = "SELECT TaskMeasurementID, HowMeasured, Frequency, Analysis, Action, MeasurementStyle, Sequence, Interim, Inactive, Objective, Size FROM tblMeasurements WHERE TaskMeasurementID=51". Which is a pretty simply SQL command. If this string is copied and pasted into the SQL window of the QBE it works fine. None of the words in teh SQL are reserved words.
So with that being said, does anyone have any ideas. I am fairly new to ADO (old fan of DAO, but now need to start writing code that is easily migratable to .NET in the future). Maybe I am missing something. Dunno?????
I'm a bit lost here with everyhing you're doing, but I wonder if the problem is one of timing. I've found in the past that VBA code doesn't execute exactly in the order it should, and I wonder if the SQL command is tryong to read a table that the database hasn't registered as exisitng yet.
We used to do the same sort of thing with our databases where we would create a table and then interrogate it. I gave that up where the table structure remained constant, and simple deleted the old contents and repopulated it. This solved a lot of problems, one of which was that code occasionally failed because of the table not existing at a critical time. You may not have that luxury, but I'd suggest it might be an approach worth trying.
I was able to find out whre the program was hanging. It had to do with an attribute name in the SQL. Apparently the word SIZE is a reserved word (not annotated on MSs Knowledgebase). I simply changed the Attribute Name and the respective table holding the tables metadata and all seems to be good to go now.