Recordset who only returns one record

[ciberwritter]

Hello!

Im using an access aplication to manipulate the data in several tables in SQL SERVER.
im able to manipulate all the data in the tables using querys in access, but onde of the tables is giving me the folowing problem:
I make a query and it returns the values, but when i do that in vba code, the recordset that i create to manupulate the data, it only returns onde record instead of all the records.
Does anybody knows how can i solve this problem?

Thanks.
Alberto Almeida

[XLGibbs]

What does the code that brings in the recordset look like.

You may need to cycle through the recordset to perform your manipulation..


Do While Not rst.EOF
'your manipulation code
rst.MoveNext
Loop


Why would you not just use the queries to do the manipulation as well?

I see a lot of this type of thing since I run the SQL server, and the analysts I service use Access to get at it....

[ciberwritter]

Hi Xl Gibbs. thansk for your atention.

This is the code:


Private Sub Comando4_DblClick(Cancel As Integer)
If IsNull([SELCUR]) Then Exit Sub
Dim a As Integer
Dim MYSET As Recordset
Dim SQLSTR As String
SQLSTR = "SELECT ALUNOS.[NUM ALUNO], ALUNOS.ANO FROM Alunos"
SQLSTR = SQLSTR & " WHERE (((ALUNOS.[TIPO CLI])='AF') AND ((ALUNOS.CURSO)='" & [Forms]![SELLANCADISCIPL]![SELCUR] & "'));"
Set MYSET = CurrentDb.OpenRecordset(SQLSTR, DB_OPEN_DYNASET)
a = MYSET.RecordCount (thi variable shoul tell me the number of records in my recordset and only returns one record.)
Do Until MYSET.EOF
LancaDisciplinas MYSET![NUM ALUNO], [SELCUR], MYSET!ANO
MYSET.MoveNext
Loop
MYSET.Close
MsgBox "Terminou o lançamento de disciplinas do curso " & [SELCUR], vbInformation
End Sub


Using the tables in access the variable 'a' retuns 230 records, but using the same table in sql server int only returns one record.

Thanks.

[XLGibbs]

Make sure your Form is open with the criteria properly filled in....it is probably that your criteria statement is limiting the records...

[ciberwritter]

Its Open.

As i told in the previous post, if the table is in access this problem doesnt happen, it only happens when the table is linked from a sql server.

Thanks.

[XLGibbs]

I would double check then. There is no reason that the records would be limited to only 1 record if more records contain the criteria identified. i read your prior post, and deal quite a bit with both platforms.

Something as simple as a trailing space in the SQL table field that does not come in with the Access based table can impact the result set.

The only limiting factors in the recordset are the criteria, so it is there that the problem lies. Your code is otherwise correct, so it seems to me the only logical place to look.

The table you are querying according the SQL statement above is also present in Access (either by linking ODBC to SQL, or as a hard table). I would be examining the criteria (or the field the criteria is contained in...)

[ciberwritter]

:thumb .

Its very wierd!!

I create a query in access wich returns the records.
When i replace the query in the code (while opening the recordset)
it happens the same thing!! only returns one record!!:(

thanks for the help.

[XLGibbs]

Why not do a DoCmd.OpenQuery and cycle through the recordset that works?

You can open a connection tot he table and set another runSQL command to do updates if necessary...

just a thought.

[ciberwritter]

Doing the openquery doesnt solve, because i need the records one by one in my function LancaDisciplinas MYSET![NUM ALUNO], [SELCUR], MYSET!ANO""!!

[XLGibbs]

Set MYSET = CurrentDb.OpenRecordset(SQLSTR, DB_OPEN_DYNASET,acForwardOnly)

Try that instead of your existing open statement. You may be inadvertandtly telling it to only get the first record..

[geekgirlau]

Set MYSET = CurrentDb.OpenRecordset(SQLSTR, DB_OPEN_DYNASET)
With MYSET
.MoveLast
.MoveFirst
a = .RecordCount
End With

I haven't checked to see whether this bug has now been resolved, however RecordCount cannot be relied upon to give an accurate count unless you move to the last record.

[ciberwritter]

Thanks for the help.
The movelast and movefirst solve the problem!!

Best regards to all,
Alberto Almeida.

[Norie]

Alberto

Are you sure only one record is returned?

If you are basing that on what you are getting using RecordCount then as geekgirlau has pointed out that's not reliable.