I wrote a script that creates a test Access database, then creates a single table called testnum with a NUMBER column. Then several rows are added using several techniques.

1. SQL INSERT... has no problem with large numbers as the data is sent as a string.

2. However, ADO and Addnew() cannot put string data into NUMBER columns. So a negative number is added.

3. The new OLEObjectType() function does not appear to support VT_DOUBLE or Long values, but I tried UI4.

4. Inserting N * N1 ( where the result would be a large integer ) fails.

5. Inserting N then extracting N, then updating the column with N*N1 will insert a large number.

I have never had the need to insert numbers more than 10-digits so any anomalies never appeared. Just curious.

cMDB         = StrCat(dirget(),"test.mdb")
; access 97 - Conn       = "Provider=MicroSoft.Jet.OLEDB.4.0; Data Source=%cMDB%;Jet OLEDB:Engine Type=4"
Conn       = "Provider=MicroSoft.Jet.OLEDB.4.0; Data Source=%cMDB%;"
If FileExist( cMDB ) Then FileDelete( cMDB )
cat          = ObjectOpen("ADOX.Catalog")
cat.Create(Conn)
ObjectClose( cat )

DB = ObjectOpen("ADODB.Connection")
RS = ObjectOpen("ADODB.Recordset")

DB.Open( Conn )
DB.Execute( "CREATE TABLE TESTNUM ( NUM NUMBER );" )

;SQL treats everything as string
DB.Execute( "INSERT INTO TESTNUM ( NUM ) VALUES ( '1234567899' );" )
RS   = ObjectOpen("ADODB.Recordset")
RS.Open("TESTNUM",Conn,2,3,2)

fld       = RS.fields(0)
RS.addnew()
fld.Value = 12345678999  ; will add as negative number
RS.Update()

RS.addnew()
fld.Value = ObjectType("UI4",12345678999) ; should not be possible
RS.Update()

RS.addnew()
fld.Value = 12345678 * 1000 ;another negative
RS.Update()

RS.addnew()
fld.Value = 12345678
RS.Update()
n = fld.value
fld.Value = n*1000   ;now should enter 11-digit value
RS.Update()

ObjectClose(RS)
DB.Close()
ObjectClose(DB)

I guess I just have to test plucking a few 17-18 digit numbers from Excel and inserting them into a database.

This may be one for the 'gotcha' documentation - or how to avoid problems using large numbers with ADO.

My personal preference is to always perform INSERTS, UPDATES and DELETES from SQL rather than ADO code ( like addnew() ), as you can write little ascii .cmd files and execute a line at a time.

;TESTXLNU.WBT
cXLS         = StrCat( dirget(),"test.xls")
oAPP         = ObjectOpen( "Excel.Application" )
oAPP.Visible = @TRUE
oWK          = oAPP.workbooks
oWK.open(cXLS)
oACT         = oAPP.ActiveWorkbook
oWKS         = oAPP.Worksheets
oWS          = oAPP.Worksheets(1)
oWS.Activate
oCell        = oWS.Cells(1,1)
n            = oCell.Value
oCell        = oWS.Cells(2,1)
oCell.Value  = n*100

ObjectClose(oWS)
ObjectClose(oWK)
ObjectClose(oACT)
ObjectClose(oAPP)
Exit

;BIGNUM.WBT
cXML        = StrCat( dirget(), "bignum.xml" )
If FileExist( cXML ) Then FileDelete( cXML )
adUseClient = 3
adDouble    = 5
RS          = ObjectOpen("ADODB.RecordSet")
RS.CursorLocation = adUseClient
flds        = RS.Fields
flds.append("Number",adDouble)
RS.Open()
f1          = RS.Fields("Number")

RS.Addnew()
f1.Value    = 12345678999
RS.Update()

RS.Addnew()
f1.Value    = '12345678999'
RS.Update()

RS.Save( cXML, 1 )
RS.Close()
ObjectClose( RS )
Exit