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)
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