Create table dynamically from recordset

[girky]

Hello,
I'm looking to create a table dynamically from a recordset that I created. For example, I have a select query from an external database and have the results in a recordset object. For my project, the fields of this recordset are different based on the different datasources that I am using. From there I need to take that recordset and create a temporary table using its fields and results. I am using many different datasources so I wanted to use 1 function for all of them.

Thanks!

 

[jadams0173]

Could you not make your Select query into a make table query?

I tried to have patience but it took to long! -DW

 

[Eupher]

girky,

If for some reason jadams0173's suggestion will not work for you, I suggest you will need to:

1) add a new tabledef to your database

2) loop through the fields collection of your recordset object(s) to determine the fields and data types for your temp table

3) append the fields to your new tabledef object

There should be examples in the help files for all these steps. Post back if you get stuck.

HTH,

Ken S.

 

[CautionMP]

girky,
To take jadams0173's suggestion to the next step, look at using [tt]DoCmd.RunSQL[/tt] to run a make table query.

sqlMakeTable = "SELECT INTO [i]Your_Temp_Table[/i]" & _ " FROM [i]Source_Dataset[/i];"
DoCmd.RunSQL sqlMakeTable

You could make this into a user defined routine and pass Source_Dataset as an argument then add to the SQL string and create the table.

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[girky]

Hello,
After working on this for a while I actually found another way to do it:

 For i = 0 To (remoterec.Fields.Count - 1)
        localtable.Fields.Append localtable.CreateField(remoterec.Fields(i).Name, remoterec.Fields(i).Type, remoterec.Fields(i).Size)
    Next i
    localdb.TableDefs.Append localtable     ' append table
    If (showProgress = True) Then progressBar 45
    
    Set localrec = localdb.OpenRecordset(strTempTable)
    
    remoterec.MoveFirst
    Do Until remoterec.EOF = True
        localrec.AddNew
        For p = 0 To (remoterec.Fields.Count - 1)
            localrec.Fields(p) = remoterec.Fields(p).Value
        Next p
        localrec.Update
        
        remoterec.MoveNext
    Loop

But thanks for the suggestions!