CODEWITHSUNDEEP
vbams-accessms-access-2010
qmckinsey
qmckinsey

Reputation: 305

Preserving Relationships on imported data

I'll start with the background story before explaining the problem with my code. I'm using MS Access 2010. I've been able to import a table of data with two columns. Then I was able to curate the data by adding fields with appropriate values to the imported table. Now, I need to take the curated table and integrate it into my data base. However, I cannot use any of Microsofts built in queries as none of these appear to be able to do what I need. The integration breaks the table apart, yes, but it needs to preserve the relationships of the data in each record.

To this end I've been writing some code in VBA:

Function IntegrateNIRData(curatedTable, queryRecords)

    On Error GoTo Error_Handler
    Dim db As DAO.Database
    Dim rsCuratedTable, rsDBRecords As DAO.Recordset
    Dim iCount As Integer

    Set db = CurrentDb()
    Set rsCuratedTable = db.OpenRecordset(curatedTable, dbOpenTable) 'open the recordset for use (table, Query, SQL Statement)
    Set rsDBRecords = db.OpenRecordset("NIR_Samples_verify", dbOpenDynaset, dbExecDirect, dbOptimisticValue)

    With rsCuratedTable
        If Not (.BOF And .EOF) Then
            Do While Not .EOF
                ' Rest of your code here.
                rsDBRecords.AddNew
                    'Assign Fields here.
                    rsDBRecords![Product Name] = rsCuratedTable![productName]
                    rsDBRecords![Lot Number] = rsCuratedTable![lotNumber]
                    rsDBRecords!counts = rsCuratedTable![counts]
                    rsDBRecords![subsets] = rsCuratedTable![subsets]
                    rsDBRecords![Date Taken] = rsCuratedTable![dateTaken]
                rsDBRecords.Update
                rsDBRecords.Bookmark = rsDBRecords.LastModified
                .MoveNext
            Loop
        End If
    End With

    rsCuratedTable.Close 'Close the recordset
    rsDBRecords.Close 'Close the recordset

Error_Handler_Exit:
    On Error Resume Next
    'Cleanup after ourselves
    Set rs = Nothing
    Set db = Nothing
    Exit Function

Error_Handler:
    MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
    Err.Number & vbCrLf & "Error Source: IntegrateNIRData" & vbCrLf & "Error Description: " & _
    Err.Description, vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit

End Function

The Function hangs on this line, the second OpenRecordset:

Set rsDBRecords = db.OpenRecordset("NIR_Samples_verify", dbOpenDynaset, dbExecDirect, dbOptimisticValue)

To my understanding this may have something to do with Workspaces and the Jet engine not accepting a ms query that spans multiple tables. Of course, I could also be way off. Any advice at this point would be greatly appriciated.

Update:

Several of you have asked similar questions so I felt I should clarify the following:

1) NIR_Samples_verify is an MS access select query that generates a table of records from several of the tables in the database.

2) I keep getting two errors depending on what I set the RecordsetOptionEnum and LockTypeEnum to in the OpenRecordset method. One is Error Number 3027 Database is Read-Only Two is Error Number 3001 Invalid Arguement

3) To my understanding the rest of the code should be fine, it is just the OpenRecordset method that is causing the problem.

Update 2:

I am thinking that maybe access is not capable of doing what I would like. Let me illustrate. If I had two tables both with primary keys and these keys are referenced in a third table that links the two tables causing a many-to-many relationship, then the code would have to not only add the new data to the two tables, but also generate an appropriate record in the third table to maintain the relationship in the data. Hope that makes since. I do appriciate the help and experience.

Update 3:

Have been searching the net and found the following:

From this post it says the query is only updatable when: 1) It is based on a single table. 2) It is based on a query based on a single table. 3) It is based on a query based on tables with a one-to-one relationship.

Upvotes: 1

Views: 1190

Answers (2)

qmckinsey
qmckinsey

Reputation: 305

As it turns out my hunch was correct. The problem had to do with MS Access having updatable and non-updatble queries (See my edits of the question). The main problem was not only does Microsoft not make this information apparent, but there is no master list on their site either. Thank you everyone for the help. Feel free to see this article for more details.

Upvotes: 0

BIBD
BIBD

Reputation: 15414

Not knowing what the contents of NIR_Samples_verify are, I'd be highly suspicious of the dbExecDirect

From the help file

dbExecDirect

"Runs a query by skipping SQLPrepare and directly calling SQLExecDirect (ODBCDirect workspaces only). Use this option only when you’re not opening a Recordset based on a parameter query. For more information, see the "Microsoft ODBC 3.0 Programmer’s Reference." "

I don't see you supplying any parameters.

-- Edit --

Typically I'll open a record set like this

Set rsDBRecords = db.OpenRecordset("select bar from foo where bar > 10", _
                                    dbOpenDynaset, _
                                    dbSeeChanges)

(Especially if I want to alter the data init)

Hopefully that'll move you further in your project.

-- Edit 2 --

It sounds like NIR_Samples_verify is to complicated to be edited. Actually, given that it is a join of multiple tables doing an Add on it doesn't make much sense, and Update MIGHT make sense in some cases.

Your ultimate solution is really going to be doing multiple Adds on multiple record sets (one for each table being referenced in NIR_Samples_verify); much like if you were entering the data into the DB by hand. You add the records that aren't dependant on anything else first (remembering to grab keys to use in the dependant tables).

Upvotes: 1

Related Questions