Excel VBA insert array variable into single field in access

Question

In my project I'm trying to save indefinite entry in excel sheet into the database (access). I'm trying to use vba to do so. However, because I don't know how long the entries will be, and I can't create so many fields in the database to fit the unknown amount of entries. And I can't INSERT an array variable into one field in the table. How can I do this?

Say I have below.

Dim SN(100) As String
SN(0)=123    'value of each entry
SN(1)=2412
'so on and so many entries

Then in the SQL statement,

"INSERT INTO [TableName] ([FieldName]) Values (SN)"

It will say RunTime Error "13" Type mismatch.

Is there any way the database field can accept a list or array as data type? So when I call the data out I can use the index to call the variable inside the list?

Parfait · Accepted Answer · 2016-05-25 23:00:38Z

MS Access does not have such a data type, more primitive types like string, boolean, long. But consider joining all items in array using Join into a single string and save that string to database table. Then use Split to pull back into array as needed:

Sub ArrayIntoSQL()

   Dim SN(100) As String, TN() As String
   Dim SNstr As String,

   SN(0) = 123  'value of each entry'
   SN(1) = 2412

   SNstr = Join(SN, "|")   ' PIPE DELIMITER '
   '123|2412|||||||||||||||||||||||||||||||||||||||||||||_
    ||||||||||||||||||||||||||||||||||||||||||||||||||||||'

   strSQL = "INSERT INTO [TableName] ([FieldName]) Values ('" & SNstr & "')"
   ...

End Sub

Sub ArrayOutofSQL()

    Dim SN() As String
    Dim SNstr As String

    strSQL = "SELECT [FieldName] FROM [TableName]"

    ...

    Do While Not rst.EOF 
         SN() = Split(rst!FieldName, "|")      
         rst.MoveNext
    Loop
    ...

End Sub

Because the array items may exceed Access' 255 character limit for short text data type, use the long text type (previously called memo) which does not have a declared limit.

However for best practices, wide tables (many columns) and stored objects like arrays in fields are more expensive than long tables (many rows) and linked objects. So simply save data iteratively:

For Each item in SN
    strSQL = "INSERT INTO [TableName] ([FieldName]) Values (" & item & ")"
    ...
Next item

Of course, save values with an identifier like ID, Person, Date!

Collectives™ on Stack Overflow

Excel VBA insert array variable into single field in access

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related