There is a problem with this example at Microsoft heard it before

  • Thread starter Thread starter George Hester
  • Start date Start date
G

George Hester

http://msdn.microsoft.com/library/en-us/dnvbpj00/html/sqlado.asp?frame=true

In here you will see the function I am using called ExecuteSQL:

http://msdn.microsoft.com/library/en-us/dnvbpj00/html/sqladoisting.asp?frame=true

I am not using all the Forms, Command Buttons and what not. This is a very simple function which can operate on its own without all the dressing. In fact it doesn't even need to use ADODB.Recordset. It can use ADOR.Recordset as well and this is the way I am using it. For this particular example this does not matter. The problem will still occur.

If the SQL statement is such that you see here:

strSQL6 = "SELECT [" & strTblNameNonKeyed & "].[" & strFldName1 & "], [" & strTblNameNonKeyed & "].[" & strFldName2 & "] " & _
"FROM [" & strTblNameNonKeyed & "] " & _
"LEFT JOIN [" & strTblNameLinked & "] " & _
"ON [" & strTblNameNonKeyed & "].[" & strFldName2 & "] = [" & strTblNameLinked & "].[" & strFldName2 & "] " & _
"WHERE ((([" & strTblNameLinked & "].[" & strFldName2 & "]) Is Null));"

and if this SQL returns only one record (here I am comparing entries in strTblNameNonKeyed with entries in strTblNameLinked then the above function ExecuteSQL will fail. It succeeds on a SQL statement that returns 2 or more records but if the SQL statement returns less then two we have a failure. In fact it will always miss the first record that is returned from the above SQL statement. It fails to return the the records generated by this SQL statement in its entirely. Try it.

Use any SQL statement that returns more then one record and see if the result of ExecuteSQL is not one less..
 
I think I found the trouble. The Join query that Access Generated for me is not correct. I will have to fix this and see what happens. This join Query is skipping the last record in the strTblNameNonKeyed and repeating the first record there. That is the result of this Query. I suspect that is where the troubles are coming from. I made the strTblNameLinked completely blank so it should have returned all records from strTblNameNonKeyed. The query just ain't right.

--
George Hester
__________________________________
http://msdn.microsoft.com/library/en-us/dnvbpj00/html/sqlado.asp?frame=true

In here you will see the function I am using called ExecuteSQL:

http://msdn.microsoft.com/library/en-us/dnvbpj00/html/sqladoisting.asp?frame=true

I am not using all the Forms, Command Buttons and what not. This is a very simple function which can operate on its own without all the dressing. In fact it doesn't even need to use ADODB.Recordset. It can use ADOR.Recordset as well and this is the way I am using it. For this particular example this does not matter. The problem will still occur.

If the SQL statement is such that you see here:

strSQL6 = "SELECT [" & strTblNameNonKeyed & "].[" & strFldName1 & "], [" & strTblNameNonKeyed & "].[" & strFldName2 & "] " & _
"FROM [" & strTblNameNonKeyed & "] " & _
"LEFT JOIN [" & strTblNameLinked & "] " & _
"ON [" & strTblNameNonKeyed & "].[" & strFldName2 & "] = [" & strTblNameLinked & "].[" & strFldName2 & "] " & _
"WHERE ((([" & strTblNameLinked & "].[" & strFldName2 & "]) Is Null));"

and if this SQL returns only one record (here I am comparing entries in strTblNameNonKeyed with entries in strTblNameLinked then the above function ExecuteSQL will fail. It succeeds on a SQL statement that returns 2 or more records but if the SQL statement returns less then two we have a failure. In fact it will always miss the first record that is returned from the above SQL statement. It fails to return the the records generated by this SQL statement in its entirely. Try it.

Use any SQL statement that returns more then one record and see if the result of ExecuteSQL is not one less..
 
(off-topic) You can use aliases to shorten your SQL String
if you want to use full reference to the Fields. For
example, you can replace the SQL posted with something
like:

strSQL6 = "SELECT TNonKeyed.[" & strFldName1 & _
"], TNonKeyed.[" & strFldName2 & "] " & _
"FROM [" & strTblNameNonKeyed & "] AS TNonKeyed " & _
"LEFT JOIN [" & strTblNameLinked & "] AS TLinked " & _
"ON TNonKeyed.[" & strFldName2 & "] = TLinked.[" & _
strFldName2 & "] " & _
"WHERE (TLinked.[" & strFldName2 & "] Is Null)"

HTH
Van T. Dinh
MVP (Access)



-----Original Message-----
I think I found the trouble. The Join query that Access
Click to expand...
Generated for me is not correct. I will have to fix this
and see what happens. This join Query is skipping the
last record in the strTblNameNonKeyed and repeating the
first record there. That is the result of this Query. I
suspect that is where the troubles are coming from. I
made the strTblNameLinked completely blank so it should
have returned all records from strTblNameNonKeyed. The
query just ain't right.
 
Huh!!!

It turns out that yes a record was missed all the time. Always one record was missed. The way this worked (LOL) as I was using it is a sub was getting an array. This array sometimes would have 0 members sometimes 1 member and mostly many members. But if this array had no more than 1 member then when the ExecuteSQL was reached it would throw a BOF or EOF problem error. It seems that the array sent to the first sub and used as data for the above ExecuteSQL was causing the problem. I had to Redim Preserve this array by one more then it originally had. No entry necessary for its last member but it had to have one more dimension then it has in its original form.

This thing was running fine in all the tests. But I did not inspect the results closely enough to determine if all the data that was obtained was in fact passed into my Linked Table. The Linked Table has over 6000 records and it is not easy scouring through that to determine if it had all it should have had. But I reduced it down to about 6 records and then sent it what should have been one more record. And this is where the problem manifested itsself. That one record was never appearing in the Linked Table.

Well luckily I set this thing up so that every array passed into the parent sub has at least one member. But I can see another problem on the horizon with this.

I understand many may not have the slightest idea what I am talking about. That is just how the ball bounces. I don't want to leave a question I posed hanging so in that regard this missing record has been determined. Let me just make sure...yup. That was the trouble. The array has to be redimmed keyworded by one more then it originally has.
--
George Hester
__________________________________
http://msdn.microsoft.com/library/en-us/dnvbpj00/html/sqlado.asp?frame=true

In here you will see the function I am using called ExecuteSQL:

http://msdn.microsoft.com/library/en-us/dnvbpj00/html/sqladoisting.asp?frame=true

I am not using all the Forms, Command Buttons and what not. This is a very simple function which can operate on its own without all the dressing. In fact it doesn't even need to use ADODB.Recordset. It can use ADOR.Recordset as well and this is the way I am using it. For this particular example this does not matter. The problem will still occur.

If the SQL statement is such that you see here:

strSQL6 = "SELECT [" & strTblNameNonKeyed & "].[" & strFldName1 & "], [" & strTblNameNonKeyed & "].[" & strFldName2 & "] " & _
"FROM [" & strTblNameNonKeyed & "] " & _
"LEFT JOIN [" & strTblNameLinked & "] " & _
"ON [" & strTblNameNonKeyed & "].[" & strFldName2 & "] = [" & strTblNameLinked & "].[" & strFldName2 & "] " & _
"WHERE ((([" & strTblNameLinked & "].[" & strFldName2 & "]) Is Null));"

and if this SQL returns only one record (here I am comparing entries in strTblNameNonKeyed with entries in strTblNameLinked then the above function ExecuteSQL will fail. It succeeds on a SQL statement that returns 2 or more records but if the SQL statement returns less then two we have a failure. In fact it will always miss the first record that is returned from the above SQL statement. It fails to return the the records generated by this SQL statement in its entirely. Try it.

Use any SQL statement that returns more then one record and see if the result of ExecuteSQL is not one less..
 
Not sure what you are showing. The table names have spaces in them. If the table names have spaces in them then I thought they had to be inclosed in []. Not sure what you mean by alias. Wouldn't I have to define that somewhere. Which is what I'm doing anyway. But I'll see what this alias thing is about. Hey off topic don't matter. I appreciate the suggestion.

--
George Hester
__________________________________
Van T. Dinh said:
(off-topic) You can use aliases to shorten your SQL String
if you want to use full reference to the Fields. For
example, you can replace the SQL posted with something
like:

strSQL6 = "SELECT TNonKeyed.[" & strFldName1 & _
"], TNonKeyed.[" & strFldName2 & "] " & _
"FROM [" & strTblNameNonKeyed & "] AS TNonKeyed " & _
"LEFT JOIN [" & strTblNameLinked & "] AS TLinked " & _
"ON TNonKeyed.[" & strFldName2 & "] = TLinked.[" & _
strFldName2 & "] " & _
"WHERE (TLinked.[" & strFldName2 & "] Is Null)"

HTH
Van T. Dinh
MVP (Access)



-----Original Message-----
I think I found the trouble. The Join query that Access
Click to expand...
Generated for me is not correct. I will have to fix this
and see what happens. This join Query is skipping the
last record in the strTblNameNonKeyed and repeating the
first record there. That is the result of this Query. I
suspect that is where the troubles are coming from. I
made the strTblNameLinked completely blank so it should
have returned all records from strTblNameNonKeyed. The
query just ain't right.
Click to expand...
 
1. Well ... your Table Names may have spaces but the Aliases I used
("TNonKeyed" and "TLinked") don't have spaces so there is no needs for the
square brackets around the Aliases.

2. Say if strTblNameNonKeyed = "Table Name With Spaces", the part after
the word "FROM" in the SQL String I suggested becomes:

FROM [Table Name With Spaces] AS TNonKeyed

See the keyword "AS" and the Alias "TNonKeyed". In effect, the Table "Table
Name With Spaces" is known by the Alias "TNonKeyed" in the processing of the
Query and you only have to refer to the original Table Name only *once* in
the query construction. You simply use the fixed alias in your string
concatenation to reduce the number of times you have to use the
concatenation operator "&".

Note also that I have retained the square brackets around the original /
actual Table Name.

3. No, you haven't done what I suggested as per explanation in points 1 &
2.

4. Also, you should read up on naming convention. The first thing in any
naming convention is to avoid white spaces in Table / Field / Object names.
White spaces simply create more complications for you.

--
HTH
Van T. Dinh
MVP (Access)




Not sure what you are showing. The table names have spaces in them. If the
table names have spaces in them then I thought they had to be inclosed in
[]. Not sure what you mean by alias. Wouldn't I have to define that
somewhere. Which is what I'm doing anyway. But I'll see what this alias
thing is about. Hey off topic don't matter. I appreciate the suggestion.
 
I knew the spaces in the table names would cause me more work. But I wanted to do it anyway. Because heck I'm going to throw those brackects in anyway so what difference does it make? But yes the difference is loooong string concatenation. I'll try that alias thing. I understand now what you said.
 
George Hester said:
Huh!!!

It turns out that yes a record was missed all the time. Always one
record was missed. The way this worked (LOL) as I was using it is a
sub was getting an array. This array sometimes would have 0 members
sometimes 1 member and mostly many members. But if this array had no
more than 1 member then when the ExecuteSQL was reached it would
throw a BOF or EOF problem error. It seems that the array sent to
the first sub and used as data for the above ExecuteSQL was causing
the problem. I had to Redim Preserve this array by one more then it
originally had. No entry necessary for its last member but it had to
have one more dimension then it has in its original form.

This thing was running fine in all the tests. But I did not inspect
the results closely enough to determine if all the data that was
obtained was in fact passed into my Linked Table. The Linked Table
has over 6000 records and it is not easy scouring through that to
determine if it had all it should have had. But I reduced it down to
about 6 records and then sent it what should have been one more
record. And this is where the problem manifested itsself. That one
record was never appearing in the Linked Table.

Well luckily I set this thing up so that every array passed into the
parent sub has at least one member. But I can see another problem on
the horizon with this.

I understand many may not have the slightest idea what I am talking
about. That is just how the ball bounces. I don't want to leave a
question I posed hanging so in that regard this missing record has
been determined. Let me just make sure...yup. That was the trouble.
The array has to be redimmed keyworded by one more then it originally
has.
Click to expand...

I'm confused as to exactly what the problem was and whether you're still
saying it was in the execution of the query itself, or whether you're
now saying it had something to do with your handling of the array. The
only common "user error" I can think of with regard to arrays is that in
Access VBA, the default lower bound of an array is 0. So the first
element of an array declared with (for example) "Dim A(10)" is A(0), not
A(1). Is that in any way related to your problem?
 
George Hester said:
Not sure what you are showing. The table names have spaces in them. If the table names have spaces in them then I thought they had to be inclosed in [].
Click to expand...

FWIW I came across a bug with nested queries which had spaces in them.
You may be encountering the same problem with table names.

The Upsizing got cancelled but my solution would've been to rename all
the queries to not have any spaces in the names. And do a massive
find and replace throughout the MDB. All programmatically of course.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
No not really. I did this in the Sub

Dim strLoc(3)
strLoc(0) = "www.microsoft.com"
strLoc(1) = "www.adobe.com"
strLoc(2) = "www.microsoft.com"
strLoc(3) = "www.mrsnooze.com"

(These are really not active links in the tables in the database)

So the Sub was something like this.

Public Sub LinkSchema(Optional dBName = "mydat.mdb")
End Sub

in anticipation of something like this:

Public Sub LinkSchema(strLoc() As Variant, Optional dBName = "mydat.mdb")
End Sub

In this Sub the data in the array was manipulated in various ways. This array was written to a table. That always worked fine. But this table would then be comapred to my Text Database as a Linked Table. And all entries that were in the above array that were NOT in the Linked table would be written to another constructed table MakeTableQuery. This is where the problems began. The new table would NOT conatain all the entries in the above array (which were duplicated in the the table they went to). ALWAYS one less. So if the array had only one member AND that value was not in the Linked table, then the new Table which was a MakeTableQuery would barf. Also barf if this array all its members were in the Linked Table. Now this case was easily avoided. The problem manifested iteself when the above array had only one member (note multi-enties are considered one entry) and that entry was NOT in the Linked table. Barfo.

So I did this:

Dim strLoc(3)
ReDim Preserve strLoc(UBound(strLoc) + 1)
strLoc(0) = "www.microsoft.com"
strLoc(1) = "www.adobe.com"
strLoc(2) = "www.microsoft.com"
strLoc(3) = "www.mrsnooze.com"

and that cleared the problem up. That problem. See this will not be hardcoded this way when it is ready for Prime Time. Another Sub I have will feed this Sub an array. But this first Sub, the Daddy Sub. That Sub will execute in full in a matter of seconds. If information is passed to this Sub I have been describing then the MakeTables and the ExecuteSQL functions which resides in the Sub (with strLoc()) don't have enough time to do what needs to be done and so I get a 3260 error. Not always but often. And then when the 3260 error happens my Linked Table is wasted. Everything in the text file is deleted and then the program says, "Hey you don't have this array entry in the Linked Table. I'll write it now.' And there went my 6000 record text file to one record.

This behavior seems endemic and so I am pondering on a new way to do this. Thanks. Hoped that helped explain the issue.
 
Back
Top