SQL string problem

[Gary D.]

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)?

 

[Rick Brandt]

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.

 

[Gary D.]

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.

 

[Ted Allen]

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

 

[Gary D.]

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

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

 

[Gary D.]

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

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

 

[Ted Allen]

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