getting data - sometimes redundant - good way of dealing with it?

[Edneeis]

Do you even need to save the personal data everytime? Is it going to change all the time? And if it changes then it should be updating the previous entry not adding a new one right?

You could do something like this:

Public Sub UpdatePersonalData(PersonalID as integer)
   'run a query to see if the personalID exists
    rs.open "SELECT * FROM PersonalData WHERE PersonalID=" & PersonalID,cnn,blahblahblah
   If rs.Recordcount<0 then
       'not already there add
       rs.addnew
   End if
       'already there just update
       rs.MoveFirst
       rs!PersonalData=pData
       rs.Update
End Sub

[Helger]

the personal data does not have to change at all once it has been saved once

the problem is now that i have this automated data collecting thing running that will just save all the data it finds on the sheet including personal data.

sooo i cant do anything useful before that collecting thing handed over the data - or can i? and it seems for handing it over it just opens it's own connection. Can i interfere here?

[mdake]

Edneeis,

If I am not mistaken, in your example the rs.RecordCount will be -1, even if there is a record that matches the criteria.
See below....

Public Sub UpdatePersonalData(PersonalID as integer)
'run a query to see if the personalID exists
rs.open "SELECT * FROM PersonalData WHERE PersonalID=" & PersonalID,cnn,blahblahblah
If rs.Recordcount<0 then
'not already there add
rs.addnew
End if
'already there just update
rs.MoveFirst
rs!PersonalData=pData
rs.Update
End Sub

The recordcount is not set until after the rs has been traversed and you can only do that on certain recordsets.

You can use a COUNT SQL query to do this, it works on all types of REcordsets.

Dim cnt as Long
rs.Open "SELECT COUNT(*) FROM PersonalData WHERE
PersonalID=" &PersonalID,cnn, etc
cnt = rs.Fields(0)

Now you can see if cnt is > than 0

[Edneeis]

The recordcount would be greater than 0 if there are matches from the SQL criteria. -1 is only returned in the event of no match or some form of error. What do you mean transversed?

I have used this code with SQL and Access DBs with no problems.

Explain more about the automated data collector thing.

Is it something you made? Is it part of the same program? a DLL? a Function?
Can you make changes to it?

[mdake]

First of all it depends on whether the person is using DAO or ADO.

Most people use ADO I guess.

ADO and DAO are very similar in many ways they have much of the same Properties and Methods, but they do not always behave the same.

Look up RecordCount property in MSDN, it lists 2
RecordCount property
RecordCountProperty(ADO)
because they are different.

For most types of recordset in DAO, the RecordCount the count is not there until you have accessed all of the records (movelast) will do that, that is what I mean to traverse(move through). I didn't say Transversed like in your post.

Now in ADO, RecordCount returns -1 if there are no matches or if you are using a ForwardOnly Recordset.

All this aside if you don't use the Recordset.Open method to get a recordset, but use the Connection.Execute to return a recordset it will always be forward only, read only. This is good for recordset you don't want to update, like to fill a Dictionary or something. So how do you get a Count From this recordset????
You would have to use a SQL Query with count in it and your criteria.

The RecordCount property is great, but it is different with each Data Access protocol and then can be different depending on how the recordset was derived in that protocol.