exceladorecordset

Are adChar and adVarChar valid ADO datatypes? Run-time error '3001'


Work has removed MS Access from our PC's!! Therefore I am trying to learn use ADO recordsets on a more standalone basis, and trying the following code:

Dim lcStrSQL            As String
Dim lcObjRcs            As ADODB.Recordset

Sub test_res()
    Set lcObjRcs = New ADODB.Recordset
    lcObjRcs.Fields.Append "Index", adDouble '* Could be any ADO type
    lcObjRcs.Fields.Append "Town", adChar
    lcObjRcs.Fields.Append "State", adChar

    lcObjRcs.Open

'Add your test records here...

    lcObjRcs.AddNew Array("Index", "Town", "State"), Array(1, "Kirkland", "IL")
    lcObjRcs.AddNew Array("Index", "Town", "State"), Array(2, "Colfax", "WI")
    lcObjRcs.AddNew Array("Index", "Town", "State"), Array(3, "Lansing", "IA")

    lcObjRcs.Update
End Sub

The error I get when I try to run this code is

Run-time error '3001':
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another

and the line that gets highlighted (highlit?) is
lcObjRcs.Fields.Append "Town", adChar
The same error occurs when I try adVarChar, and my belief in these as valid datatypes is based on this, from the horse's mouth:

https://learn.microsoft.com/en-us/sql/ado/reference/ado-api/datatypeenum?view=sql-server-ver16

I changed all fields to adDouble (and obv replaced the string values with other integers) and it runs fine.

How do I get strings into my recordset fields?

(MS Office 2021; Excel Version 2409 Build 18025.20140; VBA Version 7.1)


Solution

  • You need to specify record size:

    Sub test_res()
        Set lcObjRcs = New ADODB.Recordset
        lcObjRcs.Fields.Append "Index", adDouble  ' Correct field type for a number
        lcObjRcs.Fields.Append "Town", adVarChar, 50  ' Specify length for text fields
        lcObjRcs.Fields.Append "State", adVarChar, 2  ' Length of 2 for state abbreviations