Access 2007 VBA Query Shows Data in Query Analyzer But Not in VBA Coded Recordset

Question

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'

Answer 1

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

Answer 2

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.

Answer 3

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.

Answer 4

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).

Answer 5

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.

Answer 6

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?

Answer 7

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).

Answer 8

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

Personally I have always used DAO in Access projects.

Answer 9

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.

Answer 10

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.

Answer 11

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?