Running T_SQL scripts form Access

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Merlin - 20 Jun 2006 00:31 GMT

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.

Reply to this Message

Sylvain Lafontaine - 20 Jun 2006 00:45 GMT

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.

Signature

Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

> 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.

Reply to this Message

J Doe - 20 Jun 2006 01:01 GMT

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

> Try with CurrentProject.Connection.Execute
>
[quoted text clipped - 9 lines]
>> to
>> work that way.

Reply to this Message

Merlin - 20 Jun 2006 01:06 GMT

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

> Try with CurrentProject.Connection.Execute
>
[quoted text clipped - 8 lines]
> > I assumed I could just paste it into a Query window, but I can't get it to
> > work that way.

Reply to this Message

Sylvain Lafontaine - 20 Jun 2006 01:30 GMT

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.

Signature

Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

> Thanks, that worked.
> Is there a nicer way though?
[quoted text clipped - 19 lines]
>> > to
>> > work that way.

Reply to this Message

Sylvain Lafontaine - 20 Jun 2006 01:36 GMT

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.

Signature

Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

> 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
[quoted text clipped - 29 lines]
>>> > it to
>>> > work that way.

Reply to this Message

aaron.kempf@gmail.com - 20 Jun 2006 05:49 GMT

J Doe;

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

go play with the mdb newbies kid

> 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
[quoted text clipped - 48 lines]
> >>> > it to
> >>> > work that way.

Reply to this Message

Robert Morley - 20 Jun 2006 13:29 GMT

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

>J Doe;
>
[quoted text clipped - 65 lines]
>> >>> > it to
>> >>> > work that way.

Reply to this Message

aaron.kempf@gmail.com - 20 Jun 2006 16:33 GMT

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

> 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?
[quoted text clipped - 70 lines]
> >> >>> > it to
> >> >>> > work that way.

Reply to this Message

Robert Morley - 21 Jun 2006 00:03 GMT

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

> Robert;
>
[quoted text clipped - 4 lines]
>
> GUESS AGAIN JERK

Reply to this Message

aaron.kempf@gmail.com - 21 Jun 2006 14:22 GMT

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

'oh but you should be using mdb'

f.ck 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.

> 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
[quoted text clipped - 13 lines]
> >
> > GUESS AGAIN JERK

Reply to this Message

nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn - 26 Jul 2006 03:05 GMT

> Robert;
>
[quoted text clipped - 85 lines]
>> >> >>> > it to
>> >> >>> > work that way.

Reply to this Message

Vadim Rapp - 20 Jun 2006 12:24 GMT

> Is there a nicer way though?

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

Vadim Rapp

Reply to this Message

"privatenews" - 22 Jun 2006 07:59 GMT

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.

Reply to this Message

Bonno Hylkema - 21 Jun 2006 10:10 GMT

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

Reply to this Message

aaron.kempf@gmail.com - 21 Jun 2006 14:23 GMT

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

> 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
[quoted text clipped - 29 lines]
> > I assumed I could just paste it into a Query window, but I can't get it to
> > work that way.

Reply to this Message