Numeric problem in ADO

[Mats]

This is the problem:

I retrieve one recordset (tmprst) from a db using ADO, then I create another recordset(gridrst) which is a copy of tmprst but with one additinal boolean field. Then I copy the records from tmprst to gridrst. When I come to a numeric field(adnumeric=decimal type on the server) I get the error 'Multiple step operation generated errors. Check each status value'. Fields of all other types, including integer,long integer and double, are copied nicely.

What is going wrong here? I've tried several different approaches but without any luck. (Setting the fieldtype for all fields to advarchar gets the data copied, but I want to keep the field as numeric).

Thanks for any help,
Mats

>>Below is the essential part of the code

'Create fields in GridRst
GridRst.Fields.Append "X", adBoolean
For i = 0 To tmpRst.Fields.Count - 1
GridRst.Fields.Append tmpRst(i).name, tmpRst(i).Type, tmpRst(i).DefinedSize, tmpRst(i).Attributes
Next i

GridRst.Open
'Copy records from tmpRst to GridRst
Do Until tmpRst.EOF
GridRst.AddNew
GridRst(0) = True
For i = 0 To tmpRst.Fields.Count - 1
GridRst(i + 1) = tmpRst(i)
Next i
tmpRst.MoveNext
Loop

 

[MichaelRed]

Mats,

I would probably do this by putting the New field in GridRST after the fields copied from tmpRST, however this shouldn't be a real issue as long as the fuield offset is in place.

What really puzzles me is WHY you would create the GridRST this way at all? Why not just a plain old "Make Table" Query based on tmpRST? and the DEFINED new field "X" = FALSE?

It's not a LOT of code, but almost anything you do in code is gouing to be slowere than a query, so if you're not processing data in some really complex manner, the query route should be both quicker and easier.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[Mats]

Thanks for the input,

I'm not familiar with the 'make table' query (newbie...). Were is the 'table' created in this case, I don't wan't to create anything in the server. I just one to get the records and then cut off the server from my app.

Mats

 

[TerenceOu]

Mats are you trying to say that it doesn't really matter if the field is present in the second recordset since you say you just want to get info and thats it. Because if that is so, you can use disconnected recordsets and not try to copy info from an actively connected recordset to another recordset just so you can obtain information.

I do hope I did not misunderstand.

Cheers

 

[Mats]

I need a new recordset since I need to add the boolean field to the original recordset.

A make-table query might be the answer, but since I'm not familiar with that (other than from MS Acess), I need more info on the possibilities of doing this. I don't want to create anything on the server, all modifications should be done on the client side.

Can anyone point me to some info about using make-table based on a recordset? (MichaelRed??)

Thanks,
Mats

 

[MichaelRed]

Your approach to creating the record set is 'reasonable', depending on the vagaries of the access method you are using.

Som "modification" may be necessary depending on DAO, RDO, or ADO as the method yoy are instantiating in VB. In some of these, you will need to append the TableDef to te collection before it is 'available', while in others in may not matter.

As for the make table query, you can look at the syntax by creating one in your Ms. Access database and viewing the SQL for the query. It basically boils down to:

Select {FieldLIst from Source} Into Destination From {Source Table Name}

A simple example is shown below, Mostly to illustrate the creation of the Boolean field, Initalized to False.

SELECT tblGrade.StuName, tblGrade.Asgn01, tblGrade.Asgn02, False AS MyBool INTO tblGrades_2
FROM tblGrade;

The syntax/structure MAY vary slightly, again depending on the methods you are using (RDO, DAO, ADO).

You should also note that this will only work one time unless you ALSO delete the destination table every time you do this.

An easier? appracch might be to create the table statically, and use delete/append queries to empty the db and re-populate during the process.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[Mats]

Thanks for the input, now I know what my alternatives are.

I'm still curious though to why the copying of decimal fields failed in my original code when all other fields were copied o.k.

Mats