How to save a structure to an Access database

Question

I want to create an Access table where some of the fields need to hold structure variables and even another datatable. Is this possible and how would I do it? I can create the in-memory datatable below but do not know how to save/read it to my Access MDB. - (ItemInfo and OrderInfo are structures and BOMDatatable is another datatable):

        dtPackoutPlan = New DataTable
        With dtPackoutPlan
            .Columns.Add("OrderNumber", GetType(String))
            .Columns.Add("LineNumber", GetType(Integer))
            .Columns.Add("ItemNumber", GetType(String))
            .Columns.Add("WorkCenter", GetType(String))
            .Columns.Add("PromisedShipDate", GetType(Date))
            .Columns.Add("PackOutDate", GetType(Date))
            .Columns.Add("DeliveryDate", GetType(Date))
            .Columns.Add("PackOutSequence", GetType(Integer))
            .Columns.Add("Priority", GetType(Integer))
            .Columns.Add("Status", GetType(String))
            .Columns.Add("OrderedQuantity", GetType(Single))
            .Columns.Add("ActualPackOutDate", GetType(Date))
            .Columns.Add("ActualPackOutDateString", GetType(String))
            .Columns.Add("SplitFlag", GetType(String))
            .Columns.Add("ItemInfo", GetType(ItemInfo))
            .Columns.Add("BOMDatatable", GetType(DataTable))
            .Columns.Add("OrderInfo", GetType(OrderInfo))
        End With

You might be able to do such using OLE Object data. But the next question is why? Why not put the ItemInfo and OrderInfo as additional fields or tables? Why do you want to do so much extra coding when Access will do this just fine. — Tony Toews, Jul 18 '11 at 21:20

score 0 · Answer 1 · edited May 23 '17 at 12:04

If you really don't want to save the data into additional fields/tables (see Tony Toews' comment under the question), you could just serialize the whole DataTable to XML or JSON and save that as a string in a memo field.
Of course you can't directly query values from the DataTable then...you'd have to get the serialized string out of the Access table, deserialize it to a DataTable again and get the value from that. Maybe that's a deal-breaker for you.

How to save a structure to an Access database

1 Answers1