sql statements too short 2

[u29gr]

hello, I am trying to execute a query by assigning the statement to the "sql" property of the querydef object.

Unfortunately, these assignment accepts only the 256 last characters, and so I cannot use long statement. Do you know any way to work that out?

Thanks,
Giulio

 

[ErikZ]

Why don't you save the SQL as a query, and the call the query by typing in the name of the query?

 

[GComyn]

to add to ErikZ's post:

Or, use either docmd.runsql, or create the query dynamically.... i.e.,

dim db as dao.database
dim qry as dao.querydef
dim strSQL as string

strsql = [put the sql string here]
set db = currentdb
set qry = db.createquery("",strsql)

qry.execute

...

however, these will only work for action queries... if you want select queries, you will have to create them with names.

The above .createquery("",strsql) will create an 'invisible' query. make sure you close the query and set it to nothing before you finish the function/sub....

i.e., qry.close:set qry = nothing

GComyn

 

[u29gr]

Thanx a lot folks, though I cannot work that out, yet.
In fact, I need to write the query dynamically, as with the code GComyn suggested. But, when the "strsql" string is longer than 255 chars, that results in "strsql" being lest-trimmed to 255 chars. Do you know how to work that out?
Regards,

Giulio

 

[tomschafer]

I have never had this problem my self. But try building the SQL in pieces...

Dim q As QueryDef
dim s As String

Set q = CurrentDb.QueryDefs("QueryName&quot

s="Some 255 Characters"

q.SQL

s="Some More Characters"

q.SQL=q.SQL & s

 

[tomschafer]

Sorry there were a couple typoss...

I have never had this problem my self. But try building the SQL in pieces...

Dim q As QueryDef
dim s As String

Set q = CurrentDb.QueryDefs("QueryName&quot

s = "Some 255 Characters"

q.SQL = s

s = "Some More Characters"

q.SQL = q.SQL & s

 

[u29gr]

Pezamystik,

I really don't understand why, but your suggestion is good and now te code is working!
Thanx a lot,

Giulio

 

[tomschafer]

Because you are building the first 255 characters and then concatenating (adding) on to that. This all happens in this line of code...

q.SQL = q.SQL & s

 

[GComyn]

Here is an example of what I do...

dim strsql as string

strsql = "update......"   
strsql = strsql & "..."
strsql = strsql & "..."

'Do the following until you get all of the sql written.
' I try to keep the string on my screen, so 
'I use '" & _' at the end of the line to 
'tell the computer that I'm continuing this 
'string on the next line.

docmd.runsql strsql,false

'This line will run the sql, with transactions off.

I've got some sql that take up the entire screen, plus some. this runs perfectly (as long as there were no typos...)

To build the sql, I build a query, make sure it runs, then copy the sql into the modules, making the changes necessary to make the sql dynamic (if there was criteria that I want to change each time, that is what is used.

Now.. when you put into the sql a string criteria, you need to watch it, because of all the quotes.... here is an example of a query that I acutally use...:

strSQL = "INSERT INTO [" & strAuditTablename & "] ( RUN_YR_MO, TYPE, ACCOUNT, DBTRNM, ACCTBAL, DTASSGN, " & _
         "LPAYDT, LPAYAT ) " & _
         "SELECT TOP " & strRecordCount & " " & "CLng(Format(Date()," & DblQuote & "yyyymm" & _
         DblQuote & ")) AS RUN_YR_MO, " & DblQuote & "OLD" & DblQuote & " AS TYPE, " & _
         strAgencyTLA & ".ACCOUNT, " & strAgencyTLA & ".DBTRNM, " & strAgencyTLA & ".ACCTBAL, " & _
         strAgencyTLA & ".DTASSGN, " & strAgencyTLA & ".LPAYDT, " & strAgencyTLA & ".LPAYAT " & _
         "FROM (" & strAgencyTLA & " LEFT JOIN [" & strAuditTablename & "] ON " & strAgencyTLA & _
         ".ACCOUNT = [" & strAuditTablename & "].ACCOUNT) LEFT JOIN [" & strAuditArchiveTableName & _
         "] on " & strAgencyTLA & ".account = [" & strAuditArchiveTableName & "].account " & _
         "WHERE (((" & strAgencyTLA & ".DTASSGN) Between DateAdd(" & DblQuote & "d" & DblQuote & _
         ",-180,Date()) And DateAdd(" & DblQuote & "d" & DblQuote & ",-90,Date())) AND (([" & _
         strAuditTablename & "].ACCOUNT) Is Null) AND (([" & strAuditArchiveTableName & _
         "].ACCOUNT) Is Null)) " & _
         "ORDER BY " & strAgencyTLA & ".DBTRNM DESC;"
Set qry = CurrentDb.CreateQueryDef("", strSQL)
qry.Execute

The DblQuote variable is just a constant I declared at the top of the module, like:

Const DblQuote as String = """"

Notice that this time, I created a query. I did this because I was using the status bar, and didn't want the 'blue line of death' to show up, and the qry.execute line will run the query without the line... whereas the docmd.runsql command will show the progress bar.

To check this query segment out, it would probably be better to copy it into a module or word processor, so you can see it without it running to the next line in the middle of a word (which it doesn't in my module.)

I've got some longer queries, just can't find them right now.

I hope this helps....

GComyn