VBA to split and insert columns

[pkosanam]

Hi,

I have a table in msaccess with the following data

Table1
IDDatabase
ID-1ABC^DB-1^MSSQL^PROD
ID-1XYZ^DB-2^MSSQL^PROD
ID-1DEF^DB-3^MSSQL^PROD
ID-1GHI^DB-4^MSSQL^PROD
ID-2HJIID-2POKILID-2YGTF
ID-2CCML
ID-3HYTD^DB-5^ORACLE^PROD

I am trying to split the data in database column in a new table Table 2 with the following columns

ID, DBName, DBID, DB, ENV

The common delimeter in database column in '^'

I have tried using the split function in side an arrary, but i am not able to get the result values from the split into multiple columns.

Any help would be appreciated.

 

[brumster]

Not sure whether there is a clever way to do this in a simple update query with functions in them, but (personally) I'd probably do it quicker by just programatically doing it with 2 recordsets, reading from your source table, performing the split function on the field as normal and then inserting the corresponding record into the 2nd recordset, putting the appropriate parsed values into the target columns in your target table. Put it in a little VBA function and kick it off as a macro; job done.

 

[Pat Hartman]

Split() separates the data into an array based on the separator character. You then need to loop through the array and place each item in the designated table column.