UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Mismatch Between Multivalued Field Recordsets, When Importing Data From An External Access Database With Vba, Access 2010    
 
   
wolfe
post Jul 26 2019, 04:31 PM
Post#1



Posts: 25
Joined: 14-February 18



I have a database where (due to various circumstances) there are multiple copies without a shared back end, and I now need to import all of the data into one main copy.

This is all working fine for non-multivalued fields, but not yet for the many multivalued fields which are in the database. I'm increasingly aware of the various complications that can stem from using multivalued fields, but unfortunately am at a point where path dependence and time constraints won't allow me to change those or other significant aspects of this database structure.

Hopefully my VBA code for importing the multivalued fields (based on this earlier function) is almost right, but it's not quite there yet. The key steps are below. It is adding values to the intended records and multivalued fields, from the correct set of options but with data that is different to the intended source - for example, with the data below it is assigning people the wrong health conditions.

I think the problem seems most likely to come from how I'm pulling in the data from the external database via the 'strSourceTableDataToImportSQL' variable, as the correct data is pulled in when I change this and set up the code to take the multivalued field values from a table within the main (destination) database. However, the current SQL-based reference to the external database doesn't seem to cause any problems when importing the non-multivalued fields, and also seems fine for opening a recordset.

CODE
  Dim strSourceTable As String
  Dim strDestinationTable As String
  strSourceTable = "tbl7bHealthMain"
  strDestinationTable = "tbl7bHealthMain"

  Dim dbs As Database
  Dim strSourceTableDataToImportSQL As String
  Dim rstSourceTableDataToImport As Recordset

  Dim strProjectIDcode As String
  Dim dblInterviewRound As Double
  Dim strHHIDcode As String
  Dim strHHMemberName As String

  Dim strMultivaluedFieldToImport As String
  strMultivaluedFieldToImport = "DifficultiesFromCurrentLongTermHealthConditions"
  
  Dim rstRecordsInDestinationTableMatchingPrimaryKeyFields As Recordset

  Call SelectFilePath    ' (providing the variable gvntSelectedFilePath)

  ' Open recordset of data to be imported from the source table
  Set dbs = CurrentDb
  strSourceTableDataToImportSQL = "SELECT * FROM [;DATABASE=" & gvntSelectedFilePath & "]." & strSourceTable & ";"
  Set rstSourceTableDataToImport = dbs.OpenRecordset(strSourceTableDataToImportSQL)
  
  ' For each record to be imported from the source table:
  With rstSourceTableDataToImport
    Do Until .EOF
      
      strProjectIDcode = ![ProjectIDcode].Value
      dblInterviewRound = ![InterviewRound].Value
      strHHIDcode = ![HHIDcode].Value
      strHHMemberName = ![HHMemberName].Value
      
      ' Open recordset of the multivalued field values from the source table
      Dim rstSourceMultivaluedFieldValues As Recordset
      Set rstSourceMultivaluedFieldValues = rstSourceTableDataToImport.Fields(strMultivaluedFieldToImport).Value
      
      ' Open recordset of the matching record from the destination table
      Set dbs = CurrentDb
      Set rstRecordsInDestinationTableMatchingPrimaryKeyFields = CurrentDb.OpenRecordset("SELECT * " & _
                            "FROM " & strDestinationTable & " " & _
                            "WHERE " & strDestinationTable & ".ProjectIDcode='" & strProjectIDcode & "' " & _
                            "AND " & strDestinationTable & ".InterviewRound=" & dblInterviewRound & " " & _
                            "AND " & strDestinationTable & ".HHIDcode='" & strHHIDcode & "' " & _
                            "AND " & strDestinationTable & ".HHMemberName='" & strHHMemberName & "'")
      
      With rstRecordsInDestinationTableMatchingPrimaryKeyFields
        
        ' Check that the recordset actually contains rows
        If Not (rstRecordsInDestinationTableMatchingPrimaryKeyFields.EOF And rstRecordsInDestinationTableMatchingPrimaryKeyFields.BOF) Then
          
          Do Until .EOF
            
            ' Open recordset of the multivalued field values from the destination table
            Dim rstDestinationMultivaluedFieldValues As Recordset
            Set rstDestinationMultivaluedFieldValues = rstRecordsInDestinationTableMatchingPrimaryKeyFields.Fields(strMultivaluedFieldToImport).Value
            
            ' Delete any existing multivalued field values in the destination field, to avoid run-time error 3820 (and in case of 'ghost MVF data', etc.?)
            .Edit
            Do Until rstDestinationMultivaluedFieldValues.EOF
              rstDestinationMultivaluedFieldValues.Delete
              rstDestinationMultivaluedFieldValues.MoveNext
            Loop
            .Update
            
            ' Re-open recordset of the multivalued field values from the destination table
            Set rstDestinationMultivaluedFieldValues = rstRecordsInDestinationTableMatchingPrimaryKeyFields.Fields(strMultivaluedFieldToImport).Value
            
            ' **** COPY / PASTE multivalued field values ****
            .Edit
            Do While rstSourceMultivaluedFieldValues.EOF = False
              rstDestinationMultivaluedFieldValues.AddNew
              rstDestinationMultivaluedFieldValues!Value = rstSourceMultivaluedFieldValues!Value
              rstDestinationMultivaluedFieldValues.Update
              rstSourceMultivaluedFieldValues.MoveNext
            Loop
            .Update
            
            .MoveNext
          Loop
        End If
      End With
      
      rstRecordsInDestinationTableMatchingPrimaryKeyFields.Close
      rstSourceMultivaluedFieldValues.Close
      
      .MoveNext
    Loop
  End With



I've also attached two stripped-back sample databases for testing this problem. 'DbToImport' has two people whose details should be added to 'tbl7bHealthMain' of the 'MainDb', by running the 'MVFImportExample' VBA sub in the 'TestingMVFImportForNewInterview' module within 'MainDb'. In the source data from 'DbToImport', person A has mobility difficulties, depression, back pain or neck pain and arthritis, while person B has no health problems. However, when imported to the 'MainDb' in this way, in the 'MainDb' person A is given depression, an 'other common mental health disorder' and diabetes, and person B is given anxiety and depression. (To reset/undo the import, delete from 'tbl7bHealthMain' the two records with 'InterviewRound' 2 and 'HHIDcode' A025.)

It's possible that an earlier problem I had with 'ghost data' (which I more or less resolved by essentially wiping clean all new records when they're added during an interview) might be linked to this, although probably then it would be less important whether the source of the data was external or internal than seems to be the case here, and the part of the code which pre-emptively deletes any existing multivalued field values in the destination field should also avoid it.

Any ideas for getting this fixed would be much appreciated, to help pull all my data together!

Attached File(s)
Attached File  SampleDbsImportingExternalMVF.zip ( 97.78K )Number of downloads: 4
 
Go to the top of the page
 
theDBguy
post Jul 26 2019, 05:44 PM
Post#2


UA Moderator
Posts: 76,910
Joined: 19-June 07
From: SunnySandyEggo


Hi. I’m not in front of a computer now, so I can’t download your files at the moment, but just wanted to offer my demo on MVFs, just in case it helps.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
wolfe
post Jul 26 2019, 09:52 PM
Post#3



Posts: 25
Joined: 14-February 18



Hi, thanks for pointing me in that direction - the code is a lot more streamlined!

I've tried to incorporate the process from your demo in the updated 'MainDb' sample database attached here, but the SQL 'insert into' code for the multivalued field won't execute, with a run-time error 424 (object required). From a MsgBox I added to show the SQL string, it seems like the reference to the external database (when opening the recordset) is being ignored: the interview round value is '1', and if I disable the 'db.Execute' line to see the rest of the loop, it just loops through the multivalued field values which were already in the table before I tried to import the new ones.

Can you see where I'm going wrong, or how else to get it to recognise the external database reference (as the other 'insert into' for importing the non-multivalued fields currently does)?
This post has been edited by wolfe: Jul 26 2019, 10:00 PM
Attached File(s)
Attached File  SampleDbsImportingExternalMVFv2.zip ( 100.81K )Number of downloads: 2
 
Go to the top of the page
 
ADezii
post Jul 27 2019, 09:21 AM
Post#4



Posts: 2,710
Joined: 4-February 07
From: USA, Florida, Delray Beach


I made some significant changes to the Code and I think that I have arrived at a viable solution, but only you can be the judge of that. Rather than go into a prolonged, detailed explanation, I'll simply Upload the Revised DB for you to look at. I hope that it suits your needs.
Attached File(s)
Attached File  MainDb_Revised_1.zip ( 96.26K )Number of downloads: 3
 
Go to the top of the page
 
wolfe
post Jul 27 2019, 08:39 PM
Post#5



Posts: 25
Joined: 14-February 18



That's worked perfectly, thank you! And it was also an unexpected extra benefit to see 'Debug.Print' in action, after the frustration of previously using a MsgBox for clues on where long loops were going wrong and having to keep closing each one with every loop.

A huge relief to have this working at last - thanks again!
This post has been edited by wolfe: Jul 27 2019, 08:42 PM
Go to the top of the page
 
ADezii
post Jul 28 2019, 06:39 AM
Post#6



Posts: 2,710
Joined: 4-February 07
From: USA, Florida, Delray Beach


yw.gif , Good Luck with your Project!
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    15th December 2019 - 03:58 PM