Problem with an ado.execute sql to SQL Server from a vba project

[Dave McGuirke-Dempsey]

Hi All,

Can any one help me with a bothersome little problem.

I am tying to execute a rather large SQL via an ADO
connection to a SQL Server from within an MS ACCESS VBA
program using the .Execute (set rs = cn.execute sql)
command. The SQL consists of a couple of Create Tables
(Temporary ones) followed by several Insert's to populate
culminating in a Select statement so as to return records
to a record set. However when executed it bombs out withan
SQL Server (via ODBC) invalid syntax error, citing the
initial Create statement as the possible cause.

The SQL Script is held in a memo field of a Table stored
in the local MS Access database. I have tested the SQL by
copying it directly fom the memo field directly into Query
Analyser and it performs beutifully.

Can any body advise me as I am at my wits end.

Thank you all in anticipation.

Regards
Dave

 

[Perry]

Try this:

replace "Create" statement by
"Alter" statement.

Krgrds,
Perry