Transposing data from one table to another using DAO recordset...

[hunterw]

I am trying to copy some data from one table to another, but not in a simple way. I need to look up an index number, then using that record, cycle through all the fields past field 11, and for every non-null entry, copy both the entry as well as the name of the field in to my new table.

Here's my code:

    Dim dbsNecropsy As DAO.Database
    Dim rstGenotypes As DAO.Recordset
    Dim fld As DAO.Field
    Dim FieldName As String
    Dim Blah As Integer
    
      
    Set dbsNecropsy = CurrentDb
    Set rstGenotypes = dbsNecropsy.OpenRecordset("FacilityTyping", dbOpenTable)
    
    rstGenotypes.Index = "Animal UID"
    rstGenotypes.Seek "=", [Mouse UID]
    
    If rstGenotypes.NoMatch Then
        MsgBox "Genotypes for UID could not be found."
    Else
        [Gender] = rstGenotypes!Sex
        For Blah = 11 To rstGenotypes.Fields.Count - 1
            Set fld = rstGenotypes(Blah)
            Set FieldName = fld.Name
            If IsNull(rstGenotypes!FieldName) Then
                Next
            Else
                ["Gene" & Blah] = rstGenotypes!FieldName
                ["Allele" & Blah] = FieldName
            End If
        Next
    End If

I have no idea if it works, as it throws an error at me for this:
Set FieldName = fld.Name
I have no idea why.
Any and all help, as always, is greatly appreciated!

 

[MarkK]

A bunch of things:
- If you find yourself needing to traverse a bunch of fields like this you probably have a table design issue, like each of these fields should be a single record in a related table. Sounds like, actually, what you are now trying to implement programmatically.
- Since recondsets are tightly bound to a particular data source, like a table, therefore to move data from one table to another you are going to need two recordsets.
- Your error is that you are trying to set the name of a field, which is read-only.
- Avoid 'seek'ing. Open the recordset using a where clause that only returns the records you need.

- Your code'll probably end up looking something like this...

dim dbs as dao.database
dim fld as dao.field
dim rstSrc as dao.recordset
dim rstDst as dao.recordset
dim i as integer

set dbs = currentdb
set rstSrc = dbs.openrecordset( _
  "SELECT ... FROM ... WHERE ... AND ...")
set rstDst = dbs.openrecordset( _
  "SELECT ... FROM ...")
with rstSrc
  do while not .eof
    for i = 11 to .fields.count - 1
      set fld = .fields(i)
      if not isnull(fld.value) then
        rstDst.addnew
        rstDst!FieldName = fld.name
        rstDst!FieldValue = fld.value
        rstDst.Update
      end if
    next
    .movenext
  loop
  .close
end with

Hope this helps,

 

[hunterw]

Oh man, thanks SO much!

Yes, the table's design is poor - unfortunately, it is an automatically generated spreadsheet exported from a closed-source database called Facility, so I cannot alter its layout. All I can do is try and meaningfully extract data from it to use in my database.

Hope this gets me there!

 

[hunterw]

Hmm, having some trouble with the WHERE clause.

Here's the line:

Set rstGenotypes = dbsNecropsy.OpenRecordset("SELECT * FROM FacilityTyping WHERE FacilityTyping.[Animal UID] = " & [Mouse UID], dbOpenTable)

And here's the error:

The Microsoft Jet database engine could not find the object 'SELECT * FROM FacilityTyping WHERE FacilityTyping.[Animal UID] = 10000'. Make sure the object exists and that you spell its name and the path correctly.

I think the problem may stem from the fact that the field name has a space in it, but I'm not sure.

 

[MarkK]

No, you've handled that with the [] brackets, although it's worth avoiding in the future.
You're certain there's a table "FacilityTyping"? You're certain [Animal UID] is a numeric field? You're certain that dbOpenTable is necessary?
I'd look into those first...

 

[hunterw]

dbOpenTable was screwing it up, and apparently was not necessary.

It works!!! Here's what I ended up with:

    Dim dbsNecropsy As DAO.Database
    Dim rstGenotypes As DAO.Recordset
    Dim fld As DAO.Field
    Dim Blah As Integer
    Dim Blah2 As Integer
    
    Blah2 = 0
    Set dbsNecropsy = CurrentDb
    Set rstGenotypes = dbsNecropsy.OpenRecordset("SELECT * FROM FacilityTyping WHERE FacilityTyping.[Animal UID] = " & [Mouse UID])
        With rstGenotypes
        Do While Not .EOF
            For Blah = 11 To .Fields.Count - 1
                Set fld = .Fields(Blah)
                    If Not IsNull(fld.Value) Then
                        Blah2 = Blah2 + 1
                        If Blah2 = 1 Then
                            [Gene1] = fld.Name
                            [Allele1] = fld.Value
                        End If
                        If Blah2 = 2 Then
                            [Gene2] = fld.Name
                            [Allele2] = fld.Value
                        End If
                        If Blah2 = 3 Then
                            [Gene3] = fld.Name
                            [Allele3] = fld.Value
                        End If
                        If Blah2 = 4 Then
                            [Gene4] = fld.Name
                            [Allele4] = fld.Value
                        End If
                        If Blah2 = 5 Then
                            [Gene5] = fld.Name
                            [Allele5] = fld.Value
                        End If
                        If Blah2 = 6 Then
                            [Gene6] = fld.Name
                            [Allele6] = fld.Value
                        End If
                        If Blah2 = 7 Then
                            [Gene7] = fld.Name
                            [Allele7] = fld.Value
                        End If
                        If Blah2 = 8 Then
                            [Gene8] = fld.Name
                            [Allele8] = fld.Value
                        End If
                        If Blah2 = 9 Then
                            [Gene9] = fld.Name
                            [Allele9] = fld.Value
                        End If
                        If Blah2 = 10 Then
                            [Gene10] = fld.Name
                            [Allele10] = fld.Value
                        End If
                        If Blah2 > 10 Then
                            MsgBox "Mouse has more than 10 genotypes - only 10 displayed."
                        End If
                    End If
                Next
            .MoveNext
        Loop
        .Close
    End With

Thank you so much - I really, really appreciate it. I would be lost if it weren't for people like you.

 

[dapfeifer]

I'm pretty sure 'Else If' exists in VBA...maybe you could've considered using that to help condense the code?

Then again, I don't really use VBA all that much and don't remember for sure, but just a suggestion!

 

[hunterw]

Nah, no need. I'd actually like to have used an integer variable inside of the form's variable name, like "[Gene" & var & "]", but that didn't work and I couldn't be arsed to go find the syntax, so I just made it ghetto!