Multiple SQL Statements - Access Linked To SQL Server

[Chris Nebinger]

With the database you have linked, you have one of two
possibilities to connect a query to it. One is a normal
Update/Insert query, the other is pass through query.

If you use a pass through query, Access does not look at
the SQL at all, just passes it to SQL Server. As long as
SQL Server accepts the SQL string, then you can execute it.

I don't know which way would be faster (multiple
statements or single statements), but I would think the
single statement with ;'s between the statements would be
faster. I don't know.

Chris Nebinger

-----Original Message-----
Hey all,

I've got an Access 2000 DB linking to a SQL Server 2000

table. I'm using VBA to loop through some Excel sheets and
extract values. I'm going to run a bunch of simple SQL
statements against the SQL server. They will be one of two
kinds -

UPDATE dbo_SQL_SERVER SET MyCol='Foo' WHERE SomeCol='Poo' AND OtherCol='2'

OR

INSERT INTO dbo_SQL_SERVER (MyCol,SomeCol,OtherCol) VALUES ('Poo','Foo','2')

All in all, we're looking at about 600 statements to be run.

In SQL Server (as in the Query Analyzer), I could

seperate each of these statements with a semicolon and
execute the whole lot as one statement. Access doesn't
allow this (as far as I know).

Would SQL Server linked to Access follow the Access rules

for multiple statements, or the SQL Server rules for
multiple statements? If it follows the SQL Server rules,
is it preferable to execute one huge statement or a bunch
of small ones?