SQL string problem

I have a form within which I am trying to define a SQL string via a
string variable (I have also tried defining it as several string
variables and concatenating them, likewise as variant type variables).

I do not get any errors when I concatenate the values, so I am not
exceeding the variable's storage limitations.
According to Access specifications in the Help files, it states that
the maximum Number of characters in an SQL statement = approximately
64,000.
My SQL statement contains 4165 characters.

However, when I try to attach the SQL to the form (e.g. using
Me.RecordSource = varSQL1 & varSQL2 & varSQL3) I receive error message
2176 - "The setting for the property is too long. You can enter up to
either 255 or 2,048 characters for this property, depending on the
data type."
The Help file entry for .RecordSource property doesn't mention a
length limitation, which I guess could be an omission.

Am I correct in concluding that it is the .RecordSource property that
is the problem (stating the obvious, I know, but just to clear up my
confusion)?



---

You're never alone with - schizophrenia!

"Gary D." wrote in message
...
I have a form within which I am trying to define a SQL string via a
string variable (I have also tried defining it as several string
variables and concatenating them, likewise as variant type variables).

I do not get any errors when I concatenate the values, so I am not
exceeding the variable's storage limitations.
According to Access specifications in the Help files, it states that
the maximum Number of characters in an SQL statement = approximately
64,000.
My SQL statement contains 4165 characters.

However, when I try to attach the SQL to the form (e.g. using
Me.RecordSource = varSQL1 & varSQL2 & varSQL3) I receive error message
2176 - "The setting for the property is too long. You can enter up to
either 255 or 2,048 characters for this property, depending on the
data type."
The Help file entry for .RecordSource property doesn't mention a
length limitation, which I guess could be an omission.

Am I correct in concluding that it is the .RecordSource property that
is the problem (stating the obvious, I know, but just to clear up my
confusion)?

Yes.

You could modify the SQL of a saved query and then just use the name of the
query as your RecordSource.


--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

On Tue, 1 Jun 2004 14:51:33 -0500, "Rick Brandt"
wrote:

"Gary D." wrote in message
.. .
I have a form within which I am trying to define a SQL string via a
string variable (I have also tried defining it as several string
variables and concatenating them, likewise as variant type variables).

I do not get any errors when I concatenate the values, so I am not
exceeding the variable's storage limitations.
According to Access specifications in the Help files, it states that
the maximum Number of characters in an SQL statement = approximately
64,000.
My SQL statement contains 4165 characters.

However, when I try to attach the SQL to the form (e.g. using
Me.RecordSource = varSQL1 & varSQL2 & varSQL3) I receive error message
2176 - "The setting for the property is too long. You can enter up to
either 255 or 2,048 characters for this property, depending on the
data type."
The Help file entry for .RecordSource property doesn't mention a
length limitation, which I guess could be an omission.

Am I correct in concluding that it is the .RecordSource property that
is the problem (stating the obvious, I know, but just to clear up my
confusion)?

Yes.

You could modify the SQL of a saved query and then just use the name of the
query as your RecordSource.

Yas, thanks. I did have that in palce originally, but I was trying to
hide the SQL from normal viewing, then compile to make an mde front
end (sort of security by obscurity, which I know is not best practice
but it's only a small application).

Thanks anyway.

---

You're never alone with - schizophrenia!

Hi Gary,

In that case, you may be able to use the form's open
event to create a recordset in VBA and then set the forms
recordset equal to it. I don't have any SQL statements as
long as yours to test this with (thank goodness), but it
appears to work with a simple example. Note that I
didn't add any error handlers or anything like that.
Also, you would probably want to close rst in the form's
close event. Following is an example:

Private Sub Form_Open(Cancel As Integer)

Dim StrSQL As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
StrSQL = "Select * FROM tbl"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(StrSQL, dbOpenDynaset)
Set Me.Recordset = rst

End Sub

Hope that helps.

-Ted Allen

-----Original Message-----
On Tue, 1 Jun 2004 14:51:33 -0500, "Rick Brandt"
wrote:

"Gary D." wrote in message
. ..
I have a form within which I am trying to define a
SQL string via a
string variable (I have also tried defining it as
several string
variables and concatenating them, likewise as variant
type variables).

I do not get any errors when I concatenate the
values, so I am not
exceeding the variable's storage limitations.
According to Access specifications in the Help files,
it states that
the maximum Number of characters in an SQL statement
= approximately
64,000.
My SQL statement contains 4165 characters.

However, when I try to attach the SQL to the form
(e.g. using
Me.RecordSource = varSQL1 & varSQL2 & varSQL3) I
receive error message
2176 - "The setting for the property is too long.
You can enter up to
either 255 or 2,048 characters for this property,
depending on the
data type."
The Help file entry for .RecordSource property
doesn't mention a
length limitation, which I guess could be an omission.

Am I correct in concluding that it is
the .RecordSource property that
is the problem (stating the obvious, I know, but just
to clear up my
confusion)?

Yes.

You could modify the SQL of a saved query and then just
use the name of the
query as your RecordSource.

Yas, thanks. I did have that in palce originally, but I
was trying to
hide the SQL from normal viewing, then compile to make
an mde front
end (sort of security by obscurity, which I know is not
best practice
but it's only a small application).

Thanks anyway.

---

You're never alone with - schizophrenia!
.

On Tue, 1 Jun 2004 15:59:04 -0700, "Ted Allen"
wrote:

Hi Gary,

In that case, you may be able to use the form's open
event to create a recordset in VBA and then set the forms
recordset equal to it. I don't have any SQL statements as
long as yours to test this with (thank goodness), but it
appears to work with a simple example. Note that I
didn't add any error handlers or anything like that.
Also, you would probably want to close rst in the form's
close event. Following is an example:

Private Sub Form_Open(Cancel As Integer)

Dim StrSQL As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
StrSQL = "Select * FROM tbl"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(StrSQL, dbOpenDynaset)
Set Me.Recordset = rst

End Sub

Hope that helps.

-Ted Allen

-----Original Message-----
On Tue, 1 Jun 2004 14:51:33 -0500, "Rick Brandt"
wrote:

"Gary D." wrote in message
...
I have a form within which I am trying to define a
SQL string via a
string variable (I have also tried defining it as
several string
variables and concatenating them, likewise as variant
type variables).

I do not get any errors when I concatenate the
values, so I am not
exceeding the variable's storage limitations.
According to Access specifications in the Help files,
it states that
the maximum Number of characters in an SQL statement
= approximately
64,000.
My SQL statement contains 4165 characters.

However, when I try to attach the SQL to the form
(e.g. using
Me.RecordSource = varSQL1 & varSQL2 & varSQL3) I
receive error message
2176 - "The setting for the property is too long.
You can enter up to
either 255 or 2,048 characters for this property,
depending on the
data type."
The Help file entry for .RecordSource property
doesn't mention a
length limitation, which I guess could be an omission.

Am I correct in concluding that it is
the .RecordSource property that
is the problem (stating the obvious, I know, but just
to clear up my
confusion)?

Yes.

You could modify the SQL of a saved query and then just
use the name of the
query as your RecordSource.

Yas, thanks. I did have that in palce originally, but I
was trying to
hide the SQL from normal viewing, then compile to make
an mde front
end (sort of security by obscurity, which I know is not
best practice
but it's only a small application).

Thanks anyway.

---

You're never alone with - schizophrenia!
.


Interesting - I'll give it a try later this week.
Thanks.


---

You're never alone with - schizophrenia!

On Tue, 1 Jun 2004 15:59:04 -0700, "Ted Allen"
wrote:

Hi Gary,

In that case, you may be able to use the form's open
event to create a recordset in VBA and then set the forms
recordset equal to it. I don't have any SQL statements as
long as yours to test this with (thank goodness), but it
appears to work with a simple example. Note that I
didn't add any error handlers or anything like that.
Also, you would probably want to close rst in the form's
close event. Following is an example:

Private Sub Form_Open(Cancel As Integer)

Dim StrSQL As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
StrSQL = "Select * FROM tbl"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(StrSQL, dbOpenDynaset)
Set Me.Recordset = rst

End Sub

Hope that helps.

-Ted Allen

-----Original Message-----
On Tue, 1 Jun 2004 14:51:33 -0500, "Rick Brandt"
wrote:

"Gary D." wrote in message
...
I have a form within which I am trying to define a
SQL string via a
string variable (I have also tried defining it as
several string
variables and concatenating them, likewise as variant
type variables).

I do not get any errors when I concatenate the
values, so I am not
exceeding the variable's storage limitations.
According to Access specifications in the Help files,
it states that
the maximum Number of characters in an SQL statement
= approximately
64,000.
My SQL statement contains 4165 characters.

However, when I try to attach the SQL to the form
(e.g. using
Me.RecordSource = varSQL1 & varSQL2 & varSQL3) I
receive error message
2176 - "The setting for the property is too long.
You can enter up to
either 255 or 2,048 characters for this property,
depending on the
data type."
The Help file entry for .RecordSource property
doesn't mention a
length limitation, which I guess could be an omission.

Am I correct in concluding that it is
the .RecordSource property that
is the problem (stating the obvious, I know, but just
to clear up my
confusion)?

Yes.

You could modify the SQL of a saved query and then just
use the name of the
query as your RecordSource.

Yas, thanks. I did have that in palce originally, but I
was trying to
hide the SQL from normal viewing, then compile to make
an mde front
end (sort of security by obscurity, which I know is not
best practice
but it's only a small application).

Thanks anyway.

---

You're never alone with - schizophrenia!
.


Hi Ted,
Well I tried it but it didn't work.
I've given up with this approach. There is only one defined query
anyway and I keep backups, so if it accidentally gets deleted or
amended then I can restore it easily enough.

For info: the reason the SQL statement is so large is that it combines
data from 7 primary related tables and 3 lookup tables. I could break
it down into one main form with corresponding linked subforms as an
altervative, which I might do at some stage in the future.

Thanks for your suggestion.
Gary



---

You're never alone with - schizophrenia!

Bummer, it had worked when I tried it in a test but maybe
there was something different in your db, or maybe it was
the sql length.

But, you may be better using the saved query anyway since
Access will optimize it, vs the sql query that would not.

-Ted Allen
-----Original Message-----
On Tue, 1 Jun 2004 15:59:04 -0700, "Ted Allen"
wrote:

Hi Gary,

In that case, you may be able to use the form's open
event to create a recordset in VBA and then set the
forms
recordset equal to it. I don't have any SQL statements
as
long as yours to test this with (thank goodness), but
it
appears to work with a simple example. Note that I
didn't add any error handlers or anything like that.
Also, you would probably want to close rst in the
form's
close event. Following is an example:

Private Sub Form_Open(Cancel As Integer)

Dim StrSQL As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
StrSQL = "Select * FROM tbl"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(StrSQL, dbOpenDynaset)
Set Me.Recordset = rst

End Sub

Hope that helps.

-Ted Allen

-----Original Message-----
On Tue, 1 Jun 2004 14:51:33 -0500, "Rick Brandt"
wrote:

"Gary D." wrote in message
m...
I have a form within which I am trying to define a
SQL string via a
string variable (I have also tried defining it as
several string
variables and concatenating them, likewise as
variant
type variables).

I do not get any errors when I concatenate the
values, so I am not
exceeding the variable's storage limitations.
According to Access specifications in the Help
files,
it states that
the maximum Number of characters in an SQL
statement
= approximately
64,000.
My SQL statement contains 4165 characters.

However, when I try to attach the SQL to the form
(e.g. using
Me.RecordSource = varSQL1 & varSQL2 & varSQL3) I
receive error message
2176 - "The setting for the property is too long.
You can enter up to
either 255 or 2,048 characters for this property,
depending on the
data type."
The Help file entry for .RecordSource property
doesn't mention a
length limitation, which I guess could be an
omission.

Am I correct in concluding that it is
the .RecordSource property that
is the problem (stating the obvious, I know, but
just
to clear up my
confusion)?

Yes.

You could modify the SQL of a saved query and then
just
use the name of the
query as your RecordSource.

Yas, thanks. I did have that in palce originally, but
I
was trying to
hide the SQL from normal viewing, then compile to make
an mde front
end (sort of security by obscurity, which I know is
not
best practice
but it's only a small application).

Thanks anyway.

---

You're never alone with - schizophrenia!
.


Hi Ted,
Well I tried it but it didn't work.
I've given up with this approach. There is only one
defined query
anyway and I keep backups, so if it accidentally gets
deleted or
amended then I can restore it easily enough.

For info: the reason the SQL statement is so large is
that it combines
data from 7 primary related tables and 3 lookup tables.
I could break
it down into one main form with corresponding linked
subforms as an
altervative, which I might do at some stage in the
future.

Thanks for your suggestion.
Gary



---

You're never alone with - schizophrenia!
.