Can't update table that use large number data type in VBA

Question

I have enabled large number data type in access , So I can store number that larger than long type.

This is my test code.

Private Sub test()

Dim rs As New ADODB.Recordset

Dim size As LongLong

    rs.Open "Select * From LocalFiles ", _

            CurrentProject.AccessConnection, adOpenKeyset, adLockOptimistic

            

     rs.AddNew

     size = 12345678912345#

    rs("FileSize") = size

    rs.Update  <-- show  error Multiple-step OLE DB operation generated errors

    rs.Close

End Sub

field FileSize is large number in access Database.

When debug to update this field , it show error "Multiple-step OLE DB operation generated errors"

But If I don't assign field rs("FileSize") , it's no error.

I think it becuase OLE DB driver version that I use is not support this data type or not?

Thank

Answer 1

Finally, I found 2 solutions.

1. use Provider OLEDB 12 or OLEDB 16

Note: I found that CurrentProject.AccessConnection use OLEDB 10 Provider

    Private Sub test1()

Dim rs As New ADODB.Recordset

Dim size As LongLong

Dim cn As ADODB.Connection

        Set cn = New ADODB.Connection

        cn.Open "Provider=Microsoft.ACE.OLEDB.16.0;Data Source=d:\data\Make Money\Rapidgator\Rapidgator.accdb;Persist Security Info=False;"

        rs.Open "Select * From LocalFiles ", _

                cn, adOpenKeyset, adLockOptimistic

         rs.AddNew

         

         size = 12345678912345#

        rs("FileSize") = size

        rs.Update

        rs.Close

        cn.Close

End Sub

2. Use ADODB

Private Sub test2()

Dim size As LongLong

Dim rst As DAO.Recordset

         Set rst = CurrentDb.OpenRecordset("Select * From LocalFiles ", dbOpenDynaset)

         size = 12345678912345#

         rst.AddNew

         rst("Size1") = size

         rst.Update

         rst.Close

End Sub

Answer 2

From other's comment in stackoverflow make me wonder that If I use currentproject.connection , It may work. and really. currentproject.connection use OLE D

Private Sub test1()
Dim rs As New ADODB.Recordset
Dim size As LongLong

        rs.Open "Select * From LocalFiles ", _
                CurrentProject.Connection, adOpenKeyset, adLockOptimistic
         rs.AddNew
         size = 12345678912345#
        rs("FileSize") = size
        rs.Update
        rs.Close

End Sub