Recordset question

[mns117]

Hi! Thanks in advanced!

I have created a table that contains Metadata (information about other tables in my db)

I did this because I am working on a massive data conversion, and there are many fields in the db that I am working with that have multiple values. Instead of parsing these fields as I find them, heres what Im doing:

Like I said I created this metadata table. In the metadata table I am putting the following:
Table Name
Field Name to be parsed
Key Field
Delimiter

Ok, so heres the challenge, the table name, fieldname, key field, delimiter will be different for each record in this table.

In a function that I am writing I am trying to open a recordset and refer to a recordset row, so that I can insert records in a new table. The problem is, since the field name is always going to be different, I can't refer to the record set.

Here's my code:

Sub Parse_field(New_Tbl, Old_Tbl, Parse_Fld, Key_Fld, Fld_Dlm)
Dim rs2 As ADODB.Recordset
Dim varRecords2 As Variant
Dim sql1 As String
Dim sql2 As String
Dim rsFld As String
Dim rsFld2 As String
Dim pval As String
Dim kval As String



Set rs2 = New ADODB.Recordset

sql1 = "Select " & Parse_Fld & ", " & Key_Fld & " from " & Old_Tbl

rs2.Open sql1, CurrentProject.Connection

rsFld = "rs2!" & Parse_Fld
rsFld2 = "rs2!" & Key_Fld

Do Until rs2.EOF

sql2 = "Insert into " & New_Tbl & " (" & Parse_Fld & "," & Key_Fld & ") Values (" & rsFld & "," & rsFld2 & ")"

rs2.MoveNext
Loop


End Sub


Here's the problem...

for
rsFld = "rs2!" & Parse_Fld

what i want is rsFld to be the value of rs2!Parse_fld

instead I just get the name...

any suggestions.

 

[RoyVidar]

Hi!

To use variables for field names, for instance:

[tt]rs2.Fields(Parse_fld)[/tt]

Roy-Vidar

 

[PHV]

Have you tried this ?
rsFld = rs2(Parse_Fld)

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mns117]

RoyVidar - worked! Thanks!