creating a table from a ADO recordset easily

[joebickley]

Hi

Is there a simple method to create a table from a recordset without having to do loads of looping etc

thanks joe

 

[tomschafer]

This bit of code should do it. Check the blue for usage.
Note- It won't recreate OLE data.

Sub CreateTableFromRS(strName As String, rs As Recordset)

Dim intFields As Integer
Dim strSQL As String
Dim strValues As String
Dim tblTable As TableDef

If DCount("*", "msysobjects", "name='" & strName & "'&quot > 0 Then CurrentDb.TableDefs.Delete strName

Set tblTable = CurrentDb.CreateTableDef(strName)
strSQL = "insert into " & strName & "("

For intFields = 0 To rs.Fields.Count - 1
If rs.Fields(intFields).Type <> dbLongBinary Then
tblTable.Fields.Append tblTable.CreateField(rs.Fields(intFields).Name, rs.Fields(intFields).Type, rs.Fields(intFields).Size)
strSQL = strSQL & &quot;[&quot; & rs.Fields(intFields).Name & &quot;], &quot;
End If
Next intFields

Mid(strSQL, Len(strSQL) - 1, 1) = &quot&quot;
strSQL = strSQL & &quot; select &quot;

CurrentDb.TableDefs.Append tblTable

While rs.EOF = False
strValues = &quot;&quot;
For intFields = 0 To rs.Fields.Count - 1
If rs.Fields(intFields).Type <> dbLongBinary Then
If Nz(Len(rs(intFields)), 0) = 0 Then
strValues = strValues & &quot;null, &quot;
Else
If rs.Fields(intFields).Type = dbDate Or rs.Fields(intFields).Type = dbTime Then
strValues = strValues & &quot;#&quot; & rs(intFields) & &quot;#, &quot;
ElseIf rs.Fields(intFields).Type = dbMemo Or rs.Fields(intFields).Type = dbText Then
strValues = strValues & &quot;'&quot; & rs(intFields) & &quot;', &quot;
Else
strValues = strValues & rs(intFields) & &quot;, &quot;
End If
End If
End If
Next intFields
Mid(strValues, Len(strValues) - 1, 1) = &quot; &quot;
Debug.Print strSQL & strValues
CurrentDb.Execute strSQL & strValues
rs.MoveNext
Wend

End Sub


Sub Example()

Dim r As Recordset

Set r = CurrentDb.OpenRecordset(&quot;select * from msysobjects&quot

CreateTableFromRS &quot;NewTable&quot;, r

End Sub

 

[tomschafer]

Is this related to the how to your other post on how to get a recordset into a list box???