Help ! update recordset from other recordset

[fawaz]

Dear friends,

I have one table in a datbase which will be updated from the table of other database on satisfying some conditions as follows:

Private Sub Command1_Click()
Dim noflds As Variant
Dim fldx As Variant
Dim wrkDefault As Workspace
Set wrkDefault = DBEngine.Workspaces(0)
wrkDefault.BeginTrans
fldx = 0
noflds = Data1.Recordset.Fields.Count
With Data1.Recordset
.MoveFirst
If Not .BOF Then
Do Until .EOF
If ![TRANS_status] > 2 And Not ![REMOTE_POST] Then
.Edit
![REMOTE_POST] = 1
Data2.Recordset.AddNew
For take = 0 To noflds - 1
Data2.Recordset.Fields(take) = Data1.Recordset.Fields(take)
Next take
Data2.Recordset.Update
.Update
End If
.MoveNext
Loop
End If
End With
If MsgBox("Save all changes?", vbYesNo) = vbYes Then
wrkDefault.CommitTrans
Else
wrkDefault.Rollback
End If


when I am running this command I am getting Error message as data type conversion error 3421. Data type are same except differences in Indices. Please need help!

 

[TomB]

Is it possible that some values are Null ?

Tom

 

[fawaz]

I checked it. The problems is my mdb is replica set. It has some fields as s_ (GUID type). I am using query like "Select * from [table_name]" in data control. some time only table name in recordsource.

How I can avoid GUId type in my selection of fields. Becuase typing each field name is error traping.

Please help me.

 

[MichaelRed]

If you know the names of the GUID fields, you can just do a check on the field name in the loop and skip any which are 'auto' in the destination data base

For take = 0 To noflds - 1
If (Data2.Recordset.Fields(Take).Name = "Whatever the realname is" Then
GoTo NoCopy
End If
Data2.Recordset.Fields(take) = Data1.Recordset.Fields(take)
NoCopy:
Next take

MichaelRed
redmsp@erols.com

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

 

[edderic]

if the database field is a date/Time field it can't Null or Empty Eric De Decker
vbg.be@vbgroup.nl

License And Copy Protection AxtiveX.

Download Demo version on my Site:

http://www.createsoftware2000.com