How to use ROWNUM in VB.net to retrieve data from a MS-Access DB

i'm having troubles with a SELECT query. This is the string generated by my program.

   SELECT 
     CODLFA, POSLFA, ARTLFA, DESLFA, CANLFA, 
     DOCLFA, DTPLFA, DCOLFA, PIVLFA, TIVLFA, CE1LFA 
   FROM
     F_LFA 
   WHERE 
     CODLFA IN 
       (
        SELECT 
          CODFAC 
        FROM 
          F_FAC 
        WHERE 
         TIPFAC = '2' 
         AND FECFAC >= 09-02-2013 
         AND CLIFAC = 21 
         AND ROWNUM = 1 
         ORDER BY CODFAC
        ) 
      AND ARTLFA = '00259' 
      AND CE1LFA = '011'

When i put this in the SQL query designer in MS Access a window shows up asking me to enter the ROWNUM number, then it shows the correct row. But in my app written in VB.net it doesn't works. Here is where the query is constructed. Every other queries in my program are working fine.But this one doesn't retrieve any values. That's why i put the Fill command inside a try statement and there is where it fails because newAdapter is empty i guess.

  For Each LABLrow As DataRow In lalbaranes.Rows
            'obteniendo lineas de facturas que coincidan con la fecha, productos y cliente.
            newAdapter = New OleDb.OleDbDataAdapter("SELECT CODLFA, POSLFA, ARTLFA, DESLFA, CANLFA, DOCLFA, DTPLFA, DCOLFA, PIVLFA, TIVLFA, CE1LFA FROM F_LFA WHERE CODLFA IN (SELECT CODFAC FROM F_FAC WHERE TIPFAC = '2' AND FECFAC >= " & ALBfecha & " AND CLIFAC = " & CLIabl & " AND ROWNUM = 1 ORDER BY CODFAC) AND ARTLFA = '" & LABLrow(1) & "' AND CE1LFA = '" & LABLrow(6) & "'", dataConnection)
            newCommand = New OleDb.OleDbCommandBuilder(newAdapter)
            'graba los resultados en la tabla lboletas
            lboletas.Clear()
            Try
                newAdapter.Fill(lboletas)
                newCommand.Dispose()
                newAdapter.Dispose()

How should i select only the first row in F_FAC?

PD: Sorry for my bad English.

Solution

ROWNUM is an Oracle "pseudocolumn" for which there is no direct equivalent in Access SQL. Access prompts you for a value because ROWNUM is not recognized as a column name so it asks you to supply a value. If you were to inspect the error message you receive when you try the query in VB.NET you would see

No value given for one or more required parameters.

Again, this is because ROWNUM is not valid Access SQL.

For your purposes the Access equivalent would be...

SELECT TOP 1 FROM ...

...but be aware that TOP 1 in Access may return more than one row if there is a "tie" in the ORDER BY sort. In your case, if there were two rows with the same [CODFAC] then SELECT TOP 1 ... in Access would actually return two rows.