VB Forum / Database Access / June 2005

The error I'm getting is :  Multiple-Step operation generated errors. Check
each Status value.

Throughout this whole process I get the same error everytime I tryto update
the record set.
I started by trying to access 2 tables joined with a couple fields from
each.
Then went to 1 table.
after lurking some news groups I thought maybe the date format was bad so I
switched to a regular text field in the file.
The status property = 0 until i do the update at which point i get :The
record was not saved because it violates the structure of the underlying
database. (but is is a text field not part of an index)
Here is my code:
' recordset and connection variables
  Dim mrsRecordSet As ADODB.Recordset
  Dim mCnxn        As ADODB.Connection
  Dim msCnxn       As String
  Dim msSQLRequest As String
  Dim msDataPath   As String

Private Sub Form_Load()

   msDataPath = "D:\PRO60\SAMPLEDATA\" '"C:\hold\btrieve\FOXODBC\DO\AP"

  ' Establish and Open connection
  Set mCnxn = New ADODB.Connection
  msCnxn = "Provider=VFPOLEDB.1;Data Source= " & msDataPath &
";Mode=ReadWrite;Password=;Collating Sequence=MACHINE"
  mCnxn.CursorLocation = adUseClient
  mCnxn.Open msCnxn

  ' Declare new record set
  Set mrsRecordSet = New ADODB.Recordset
  ' Set SQL variable
  msSQLRequest = "select somast04.custno, somast04.sodate,sotran04.item
from somast04 inner join sotran04 on somast04.sono = sotran04.sono where
somast04.sono = '      1042'"   ' where gltype = 'A1'"
  ' Open the record set passing the SQL string, connection, cursor type,
file locking type and the type of data access
  mrsRecordSet.Open msSQLRequest, mCnxn, adOpenKeyset, adLockOptimistic,
adCmdText

   'This will update custno on the current record
   mrsRecordSet!custno = "Dann"
   mrsRecordSet.UpdateBatch (adAffectGroup)
   mrsRecordSet.Requery

I have tried using just update and get the same error. I have been trying to
find some useful information about VB frontend accessing Fox tables, but
have not. Would also be willing to use a third party tool to do so if one
existed and was good. ANY help or direction to some information would be
appreciated.

THe main goal of my quest is to be able to slowly replace custom screens
written in FoxPro  that interact with SBT and eventually replace the whole
system  in VB.