I am using Access 2000 and have one main table that contains general data and then 3 other tables that hold other specific data related to the main table. The main table use an autonumber field as it's key and this field is linked to each of the other 3 tables as a key. I have a query that simply reads all of these tables into one recordset based on the key (looks like one big file when run). In Access, when I run this query, I am able to add records in the table produced by the query. These records show up properly (with the right key) in each of the specific data tables.
My problem is when I go into my VB6 program and use this query open a recordset and add data (via AddNew/Update methods of recordset), the data is added to each of the fields, but the keyvalue is only set in the main table (the one with the autonumber field). When you look at it, all the data is there, but is not linked becuase the ID field in each sub table is not set to match the autonumber.
I am using ADO connection and recordset and have tried using the query stored in Access directy as the source for the recordset as well as hardcoding the SQL statement into VB neither work. I have relationships for each of the field/tables set up in Access. I have tried it with Enforce Ref Integrity on and off. I have tried many options of join types. I searched thru forums and FAQ's and just can't find anything that talks about this directly. I have to be missing something obvious.
Public Sub SaveRecord()
' Save data stored in the properties to the database
Dim rsTestLog As ADODB.Recordset
Set rsTestLog = New ADODB.Recordset
With rsTestLog
.LockType = adLockOptimistic
.CursorType = adOpenDynamic
.ActiveConnection = mcnnTestLog
.Source = "qryTestLog"
.Open
.AddNew
' The following routine reads values from properties
' and puts them in the recordset -- These values are
' there when I view them in immediate mode
Call UpdateRecSetVals(rsTestLog)
.Update
.Close
End With
Set rsTestLog = Nothing
End Sub
Any help is greatly appreciated.
Thanks
My problem is when I go into my VB6 program and use this query open a recordset and add data (via AddNew/Update methods of recordset), the data is added to each of the fields, but the keyvalue is only set in the main table (the one with the autonumber field). When you look at it, all the data is there, but is not linked becuase the ID field in each sub table is not set to match the autonumber.
I am using ADO connection and recordset and have tried using the query stored in Access directy as the source for the recordset as well as hardcoding the SQL statement into VB neither work. I have relationships for each of the field/tables set up in Access. I have tried it with Enforce Ref Integrity on and off. I have tried many options of join types. I searched thru forums and FAQ's and just can't find anything that talks about this directly. I have to be missing something obvious.
Public Sub SaveRecord()
' Save data stored in the properties to the database
Dim rsTestLog As ADODB.Recordset
Set rsTestLog = New ADODB.Recordset
With rsTestLog
.LockType = adLockOptimistic
.CursorType = adOpenDynamic
.ActiveConnection = mcnnTestLog
.Source = "qryTestLog"
.Open
.AddNew
' The following routine reads values from properties
' and puts them in the recordset -- These values are
' there when I view them in immediate mode
Call UpdateRecSetVals(rsTestLog)
.Update
.Close
End With
Set rsTestLog = Nothing
End Sub
Any help is greatly appreciated.
Thanks
