Running T_SQL scripts form Access

[Merlin]

If I have a TSQL script to create a table (for example), is there some way I
can can I run it against a SQL Server database from Access?
I assumed I could just paste it into a Query window, but I can't get it to
work that way.

 

[Sylvain Lafontaine]

Try with CurrentProject.Connection.Execute

Probably that you can run it from the Immediate Window (also known as the
debug window) but I never tried.

In VBA code, it will be either with the above call or using ADO objects.

 

[J Doe]

Did you try a pass-through query? Or a data definition query?

 

[Merlin]

Thanks, that worked.
Is there a nicer way though?
Scripts can be quite big and I had to put it all on one line in the debug
window.
It also wouldn't accept 'GO', so if there is more than one step you would
have to do each one separately.
Is there no window you can just run scripts from?
Thanks

 

[Sylvain Lafontaine]

Maybe you can try to edit a Query in design Mode, switch to the SQL view and
then use the Run command. You can also put your stuff in a Stored Procedure
and run it.

However, the best way to design your database would be to no use Access.
Enterprise Manager 2000 or its successor SSMS 2005 are much more powerful
client tools (altough some people seems to not be atonished by the speed of
SSMS). You can also find many other tools on the web.

 

[Sylvain Lafontaine]

You could also design your own SQL-Editor: create a form with a text box and
a command button. At the click of the button, the text will then be sent to
the SQL-Server.

 

[aaron.kempf]

J Doe;

he is talking about SQL Server; not a kids' database

go play with the mdb newbies kid

 

[Vadim Rapp]

Is there a nicer way though?

create new stored procedure, paste the text, run, delete stored procedure.

Vadim Rapp

 

[Robert Morley]

Is there some reason you feel the deep-seated need to insult anybody who's
not using exactly what you seem to think they should be using?


Rob

 

[aaron.kempf]

Robert;

because he is asking for help about ADP and these dipsticks don't know
ADP from the back of their hand.

'uh a passthrough query'

GUESS AGAIN JERK

 

[Robert Morley]

Yeah, I spotted the fact that he was confused as to the type of front-end,
but that doesn't mean you have to go around calling him names. Had it been
this once, I would've just assumed you were having a bad day, but the
reality is, you do this sort of thing ALL THE TIME. What is it that makes
it impossible for you to just say nothing like the rest of us do...do you
REALLY think you're that much better than everybody else?


Rob

 

[Bonno Hylkema]

The discussion gets a bit complicated. I use the following function in my
Access adp when needed. The parameter ExecSPCommand is the required T-SQL
script.

Public Function ExecStoredProcedure(ExecSPCommand) As Long

Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command

Set cnn = CurrentProject.Connection
Set cmd = New ADODB.Command

With cmd
.ActiveConnection = cnn
.CommandText = ExecSPCommand
.CommandType = adCmdText
.Execute
End With

cnn.Close

Set cmd = Nothing
Set cnn = Nothing

This is in fact the method Sylvain LaFontaine proposed.

Regards, Bonno Hylkema

 

[aaron.kempf]

I do this sort of thing ALL THE TIME because YOU MDB ASSHOLES always
spread mins-information about ADP.

'oh but you should be using mdb'

**** you and don't ever try to steal a adp convert ever again

mdb is dead and you obsolete fucktards can screw yourself

always trying to convince people that 'adp is going away'

it's not adp that is going away-- mdb and adp are going away. Right?

in favor of accdb?

but access 2007 creates adp; so I am not going to give up a single ADP
user. just because you guys 'accidently' try to sell him on mdb time
after time after time

you mdb idiots should go back to school and learn a real db engine.
Access Data Projects made MDB obsolete 6 years ago.

 

[aaron.kempf]

i think that sometimes you need to be sure to use a higher
commandTimeout
and sometimes even your own connection right?

i always have timeouts i can't get around unless i toy with a 2nd
connection occassionaly.

i use adp for some etl prototypes though

-aaron

 

[privatenews]

Hello

I think Vadim's suggest might meet your requirment best. You could create
new stored procedure, switch to "SQL View", paste the text, save the SP,
run and then delete stored procedure.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================



This posting is provided "AS IS" with no warranties, and confers no rights.

 

[nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn]

Robert;

because he is asking for help about ADP and these dipsticks don't know
ADP from the back of their hand.

'uh a passthrough query'

GUESS AGAIN JERK