Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2005
    Posts
    333

    Unanswered: fill a table when you don't know the fileds

    This is related to another post that I had a week or so ago. I would put a link to it but I haven't figured out what happened to the advanced search options.

    I'm trying to create a table that is a composite of two other tables. The tables have related data but has no fields that can be used to join the tables. My attempt here is to open both tables and create the new table one row at a time. I'm pretty close but am having trouble moving data to a field when I don't know the name of the field.

    The code in blue reads the field names from the existing table and creates those fields in the new table. This works. The code in green copies the time stamp from the original table to the new table. This works.

    The code in red has kicked my butt for hours. I know that I don't want to hard code the value point_62 . This was a test to see that if I had a valid filed name that the code woud work. It does. The question is, how woudl I make statement dynamically?

    I've tried things like
    Code:
    rs3!fld.SourceField = fld.Value
    but I get errors. There has to be a way to do this. Any ideas?


    Code:
     
    Private Sub btnGetOldFile_Click()
        Dim db As Database
        Dim rs1 As Recordset
        Dim rs2 As Recordset
        Dim rs3 As Recordset
        Dim ws As Workspace
        Dim tdf As TableDef
        Dim obj As AccessObject
        Dim dbs As Object
        Dim fld As Field
        Dim strSql As String
        Dim temp As String
        
        Set ws = DBEngine.Workspaces(0)
        Set db = ws.Databases(0)
        Set tdf = db.CreateTableDef("DataOut")
        Dim filename As String
        ' Makes call to Open file dialog and returns the path to the
        ' desired database.
        filename = OpenFile
        
        Set dbs = Application.CurrentData
        
        For Each obj In dbs.AllTables
            If obj.Name = "ScaledData" Then
                DoCmd.DeleteObject acTable, "ScaledData"
            End If
            If obj.Name = "RealTimeData" Then
                DoCmd.DeleteObject acTable, "RealTimeData"
            End If
            If obj.Name = "DataOut" Then
                DoCmd.DeleteObject acTable, "DataOut"
            End If
        Next obj
       
        DoCmd.TransferDatabase acImport, "Microsoft Access", _
        filename, acTable, "ScaledData", "ScaledData"
        
        DoCmd.TransferDatabase acImport, "Microsoft Access", _
        filename, acTable, "RealTimeData", "RealTimeData"
        
        Set rs1 = db.OpenRecordset("SELECT * FROM RealTimeData", dbOpenDynaset)
        Set rs2 = db.OpenRecordset("SELECT * FROM ScaledData", dbOpenDynaset)
        
        Set dbs = rs2.Fields
        
        With tdf
            .Fields.Append .CreateField("RealTime", dbText)
            
            For Each fld In dbs
                .Fields.Append .CreateField(fld.SourceField, dbText)
            Next fld
        End With
        
        db.TableDefs.Append tdf
        
        Set rs3 = db.OpenRecordset("SELECT * FROM DataOut")
        
        rs1.MoveFirst
        rs2.MoveFirst
        
        Do While Not rs2.EOF
        
            rs3.AddNew
            rs3![RealTime] = rs1![RealTime]        
            
            For Each fld In dbs            
                rs3![point_62] = fld.Value
              Next fld
            
            rs3.Update
            
            rs1.MoveNext
            rs2.MoveNext
         
        Loop
        
    End Sub

  2. #2
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    It looks like you are moving the data from the two files into your new table where the first two files have been concatenated. So, as you loop through all the fld's in DBS, you also need to loop through all the fields in rs3. The fields in rs3 can be referenced by their index location. So just add one to the index for every loop though the fld's.
    Code:
    Dim x as integer
        x = 1
        For Each fld In dbs            
             rs3.Fields(x) = fld.Value
             x = x + 1
        Next fld
    I have not tested this, but am quite sure it should work. By the way, I would not normally reuse an object variable like "dbs", once as a database, next as a tabledef? I was confused at first until I noticed the second SETting of "dbs".
    HTH,

  3. #3
    Join Date
    Feb 2005
    Posts
    333
    Thanks Vic. That did it. I knew that there was a simple solution.
    I would not normally reuse an object variable like "dbs", once as a database, next as a tabledef?
    I'm bad about this. I type pretty slow so I do a lot of copy and paste during development and then I go back and clean things up after every thing works. At least that's the theory. Chances are, once it works I don't want to mess with it so I leave the bad code in. That's what happens when you have an engineer write software.

  4. #4
    Join Date
    Dec 2005
    Location
    Kalamazoo
    Posts
    484
    Provided Answers: 1
    Quote Originally Posted by campster
    This is related to another post that I had a week or so ago. I would put a link to it but I haven't figured out what happened to the advanced search options.
    ]
    We're still waiting for servers to be provisioned so we can attempt to address the search function fo dBforums.

    You can still access the advanced search options by visiting http://www.dbforums.com/search.php

    You can find all posts by you by visiting your profile and using the Find all posts by campster link. Or you can find all threads started by you by visiting your profile and using the Find all threads started by campster link.
    Have a community? Have questions?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •