ADO recordset update can't handle field name [Acct #]

[UBfoolin]

ADO recordset update can't handle field name [Acct #] ?

I'm attempting to add a record to a table. My code looks like this (with all the options I've tried):

recordset.AddNew

Options tried:
recordset("Acct #&quot = '123'
recordset.Fields(0) = '123'
recordset.Fields("Acct #&quot = '123'
recordset(0) = '123'

recordset.UpdateBatch

The error msg I get when using any of these options is: The INSERT INTO statement contains the following unknown field name: 'Acct #'.

It appears that the single space & the pound sign character in the field name are bombing ADO. Any way around this other than renaming the field in the database (which I cannot do)? As a test, when I change the field name in the table to [Acct], the insert works perfectly, but that is NOT an alternative open to me.

If I debug this, I can see that the assignment is made correctly & I can see the field name as [Acct #]. It is when the UpdateBatch executes that I get the error message.

Any thoughts?

 

[AdHoc]

It seems to be the space in the column name that causes the problem. You may have to construct your own INSERT INTO statement and use a command object instead of the recordset.
.
.
Dim cmdOb As New adodb.Command
.
.
With cmdOb
.ActiveConnection = cnb
.CommandType = adCmdText
.CommandText = "INSERT INTO FirstTable " _
& "(FirstName, LastName, [Acct #]) " _
& "VALUES ('Craig','Ellis',456);"
.Execute
End With

 

[Parke]

Stay away from variable names with spaces, and symbols like #, $, %. They may look neat but they cause problems.