JamesMcS
Keyboard-Chair Interface
- Local time
- Today, 19:24
- Joined
- Sep 7, 2009
- Messages
- 1,819
Morning all! Long winded and annoying one - get yourself a coffee.... 
I've got a table with SQL statements in it. I've got a recordset going to cycle through the records and append querydefs. It works OK on the shorter queries, but some of the statements are really long and it seems as though it's putting CRLFs in when the maximum line size is reached in the query - sometimes halfway through field names and expressions, which is causing errors when appending the querydef to the database. I've confirmed this by copying the SQL statement into Word and showing hidden characters, and there they are....
I've tried using Replace to get rid of the CRLFs, to no avail:
I've also tried writing the queries to text files(they get written without the CRLFs) and using filesystemobject.readline to read them back into a variable, and append it that way, no joy.
The interesting thing is, if I copy and paste the code into an SQL window, the query works OK. So I guess I need to automate this, but how would I copy the statement and paste into an SQL window using code?
I've got a table with SQL statements in it. I've got a recordset going to cycle through the records and append querydefs. It works OK on the shorter queries, but some of the statements are really long and it seems as though it's putting CRLFs in when the maximum line size is reached in the query - sometimes halfway through field names and expressions, which is causing errors when appending the querydef to the database. I've confirmed this by copying the SQL statement into Word and showing hidden characters, and there they are....
I've tried using Replace to get rid of the CRLFs, to no avail:
Code:
Set Qdef = Dbs.CreateQueryDef(Rst!qdef_name, Replace(Rst!qdef_newsql, vbCrLf, ""))The interesting thing is, if I copy and paste the code into an SQL window, the query works OK. So I guess I need to automate this, but how would I copy the statement and paste into an SQL window using code?
