update query in Access

[tkecowboy]

I am not very experienced in VBA. I've been doing traditional VB development for years. I am trying to perform an update of a database field in Access from within an Access module.

db.Open CurrentProject.Connection
sql = "update tblPatientHistoryNew set probdesc = '" & x & "' where patientID = " & Forms!frmPatientList!txtPatientID & " and refdate = #" & Me.HistDate & "#;"
db.Execute sql

note that x contains a very long Base64 string. I am getting a syntax error on the execute method. The weird thing is that I can print the SQL string in the immediate window, put that exact statement in a new Access query and it runs fine. Is my syntax of the execute method incorrect? Thanks.

Glenn

 

[rstitzel]

try,

docmd.runsql ("update tblPatientHistoryNew set probdesc = '" & x & "' where patientID = " & Forms!frmPatientList!txtPatientID & " and refdate = #" & Me.HistDate & "#;")

 

[tkecowboy]

I am still getting this error:
"syntax error in string in query expression" and the msgbox only lists the base64 string. For some reason, it doesn't like the base64 string variable, but when I plug that exact sql string into a new Access query it runs fine.


This is the sql expression:

update tblPatientHistoryNew set probdesc = 'base64:AKUFHAOAgAQdBMQGxgIDBEgQRTUZFDIIAIAQAgAAFEIzCACADAIAABhCEauq00EeBgyDg5cwAAo5OYL9MfpnEzxk+HtpeZ5EgrQEpDLVctsuAIP4U+F4U4Vy488+Dz61jlrhCkQlcwmJq4SRCLOMUmpACjhEgv4Bw/gHEERzTVhlbLgs0ARI2bBN5aLK0IL8DfgeWSxYKps1JJk9ZvfN871zkZOR5a2zVxywgAo9PYP8LV+FtLuKVOZiYqEI10x05d+/eUJulkokTlKsTRUgg/c91Gq1vc3Emc1OgR4FZ5r3MTM4inAm0kkIkApWiQGC/KH5RAGLEtTc02UsiW5ZYEsubZYs

http://WWWLAESylGjNwJxKSb1SggYTVACC...9pfs+yZVWtVCUChUWgv35++ZYuUtqzUSC/eH7xABYssA=

' where patientID = 6 and refdate = #10/13/2004#;

 

[bodmin]

I guess that the problem is that the base64 string actually exceeds the length that Access will allocate to string variables, I think I remember a limit of 255 characters but someone else will probably correct me on this. It may require splitting the base64 into 2 strings and then concotonating the string together during the update statement.

cheers hope this helps

 

[rstitzel]

You are correct a text field in Access is limited to 255 characters. You would have to create several text fields in the table set to a maximum length of 255 each and then split your description into several parts x, y, z filling each part up to the maximum of 255 characters and then updating each description field in the table. You would have to have a sql statement for each field your updating.

I still don't think you can concatenante them in a query due still to the field size limitation. But you could place them side by side in a report or query.

 

[bodmin]

I think that if you use a memo field with in your table you may then be ableto try conctonating the strings together into the tableas you required.

 

[RoyVidar]

I think one of the problems, is the characters within the string, another mitght be how you're using the connection.

For this to work, I think you could
[tt]currentproject.connection.execute sql
' or
dim cn as adodb.connection
set cn=currentproject.connection
cn.execute sql[/tt]

Put prior to that, I think you'd need to prepare the string. I don't know which of the characters makes trouble, but I'll guess "=" and "+", but you'll need to find that out try "doubling" those characters

[tt]x=replace(x,"=","==")[/tt]

But, to avoid that problem, have a look at chiph's faq on using ADO parameters faq709-1526.

Roy-Vidar