memo field update transaction

[Souris]

I need update or insert data in to memo field.

Docmd.RunSQL runing perfect, but does support transaction rollback or commit
DAO.QueryDef only accepts parameter up to 255 characters, but support roll
back.

Are there any method that I can update or insert in to memo field and
support rollback commit transactions?


Your information is great appreciated,

 

[Allen Browne]

What happens if you concatenate a literal value into the string, and Execute
it?

This kind of thing (in a transaction):
strSql = "UPDATE Table1 SET MyMemo = 'Put more than 255 char here';"
db.Execute strSql, dbFailOnError

 

[Souris]

Thanks for the message,

I got fewer parameter expected.
I have followign code

Dim ws As DAO.Workspace
Dim db As DAO.Database
Dim bInTrans As Boolean
Dim strMyLongString as string

Set ws = DBEngine(0)
ws.BeginTrans
bInTrans = True
Set db = ws(0)

strMyLongString = " there are more than 255 characters"
SQLString = "INSERT INTO TABLE1 (MyField1) values (strMyLongString)"

db.Execute SQLString, dbFailOnError

I got fewer parameter expected when the code execute db.Execute SQLString,
dbFailOnError.

I am not sure what I am wrong here.

Your information is great appreciated,

 

[Allen Browne]

SQLString = "INSERT INTO TABLE1 (MyField1) values (""" & strMyLongString &
""")"

Explanation of the quotes:
http://allenbrowne.com/casu-17.html

 

[Douglas J. Steele]

If there's a chance that there might be double quotes inside the string
being inserted, use

SQLString = "INSERT INTO TABLE1 (MyField1) values (""" & _
Replace(strMyLongString, """", """""") & """)"

 

[Souris]

Thanks again,

I got "Too fewer parameters, expected 1".

I do not have double quotes in the string.

 

[Souris]

I use msgbox to show SQL string which exactly is

INSERT INTO tblActivityReason
(Employee_ID, ActivityDate, ActivityCode, ActivityComments, Transfer,
ActivityHours, Added_By, [TimeStamp]) values (469, #2/13/2008#, "" +
cmbActivyCode.value + "", "this is a test", 0,0, 469, #2/12/2008 8:53:28AM #)

It works if I use doCmd.RunSQL, but I got "too few parameters, expected 1"
message from Ms Access while the code execute db.execute strMySQL,
dbFailOnError

Your information is great appreciated

 

[Douglas J. Steele]

Are you saying that your SQL actually contains the expression

"" + cmbActivyCode.value + ""

?

The Jet engine doesn't know anything about cmbActivyCode.value: at a bare
minimum, you need Forms![NameOfForm]!cmbActivyCode, although personally I'd
say you're better off resolving it in VBA so that you're simply passing the
value to Jet.

Show all of the code that makes up the SQL string if you're still having
problems.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I use msgbox to show SQL string which exactly is

INSERT INTO tblActivityReason
(Employee_ID, ActivityDate, ActivityCode, ActivityComments, Transfer,
ActivityHours, Added_By, [TimeStamp]) values (469, #2/13/2008#, "" +
cmbActivyCode.value + "", "this is a test", 0,0, 469, #2/12/2008 8:53:28AM
#)

It works if I use doCmd.RunSQL, but I got "too few parameters, expected 1"
message from Ms Access while the code execute db.execute strMySQL,
dbFailOnError

Your information is great appreciated

If there's a chance that there might be double quotes inside the string
being inserted, use

SQLString = "INSERT INTO TABLE1 (MyField1) values (""" & _
Replace(strMyLongString, """", """""") & """)"

 

[Souris]

Thanks again,

I will change my BuildSQL function.
The strSQL build is from collections and build at run time

Does DoCmd.RunSQL through Jet Engine?
It seems runSQL knows cmbActivityCode.value.

Because RunSQL does not support trransaction and rollback, I wanted to
convert my code from RunSQL to db.execute.

Thanks again,

Are you saying that your SQL actually contains the expression

"" + cmbActivyCode.value + ""

?

The Jet engine doesn't know anything about cmbActivyCode.value: at a bare
minimum, you need Forms![NameOfForm]!cmbActivyCode, although personally I'd
say you're better off resolving it in VBA so that you're simply passing the
value to Jet.

Show all of the code that makes up the SQL string if you're still having
problems.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I use msgbox to show SQL string which exactly is

INSERT INTO tblActivityReason
(Employee_ID, ActivityDate, ActivityCode, ActivityComments, Transfer,
ActivityHours, Added_By, [TimeStamp]) values (469, #2/13/2008#, "" +
cmbActivyCode.value + "", "this is a test", 0,0, 469, #2/12/2008 8:53:28AM
#)

It works if I use doCmd.RunSQL, but I got "too few parameters, expected 1"
message from Ms Access while the code execute db.execute strMySQL,
dbFailOnError

Your information is great appreciated

If there's a chance that there might be double quotes inside the string
being inserted, use

SQLString = "INSERT INTO TABLE1 (MyField1) values (""" & _
Replace(strMyLongString, """", """""") & """)"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


SQLString = "INSERT INTO TABLE1 (MyField1) values (""" &
strMyLongString &
""")"

Explanation of the quotes:
http://allenbrowne.com/casu-17.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the message,

I got fewer parameter expected.
I have followign code

Dim ws As DAO.Workspace
Dim db As DAO.Database
Dim bInTrans As Boolean
Dim strMyLongString as string

Set ws = DBEngine(0)
ws.BeginTrans
bInTrans = True
Set db = ws(0)

strMyLongString = " there are more than 255 characters"
SQLString = "INSERT INTO TABLE1 (MyField1) values (strMyLongString)"

db.Execute SQLString, dbFailOnError

 

[Souris]

GetMyValuesClause.Add """"" + cmbActivetycode.Value + """""

This is my code to buid my SQL in collection.

anything I need change to add my collection?

Your help is great appreciated,

Are you saying that your SQL actually contains the expression

"" + cmbActivyCode.value + ""

?

The Jet engine doesn't know anything about cmbActivyCode.value: at a bare
minimum, you need Forms![NameOfForm]!cmbActivyCode, although personally I'd
say you're better off resolving it in VBA so that you're simply passing the
value to Jet.

Show all of the code that makes up the SQL string if you're still having
problems.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I use msgbox to show SQL string which exactly is

INSERT INTO tblActivityReason
(Employee_ID, ActivityDate, ActivityCode, ActivityComments, Transfer,
ActivityHours, Added_By, [TimeStamp]) values (469, #2/13/2008#, "" +
cmbActivyCode.value + "", "this is a test", 0,0, 469, #2/12/2008 8:53:28AM
#)

It works if I use doCmd.RunSQL, but I got "too few parameters, expected 1"
message from Ms Access while the code execute db.execute strMySQL,
dbFailOnError

Your information is great appreciated

If there's a chance that there might be double quotes inside the string
being inserted, use

SQLString = "INSERT INTO TABLE1 (MyField1) values (""" & _
Replace(strMyLongString, """", """""") & """)"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


SQLString = "INSERT INTO TABLE1 (MyField1) values (""" &
strMyLongString &
""")"

Explanation of the quotes:
http://allenbrowne.com/casu-17.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the message,

I got fewer parameter expected.
I have followign code

Dim ws As DAO.Workspace
Dim db As DAO.Database
Dim bInTrans As Boolean
Dim strMyLongString as string

Set ws = DBEngine(0)
ws.BeginTrans
bInTrans = True
Set db = ws(0)

strMyLongString = " there are more than 255 characters"
SQLString = "INSERT INTO TABLE1 (MyField1) values (strMyLongString)"

db.Execute SQLString, dbFailOnError

 

[Souris]

Thanks millions, It works,

Are you saying that your SQL actually contains the expression

"" + cmbActivyCode.value + ""

?

The Jet engine doesn't know anything about cmbActivyCode.value: at a bare
minimum, you need Forms![NameOfForm]!cmbActivyCode, although personally I'd
say you're better off resolving it in VBA so that you're simply passing the
value to Jet.

Show all of the code that makes up the SQL string if you're still having
problems.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I use msgbox to show SQL string which exactly is

INSERT INTO tblActivityReason
(Employee_ID, ActivityDate, ActivityCode, ActivityComments, Transfer,
ActivityHours, Added_By, [TimeStamp]) values (469, #2/13/2008#, "" +
cmbActivyCode.value + "", "this is a test", 0,0, 469, #2/12/2008 8:53:28AM
#)

It works if I use doCmd.RunSQL, but I got "too few parameters, expected 1"
message from Ms Access while the code execute db.execute strMySQL,
dbFailOnError

Your information is great appreciated

If there's a chance that there might be double quotes inside the string
being inserted, use

SQLString = "INSERT INTO TABLE1 (MyField1) values (""" & _
Replace(strMyLongString, """", """""") & """)"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


SQLString = "INSERT INTO TABLE1 (MyField1) values (""" &
strMyLongString &
""")"

Explanation of the quotes:
http://allenbrowne.com/casu-17.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the message,

I got fewer parameter expected.
I have followign code

Dim ws As DAO.Workspace
Dim db As DAO.Database
Dim bInTrans As Boolean
Dim strMyLongString as string

Set ws = DBEngine(0)
ws.BeginTrans
bInTrans = True
Set db = ws(0)

strMyLongString = " there are more than 255 characters"
SQLString = "INSERT INTO TABLE1 (MyField1) values (strMyLongString)"

db.Execute SQLString, dbFailOnError

 

[Linq Adams via AccessMonster.com]

I work in the health care field, and thus use memo fields extensively for
things such as progress notes, treatment notes, etc. When used for their
intended purpose, to store memos or notes, there's seldom a problem. But in
looking over your post , especially your SQL, it strikes me that using a memo
field to hold this type of data is far from best practice! Memo fields should
never, ever be used to store data that will ever need to be sorted, searched
or in any other way manipulated, much less rolled back.

 

[Souris]

Thanks for the information,