VBA, DoCmd.RunSQL too long record

Luka
#1 / 6

DoCmd.RunSQL too long record

Hello,
Let's say we have a table with a lot of fields, and field
names are long.
When we want to write an SQL (DoCmd.RunSQL "etc")based on
this table, it can be quite a long record (with no use of
asterix). That long, that code window can not accept it.
How to write "Line Feed"? Please help.

Tue, 21 Jun 2005 17:50:52 GMT

Dirk Goldga
#2 / 6

DoCmd.RunSQL too long record

Quote:

> Hello,
> Let's say we have a table with a lot of fields, and field
> names are long.
> When we want to write an SQL (DoCmd.RunSQL "etc")based on
> this table, it can be quite a long record (with no use of
> asterix). That long, that code window can not accept it.
> How to write "Line Feed"? Please help.

You can break the SQL string up into pieces and concatenate them,
using the line-continuation character "_" to put the whole statement
onto multiple lines, like this:

DoCmd.RunSQL _
"SELECT Field1, Field2, " & _
"Field3, Field4, Field5 " & _
"FROM tblMyTable " & _
"WHERE Field1 = 'SomethingOrOther'"

Is that what you were looking for?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Tue, 21 Jun 2005 18:01:27 GMT

Luka
#3 / 6

DoCmd.RunSQL too long record

Dirk Goldgar,
that is it. Thank you very much.
Luka

Tue, 21 Jun 2005 18:24:40 GMT

Tim Ferguso
#4 / 6

DoCmd.RunSQL too long record

Quote:

> You can break the SQL string up into pieces and concatenate them,
> using the line-continuation character "_" to put the whole statement
> onto multiple lines, like this:

Doesn't db.Execute accept a longer string than DoCmd.RunSQL too?

Tim F

Wed, 22 Jun 2005 04:14:55 GMT

Dirk Goldga
#5 / 6

DoCmd.RunSQL too long record

Quote:

> > You can break the SQL string up into pieces and concatenate them,
> > using the line-continuation character "_" to put the whole
statement
> > onto multiple lines, like this:

> Doesn't db.Execute accept a longer string than DoCmd.RunSQL too?

As I read the (A2K) help file, db.Execute theoretically accepts a
string up to the 2GB maximum string size, while DoCmd.RunSQL is
limited to a "mere" 32KB. Somehow I don't think it should be an
issue.

The RunSQL macro action, on the other hand, is limited to 256
characters for the SQL string. That's probably what raised your
warning flag.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Wed, 22 Jun 2005 04:26:29 GMT

Tim Ferguso
#6 / 6

DoCmd.RunSQL too long record

Quote:

> The RunSQL macro action, on the other hand, is limited to 256
> characters for the SQL string. That's probably what raised your
> warning flag.

Thanks Dirk, that must have been it.

B Wishes

Tim F

Thu, 23 Jun 2005 01:46:28 GMT