views:

4600

answers:

11

I have a function I've written that was initially supposed to take a string field and populate an excel spreadsheet with the values. Those values continually came up null. I started tracking it back to the recordset and found that despite the query being valid and running properly through the Access query analyzer the recordset was empty or had missing fields.

To test the problem, I created a sub in which I created a query, opened a recordset, and then paged through the values (outputting them to a messagebox). The most perplexing part of the problem seems to revolve around the "WHERE" clause of the query. If I don't put a "WHERE" clause on the query, the recordset always has data and the values for "DESCRIPTION" are normal.

If I put anything in for the WHERE clause the recordset comes back either totally empty (rs.EOF = true) or the Description field is totally blank where the other fields have values. I want to stress again that if I debug.print the query, I can copy/paste it into the query analyzer and get a valid and returned values that I expect.

I'd sure appreciate some help with this. Thank you!

Private Sub NewTest()

'  Dimension Variables
'----------------------------------------------------------
Dim rsNewTest As ADODB.Recordset
Dim sqlNewTest As String
Dim Counter As Integer

'  Set variables
'----------------------------------------------------------
Set rsNewTest = New ADODB.Recordset

sqlNewTest = "SELECT dbo_partmtl.partnum as [Job/Sub], dbo_partmtl.revisionnum as Rev, " & _
                "dbo_part.partdescription as Description, dbo_partmtl.qtyper as [Qty Per] " & _
            "FROM dbo_partmtl " & _
            "LEFT JOIN dbo_part ON dbo_partmtl.partnum = dbo_part.partnum " & _
            "WHERE dbo_partmtl.mtlpartnum=" & Chr(34) & "3C16470" & Chr(34)

'  Open recordset
rsNewTest.Open sqlNewTest, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

    Do Until rsNewTest.EOF

        For Counter = 0 To rsNewTest.Fields.Count - 1
            MsgBox rsNewTest.Fields(Counter).Name
        Next

        MsgBox rsNewTest.Fields("Description")

        rsNewTest.MoveNext

    Loop

'  close the recordset

rsNewTest.Close
Set rsNewTest = Nothing

End Sub

EDIT: Someone requested that I post the DEBUG.PRINT of the query. Here it is:

SELECT dbo_partmtl.partnum as [Job/Sub], dbo_partmtl.revisionnum as Rev, dbo_part.partdescription as [Description], dbo_partmtl.qtyper as [Qty Per] FROM dbo_partmtl LEFT JOIN dbo_part ON dbo_partmtl.partnum = dbo_part.partnum WHERE dbo_partmtl.mtlpartnum='3C16470'
+1  A: 

Description is a reserved word - put some [] brackets around it in the SELECT statement

EDIT

Try naming the column something besides Description

Also are you sure you are using the same values in the where clause - because it is a left join so the Description field will be blank if there is no corresponding record in dbo_part

EDIT AGAIN

If you are getting funny results - try a Compact/Repair Database - It might be corrupted

DJ
A: 

I put brackets around the word "Description" in the SELECT statement, but it's behavior remains. It works fine as long as I don't put anything in the WHERE clause. I've found if I put anything in the where clause, the description is blank (despite showing up in the Query analyzer). If I use a LIKE statement in the WHERE clause, the entire recordset is empty but it still works properly in the Query Analyzer.

Mr Furious
Try naming the column something besides Description
DJ
Also are you sure you are using the same values in the where clause - because it is a left join so the Description field will be blank if there is no corresponding record in dbo_part
DJ
I have tried renaming the column. I posted the debug.print so you could see the exact query being entered into the query analyzer.
Mr Furious
"Description" is a reserved word in Access, though I'm not sure if it's a Jet reserved word.
David-W-Fenton
A: 

I have tried double and single quotes using ASCII characters and implicitly.

For example:

"WHERE dbo_partmtl.mtlpartnum='3C16470'"

I even tried your suggestion with chr(39):

"WHERE dbo_partmtl.mtlpartnum=" & Chr(39) & "3C16470" & Chr(39)

Both return a null value for description. However, if I debug.print the query and paste it into the Access query analyzer, it displays just fine. Again (as a side note), if I do a LIKE statement in the WHERE clause, it will give me a completely empty recordset. Something is really wonky here.

Mr Furious
can you edit your original question and add the SQL statement that you see with debug.print? Thanks
Russ Cam
Sure - I'll edit it now.
Mr Furious
A: 

Absolutely I do. Remember, the DEBUG.PRINT query you see runs perfectly in the query analyzer. It returns the following:

Job/Sub Rev Description Qty Per 36511C01 A MAIN ELECTRICAL ENCLOSURE 1 36515C0V A VISION SYSTEM 1 36529C01 A MAIN ELECTRICAL ENCLOSURE 1

However, the same query returns empty values for Description (everything else is the same) when run through the recordset (messagebox errors because of "Null" value).

Mr Furious
A: 

I tried renaming the "description" field to "testdep", but it's still empty. The only way to make it display data is to remove the WHERE section of the query. I'm starting to believe this is a problem with ADO. Maybe I'll rewriting it with DAO and seeing what results i get.

EDIT: I also tried compacting and repairing a couple of times. No dice.

Mr Furious
If you're programming in Access, why are you using ADO in the first place?
David-W-Fenton
+1  A: 

Well, what I feared is the case. It works FINE with DAO but not ADO.

Here is the working code:

Private Sub AltTest()

'  Dimension Variables
'----------------------------------------------------------
Dim rsNewTest As DAO.Recordset
Dim dbl As DAO.Database

Dim sqlNewTest As String
Dim Counter As Integer

'  Set variables
'----------------------------------------------------------

sqlNewTest = "SELECT dbo_partmtl.partnum as [Job/Sub], dbo_partmtl.revisionnum as Rev, " & _
                "dbo_part.partdescription as [TestDep], dbo_partmtl.qtyper as [Qty Per] " & _
            "FROM dbo_partmtl " & _
            "LEFT JOIN dbo_part ON dbo_partmtl.partnum = dbo_part.partnum " & _
            "WHERE dbo_partmtl.mtlpartnum=" & Chr(39) & "3C16470" & Chr(39)


Debug.Print "sqlNewTest: " & sqlNewTest
Set dbl = CurrentDb()
Set rsNewTest = dbl.OpenRecordset(sqlNewTest, dbOpenDynaset)


' rsnewtest.OpenRecordset

    Do Until rsNewTest.EOF

        For Counter = 0 To rsNewTest.Fields.Count - 1
            MsgBox rsNewTest.Fields(Counter).Name
        Next

        MsgBox rsNewTest.Fields("TestDep")

        rsNewTest.MoveNext

    Loop

'  close the recordset

dbl.Close
Set rsNewTest = Nothing

End Sub

I don't use DAO anywhere in this database and would prefer not to start. Where do we go from here?

Mr Furious
Are you using the latest ADO version? What version do you have installed? Maybe download and install the latest MDAC package?
DJ
I'm running on Vista 64 - SP1. The reference is saying ADO v2.8... however, I have tried 2.5 and 2.6 also. You know - Office 2007 is looking worse all the time. I never had ADO problems in 2003. :\
Mr Furious
I've had problems with buggy ADO before (version 2.6) but 2.8 has been pretty rock solid for me.
DJ
I have an option in reference for ADO version 6.0... Never heard of that before...
Mr Furious
That's for Vista - that's the version you should probably be using!!
DJ
Unfortunately, if I use that one I won't be able to use this database on any non-vista PC then.
Mr Furious
Why is there some problem with using DAO in an Access front end? If it's an Access front end, DAO should be your first choice, anyway.
David-W-Fenton
There isn't any real reason I can't use DAO. I'm switching back and forth between that and some other development work that uses ADO so it was nice to be able to use the same syntax. Realistically there isn't any reason it can't be used. That said, I'd still like to solve the issue. ;)
Mr Furious
I never did find a solution for this, so I just accepted the fact that I would need to use DAO.
Mr Furious
+1  A: 

What type is the Description field? Can you try


Do while not rs.EOF
......
rs.MoveNext
Loop

1 more thing, why should you use ADO when MS-Access has built-in support for DAO (no need to reference any external library).

shahkalpesh
A: 

Ultimately I think it's a problem with running ADO 2.8 on Vista 64

Personally I have always used DAO in Access projects.

DJ
A: 

Here is an interesting tidbit. The tables are linked to a SQL Server. If I copy the tables (data and structure) locally, the ADO code above worked flawlessly. If I use DAO it works fine. I've tried this code on Windows XP, Access 2003, and various versions of ADO (2.5, 2.6, 2.8). ADO will not work if the tables are linked.

There is some flaw in ADO that causes the issue.

Mr Furious
Just a thought, why not use ADO to connect to the SQL Server and populate a recordset by executing the SQL statement on the SQL Server?
Russ Cam
I'm not entirely certain how that would be accomplished. Are you suggesting that the function would make it's own direct connection rather than use a linked table?
Mr Furious
precisely. I can post an example if you're interested.
Russ Cam
Conceptually how would that differ from using a linked table? The linked table is using ODBC which is using a SQL driver. Wouldn't the ADO connection be doing the same just setting up a temporary connection?
Mr Furious
NOW you tell us that you are using linked tables!! As for Russ' comment of course it's different. ADO -> SQL Driver vs ADO -> Jet -> ODBC -> SQL Driver
DJ
I didn't realize it was relevant initially. Now that I discovered it was, I made sure to post it.
Mr Furious
A: 

When using ADO LIKE searches must use % instead of *. I know * works in Access but for some stupid reason ADO won't work unless you use % instead.

I had the same problem and ran accoss this forum while trying to fix it. Replacing *'s with %'s worked for me.

A: 

I have the same exact problem! Only, my tables are not linked. They are simply Access tables that reside within the same Access 2007 database. I've performed this routine hundreds of times before with ADO v2.8 and I've had no problems. I've used '*' in the WHERE clause without issue, too. For some reason, this is no longer working, and I haven't got the slightest clue. I'd really prefer not to use DAO, because I've only ever used ADO and I've read that DAO actually does not support some of the things I've tried to do in the past. Anyone figure this out yet?