String Limitation - 255 characters - SQL in Excel Macro

[jayce7]

I'm trying to get either Access or Excel to query an external oracle database
and return the results. The problem is that my query is complex and over 255
characters (ghast!). With VBA supporting only 255 character strings, how am
I supposed to run this query. I still haven't figured out how to do the rest
of the code either (connection, execute, return recordset, store). Any and
all help would save my 12 hrs of pointless searching.

Thanks,
Jason

 

[Karl E. Peterson]

With VBA supporting only 255 character strings,

That's simply not true. Bad premise led to bad conclusion?

 

[jayce7]

Karl,

I'm happy to admit if I'm wrong... but either way, my problem still exists.
I don't know how to execute the query.

Thanks for the help.

- Jason

 

[Karl E. Peterson]

I'm happy to admit if I'm wrong...

Likewise, I hope.

but either way, my problem still exists.
I don't know how to execute the query.

That is a problem, yes. I wish I knew myself, but I've never used Oracle anything.
Best I could do was try to help you at least look where the trouble may lie. It's
definitely not any sort of VBA string length limitation, as they can "theoretically"
range anywhere up to 2Gb in size.

Are you getting any specific error?

Sorry... Karl

 

[jayce7]

Well, in my VBA code, I attempt to store the query so I can pass it as a
string. I've tried declaring:

Dim dbQuery as String
dbQuery = "<my query here>" & _
"<continued etc..>"

And I've also tried declaring it like: Dim dbQuery$

But everytime I try to enter it, I exceed the line continuation limit. To
get around that, I create 5 strings that don't throw the error and then at
the end I try to concatenate them. When I test the code to view in the watch
window, my variables are only storing part of the query - 255 characters a
piece... and the concatenated version looks like only the first query (the
first 255 characters).

Is it just the watch window? Is it really storing all of it but I just
can't view it? I tried to validate that by throwing the concatenated query
into a messagebox, but it too only showed a fragment.

Thanks,
Jason

 

[Karl E. Peterson]

Well, in my VBA code, I attempt to store the query so I can pass it
as a string. I've tried declaring:

Dim dbQuery as String
dbQuery = "<my query here>" & _
"<continued etc..>"

And I've also tried declaring it like: Dim dbQuery$

But everytime I try to enter it, I exceed the line continuation
limit.

Ah, yeah, that's far more restrictive.

To get around that, I create 5 strings that don't throw the
error and then at the end I try to concatenate them. When I test the
code to view in the watch window, my variables are only storing part
of the query - 255 characters a piece... and the concatenated version
looks like only the first query (the first 255 characters).

Is it just the watch window? Is it really storing all of it but I
just can't view it?

Yeah, I think so.

I tried to validate that by throwing the
concatenated query into a messagebox, but it too only showed a
fragment.

Try using Debug.Print with the long string. MsgBox is the debugging tool from hell.