K
KesM
This looks like a bug in Access 2007 / DAO 12
Have tried to run the following but this gives error '3824' INSERY INTO
query cannot contain multi-valued field.
INSERT INTO [;database=C:\Data\DBexchange.accd].MyTable (ID, Field1, Field2,
Field3)
SELECT ID, Field1, Field2, Field3
FROM MyTable
WHERE ID NOT IN (SELECT DISTINCT ID FROM
[;database=C:\Data\DBexchange.accd].MyTable)
So have resorted to using VBA DAO Recordsets
The current code listed below is working fine for all field types except the
fields which are using multi-valued fields.
Am receiving error 'object not available' when doing RS_S("Field1") =
RS_C("Field1")
All four Databases are identical apart from the data.
Table: [Register]
Field: [Field1]
Type: [Text; Lookup ComboBox; Source is Table [Medical Conditions]
Bound to Column 2; Width 0cm,1cm;]
[Medical Conditions] has two fields,
Field 1: [MedCondID] [autonumber] Key
Field 2: [Medical Condition] [Text]
The actual VBA Code part to copy RecordSets is as follows:-
Any suggestions would be grateful.
Have tried to run the following but this gives error '3824' INSERY INTO
query cannot contain multi-valued field.
INSERT INTO [;database=C:\Data\DBexchange.accd].MyTable (ID, Field1, Field2,
Field3)
SELECT ID, Field1, Field2, Field3
FROM MyTable
WHERE ID NOT IN (SELECT DISTINCT ID FROM
[;database=C:\Data\DBexchange.accd].MyTable)
So have resorted to using VBA DAO Recordsets
The current code listed below is working fine for all field types except the
fields which are using multi-valued fields.
Am receiving error 'object not available' when doing RS_S("Field1") =
RS_C("Field1")
All four Databases are identical apart from the data.
Table: [Register]
Field: [Field1]
Type: [Text; Lookup ComboBox; Source is Table [Medical Conditions]
Bound to Column 2; Width 0cm,1cm;]
[Medical Conditions] has two fields,
Field 1: [MedCondID] [autonumber] Key
Field 2: [Medical Condition] [Text]
The actual VBA Code part to copy RecordSets is as follows:-
Code:
Dim dbsOutgoing As Database
Dim strDBName as String
strDBName = "Databases/DBexchange"
Dim RS_C As Recordset2
Dim RS_S As Recordset2
Dim strSQL_C0, strSQL_S1 As String
Dim errLoop As Error
On Error GoTo Err_Execute
DoCmd.Hourglass True
Beep
If (boolDbIsServer = True) Then GoTo Server_Side
Client_Side:
strSQL_C0 = "SELECT * FROM [Register] WHERE ([ReportStatus] = '1');"
Set RS_C = CurrentDb.OpenRecordset(strSQL_C0, , dbOpenDynamic)
'Open up the Reports DataBase
Set dbsOutgoing = OpenDatabase(strDBName)
strSQL_S1 = "SELECT * FROM [Register];"
Set RS_S = dbsOutgoing.OpenRecordset(strSQL_S1, , dbOpenDynamic)
While Not RS_C.EOF
RS_S.AddNew
RS_S("GuardianID") = RS_C("GuardianID")
'Copies over all fields types ok.
'Fails on the following.
RS_S("Field1") = RS_C("Field1")
RS_S.Update
RS_C.Edit
RS_C("ReportStatus") = "0"
RS_C.Update
RS_C.MoveNext
Wend
RS_S.Close
RS_C.Close
DoEvents
'Continues for the other tables..
Server_Side:
'Identical but RS_C = RS_S..
DoEvents
Both_Continue_FromHere:
dbsOutgoing.Close
End_Here:
On Error Resume Next
Set RS1 = Nothing
Set RSo = Nothing
Set RS_C = Nothing
Set RS_S = Nothing
Set dbsOutgoing = Nothing
DoCmd.Hourglass False
Exit Sub
Err_Execute:
' Notify user of any errors that result from
' executing the query.
If DBEngine.Errors.Count > 0 Then
For Each errLoop In DBEngine.Errors
MsgBox "Error number: " & errLoop.Number & vbCr & _
errLoop.Description
Next errLoop
End If
GoTo End_Here
End SubAny suggestions would be grateful.