Convert Rows to Fields

[GigglyGirl]

Hi,

I am trying to figure out how to convert records or rows to fields.
For example if I have a table with certain records

field1
------
A
B
C
D

and I want them to be my fields in a new table, that would look something like this
A B C D

How can I do that in VBA by creating a module?

Thank you

 

[cmmrfrds]

Are you trying to create a new table structure where the values in field1 in an existing table are the column names of the new table?

 

[GigglyGirl]

yupe that is right

 

[cmmrfrds]

I believe that it will be necessary to build the sql dynamically. Kind of an outline.
Dim cn as New ADODB.Connection
set cn = CurrentProject.Connection

Dim ssql as string, ffld as string

The string would need to end up looking something like this.
CREATE TABLE mytable
(
value1 text,
value2 text,
value3 text,
etc...
)

'start the front of the sql string
ssql = "Create Table mytable ( "

' create a loop to loop through the recordset to read each value and put into a variable
ffld = rs!myfield
'concatenate into the string
ssql = ssql & " " & ffld & " text,"
end loop
'strip off last comma
ssql = ssql & &quot"

'execute string to build the table.
cn.Execute ssql

 

[GigglyGirl]

Well Actually I was trying something else.. such as using DAO

But just cannot figure out what is wrong that doesn;t create a new table assign.

Public Function CreateFieldX()
Dim db As DAO.Database
Dim rsMarkers As DAO.Recordset
Dim tdfNew As TableDef
Dim dbText As Object
Set db = CurrentDb()
Set rsMarkers = db.OpenRecordset("SELECT Markers.Marker FROM Markers WHERE (((Markers.Chr)=1));&quot
rsMarkers.MoveFirst
Set tdfNew = db.CreateTableDef("NewTableDef&quot

Do While Not rsMarkers.EOF
With tdfNew
.Fields.Append .CreateField(rsMarkers!Marker, dbText, 50)
End With
rsMarkers.MoveNext
Loop
db.TableDefs.Append tdfNew
End Function

I don;'t know if you have any idea why this is not working. Let me know if you know.

Thanks for your idea, I will try that

 

[cmmrfrds]

I don't think Access will accept a variable in the DAO definition.
.Fields.Append .CreateField(rsMarkers!Marker, dbText, 50)

I don't believe rsMarkers!Marker can be a variable, that is why I suggested the dynamic way. Maybe somebody else knows for sure.

 

[GigglyGirl]

Well actually I figure out.. Thank you any ways

Here is the answer

Public Function CreateFieldX()

Dim db As DAO.Database
Dim rsMarkers As DAO.Recordset
Dim tdfNew As TableDef
' Dim dbText As Object 'NOT NEEDED
Dim fldNewField As DAO.Field

Set db = CurrentDb()
Set rsMarkers = db.OpenRecordset("SELECT Markers.Marker FROM Markers WHERE (((Markers.Chr)=1));&quot

rsMarkers.MoveFirst

Set tdfNew = db.CreateTableDef("NewTableDef&quot

Do While Not rsMarkers.EOF

Set fldNewField = tdfNew.CreateField(rsMarkers!Marker, dbText, 50)
With tdfNew

.Fields.Append fldNewField

End With
rsMarkers.MoveNext

Loop

db.TableDefs.Append tdfNew

End Function

 

[cmmrfrds]

That is good to know. Glad you got it.