General discussion

Locked

Access 2000 - Field Data

By JohnGreer ·
I want to read a record and append that record to another table. However, I want the destination table name to be the data found in a field within the origional record. Is there any way I can use the data in a field as the destination table name?

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Access 2000 - Field Data

by john_wills In reply to Access 2000 - Field Data

Use the destination-nameing field to construct a string containing the name of the destination table. Put this string in a larger string with a SQL INSERT command using the appendend data as constants. DoCmd.RunSQL on that longer string.

Collapse -

Access 2000 - Field Data

by john_wills In reply to Access 2000 - Field Data

Put the SELECT for the table you want to read in a string and use that string as parameter when opening a RecordSet:
Set ReadTable = CurrentDb().OpenRecordset(ReadSQL)
ReadTable.MoveFirst ' MoveNext the next time
DestTableName = ReadTable.Fields(0) 'I here suppose that the destination table name is in the first column; the columns to be copied to the destination are found in their own field positions.

SQLString="INSERT INTO " & DestTableName & "(<list of columns in destination&gt VALUES('" & <first column to be copied> & "','" & <second column>... & ");"
DoCmd SQLString

Make this into a loop.

Collapse -

by JohnGreer In reply to Access 2000 - Field Data

It's been so long I felt I owe you the points.
Sorry, but I didn't try your answer.

Collapse -

Access 2000 - Field Data

by JohnGreer In reply to Access 2000 - Field Data

John,

Can you give me an example?

Collapse -

by JohnGreer In reply to Access 2000 - Field Data

This question was closed by the author

Back to Software Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums