Maximum length of string used in VBA update statement (1 Viewer)

isladogs

MVP / VIP
Local time
Today, 19:55
Joined
Jan 14, 2017
Messages
18,186
Hi

I have created a routine to do a full database SQL search for specified keywords in all database objects.
I then save the SQL / table field names etc into a memo / long text field called SQL in a table (tblDatabaseObjects) for future use i.e. for additional searches

As a small part of this routine, I extract the code from macro designs by:
a) exporting the macro (strObjectName) to a text file
b) reading the text file to a string called strMacroText
c) stripping out all unnecessary characters for my purposes
c) running a SQL update statement to import the edited string into the SQL field

Code:
CurrentDb.Execute "UPDATE tblDatabaseObjects" & _
                        " SET tblDatabaseObjects.SQL = '" & strMacroText & "', tblDatabaseObjects.[SQLDateListed]=Now()" & _
                        " WHERE (((tblDatabaseObjects.ObjectName)='" & strObjectName & "') AND ((tblDatabaseObjects.ObjectType)='Macro'));"

This works perfectly for all except an Autokeys macro which has a string length of about 3200 characters - see attached file.
In that case, nothing is imported.
I've tried editing the query but can only import the first 255 characters

Another file of around 2000 characters 'imports' in full OK.

As far as I can see, there is nothing unusual about the text to be imported except possibly its length.
However as the destination field type is memo/long text, I understand the limit should be about 65000 characters - certainly not 255.

Does anyone here have experience of this situation & a possible solution?

EDIT: by adding dbFailOnError to the above code, I'm getting error 3075 (syntax error - missing operator) on the first section of the text file - see screenshot

I tried stripping out all non alphanumeric characters such as ", {, }, ^ ... etc and still got error 3075
For the life of me, I can't see the missing operator
 

Attachments

  • Autokeys.txt
    6.3 KB · Views: 156
  • Error3075.PNG
    Error3075.PNG
    6.8 KB · Views: 178
Last edited:

isladogs

MVP / VIP
Local time
Today, 19:55
Joined
Jan 14, 2017
Messages
18,186
Hi

Thanks for the link - I'd forgotten this recent thread
I'd already looked at Allen Browne's site but nothing there was relevant.

I'll have a look at the solution MilaK found tomorrow

For info, I've just edited my post to say it may be nothing to do with length of string after all
I'm now getting a syntax error (3075) in the text string I'm trying to import.
However, it looks fine to me
 

isladogs

MVP / VIP
Local time
Today, 19:55
Joined
Jan 14, 2017
Messages
18,186
Solved it by doing it a different way.

Instead of the SQL update statement, I just used recordset code

Code:
   .Edit
   !SQL = Nz(strMacroText, "")
   .Update

The whole string imported without problems

In fact I experimented & have since imported a text file of 13000 characters without problem
 

Users who are viewing this thread

Top Bottom