Re: [MSAccessProfessionals] UPDATE TABLE issue

Happy to help Sean!  JasonM has a FAQ on the subject here:

http://www.baldyweb.com/BuildSQL.htm

Paul

------ Original Message ------

From: "Sean Cooper via groups.io" <smcjb=yahoo.com@groups.io>

To: "MSAccessProfessionals@groups.io" <msaccessprofessionals@groups.io>

Sent: 12/22/2020 11:44:15 AM

Subject: Re: [MSAccessProfessionals] UPDATE TABLE issue

Thanks Paul. That fixes it. Is there an easy explanation as to why it needed the ' deliminaters?

On Tuesday, December 22, 2020, 01:32:06 PM CST, Paul Baldy <pbaldy@gmail.com> wrote:

The value needs delimiters.  Try

CurrentDb.Execute "UPDATE " & TableToModify & " SET " & NewColName & " = '" & myString & "'"

Paul

------ Original Message ------

From: "Sean Cooper via groups.io" <smcjb=yahoo.com@groups.io>

To: "MSAccessProfessionals@groups.io" <MSAccessProfessionals@groups.io>

Sent: 12/22/2020 11:17:14 AM

Subject: [MSAccessProfessionals] UPDATE TABLE issue

The following works fine

Public Sub MyAddColumn()

Dim TableToModify As String

Dim NewColName As String

Dim mylong As Long

TableToModify = "ABC"

NewColName = "NewCol20"

mylong = 1122

CurrentDb.Execute "ALTER TABLE " & TableToModify & " ADD COLUMN " & NewColName & " Long"

CurrentDb.Execute "UPDATE " & TableToModify & " SET " & NewColName & " = " & mylong

End Sub

But when I try and modify it to update with a string or text instead of a Long I get a Run-Time error 3061, Too few parameters. Expected 1. I've tried changing the ALTER TABLE to CHAR(14), Dim'ing the String with no size all with no luck.

Public Sub MyAddColumn()

Dim TableToModify As String

Dim NewColName As String

Dim myString As String * 14

TableToModify = "ABC"

NewColName = "NewCol20"

myString = "ABCD1234567890

CurrentDb.Execute "ALTER TABLE " & TableToModify & " ADD COLUMN " & NewColName & " TEXT(14)"

CurrentDb.Execute "UPDATE " & TableToModify & " SET " & NewColName & " = " & myString

End Sub

Any help appreciated.

Thanks

Sean