B
benatom
Is it possible to create a stored procedure on a SQL Server database
from Access VBA? If so, how?
Thanks.
from Access VBA? If so, how?
Thanks.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
N
Norman Yuan
You can simple open an ADODB.Connection and then execute "Create
Procedure..." SQL statement. Code would look like this:
Dim cn As ADODB.Connection
Set cn=New ADODB.Connection
cn.Open "<connectionString here>"
cn.Execute "CREATE PROCEDURE MySP1 AS......"
cn.Close
Of course the user account running your Access VBA code must have permission
to create SP in the targeting database of the SQL Server.
Procedure..." SQL statement. Code would look like this:
Dim cn As ADODB.Connection
Set cn=New ADODB.Connection
cn.Open "<connectionString here>"
cn.Execute "CREATE PROCEDURE MySP1 AS......"
cn.Close
Of course the user account running your Access VBA code must have permission
to create SP in the targeting database of the SQL Server.
L
Larry Linson
Is it possible to create a stored procedure on a
SQL Server database from Access VBA? If so, how?
It is possible to create a stored procedure, as you've been told. I'd like
to make it clear, however, that SQL Server does not _support or execute_
VBA... it handles the T-SQL language, and the latest versions support the
DotNet languages VB.NET and C#.
Larry Linson
Microsoft Office Access MVP
A
a a r o n . k e m p f
SQL Server doesn't NEED VBA, jackass
now lets start from scratch.
FILE
NEW
PROJECT (EXISTING DATA)
ENTER SERVER NAME
ENTER DB NAME
HIT TEST CONNECTION
HIT OK
HIT THE QUERIES TAB- JUST LIKE IN JET
HIT NEW
CHOOSE EITHER 'DESIGN STORED PROCEDURE' or "CREATE TEXT STORED
PROCEDURE"
doing it through jet is much less practical
now lets start from scratch.
FILE
NEW
PROJECT (EXISTING DATA)
ENTER SERVER NAME
ENTER DB NAME
HIT TEST CONNECTION
HIT OK
HIT THE QUERIES TAB- JUST LIKE IN JET
HIT NEW
CHOOSE EITHER 'DESIGN STORED PROCEDURE' or "CREATE TEXT STORED
PROCEDURE"
doing it through jet is much less practical
L
Larry Linson
Troll elsewhere, Chris.
I'll acknowledge you are an MVP when you show up in the private newsgroup,
and perhaps, if you have learned to be polite, I might even communicate with
you there. Until then, if you reply to one of my posts, expect nothing more
than the first sentence of this post as a response, "Troll elsewhere,
Chris." And, that is more polite than I think you deserve.
Larry Linson
Microsoft Office Access MVP
I'll acknowledge you are an MVP when you show up in the private newsgroup,
and perhaps, if you have learned to be polite, I might even communicate with
you there. Until then, if you reply to one of my posts, expect nothing more
than the first sentence of this post as a response, "Troll elsewhere,
Chris." And, that is more polite than I think you deserve.
Larry Linson
Microsoft Office Access MVP
J
James A. Fortune
Chris said:How many people have you run into who knew how to write a stored procedure
for SQL Server and thought it could use vba code? What state are you in?
Chris
Microsoft MVP
I agree that it was a strange request. What Larry said was correct.
Here's some information about calling .NET functions from T-SQL:
SQL Server, expressions can call code in modules??
http://groups.google.com/group/comp.databases.ms-access/browse_frm/thread/7659aa8da865bac7
More information on managed code UDF's in SQL Server 2005
http://groups.google.com/group/comp.databases.ms-access/browse_frm/thread/c3baeb7fea00a21
James A. Fortune
(e-mail address removed)
P
Please Learn to Read
a a r o n . k e m p f @ g m a i l . c o said:SQL Server doesn't NEED VBA, jackass
The original question was "Is it possible to create a stored procedure on a
SQL Server database from Access VBA? If so, how?" If you are unable to read
and understand that original question, please ask around, because many
welfare and educational organizations offer basic reading courses for the
illiterate. We are sure there must be some in your locale.
2008 Literacy Champions Program
P
posted_by_anonymous
:
"How many people have you run into who knew how
to write a stored procedure for SQL Server and
thought it could use vba code?"
Wal now, it wouldn't take more than one to ask that question, would it,
smartass? Jes what assumptions would you feel safe in making about somebody'd
ask about creating a stored procedure in VBA?
"What state are you in?"
Poster was in a State of Confusion. aaron's always in the State of
Confusion, a State of Denial, and a State of Anger.
Hell's bells, Linson was jes WAY, WAY to polite to you. Reckon he's in a
State of Being Too Nice?
I went to that MVP place and I didn't see you neither. Jes like goin to that
MCP place and nobody seein' aaron, neither. You and ol' aaron in The 2008
Great Pretender Contest, honeybucket?
Anony Mous
"How many people have you run into who knew how
to write a stored procedure for SQL Server and
thought it could use vba code?"
Wal now, it wouldn't take more than one to ask that question, would it,
smartass? Jes what assumptions would you feel safe in making about somebody'd
ask about creating a stored procedure in VBA?
"What state are you in?"
Poster was in a State of Confusion. aaron's always in the State of
Confusion, a State of Denial, and a State of Anger.
Hell's bells, Linson was jes WAY, WAY to polite to you. Reckon he's in a
State of Being Too Nice?
I went to that MVP place and I didn't see you neither. Jes like goin to that
MCP place and nobody seein' aaron, neither. You and ol' aaron in The 2008
Great Pretender Contest, honeybucket?
Anony Mous
C
Chris O''C
Please pardon me for posting too hastily to give the background info. You're
in charge of an Access user group, right? Your post made me think there may
be a training need for those who want to upsize to SQL Server in your area,
and I may have an opportunity to help out if there's enough interest.
(That's why I asked how many you've run into.)
A few days ago I was invited to teach for an Access power user workshop in
Dallas, Texas, (www.datapigtechnologies.com) which I think is in your area.
If you know of a lot of people who need training to upsize to SQL Server and
convert their queries to tsql stored procedures, I could probably swing by
your town to give a free training session at a user group meeting either
before or after the Access workshop. The next Access workshop is in October
and I won't be able to make that one, but the following one will probably be
after the holidays. If you're not in Texas, it would probably be more
difficult to fit in my schedule. (That's why I asked where you are.)
There's no way to post in the private groups from the web yet, which is one
of the reasons Microsoft says only about 1 in 7 MVPs even uses the private
groups. But you can see proof that I'm a Microsoft MVP by the Microsoft MVP
icon next to my name (and Jerry Whittle's and Tom Wickerath's, who you know
are MVPs) from this post and prior posts here.
http://www.microsoft.com/office/com...@uwe&cid=6c953bd0-609e-4d43-bf9f-58d3a67ac392
Stretch out the center column to the right until you see the blue MVP icons
next to our names. Only current MVPs can sign in to Microsoft's site and get
a blue MVP icon next to our names on our posts. If I weren't a Microsoft
MVP, I wouldn't have the blue MVP icon next to my name on this post or any of
the others.
The way you keep treating me is the same way I get treated whenever someone
decides my skin is the wrong color, and that never turns out well. So I'll
just say it's been nice talking to you Mr. Linson and leave it at that.
Chris
Microsoft MVP
in charge of an Access user group, right? Your post made me think there may
be a training need for those who want to upsize to SQL Server in your area,
and I may have an opportunity to help out if there's enough interest.
(That's why I asked how many you've run into.)
A few days ago I was invited to teach for an Access power user workshop in
Dallas, Texas, (www.datapigtechnologies.com) which I think is in your area.
If you know of a lot of people who need training to upsize to SQL Server and
convert their queries to tsql stored procedures, I could probably swing by
your town to give a free training session at a user group meeting either
before or after the Access workshop. The next Access workshop is in October
and I won't be able to make that one, but the following one will probably be
after the holidays. If you're not in Texas, it would probably be more
difficult to fit in my schedule. (That's why I asked where you are.)
There's no way to post in the private groups from the web yet, which is one
of the reasons Microsoft says only about 1 in 7 MVPs even uses the private
groups. But you can see proof that I'm a Microsoft MVP by the Microsoft MVP
icon next to my name (and Jerry Whittle's and Tom Wickerath's, who you know
are MVPs) from this post and prior posts here.
http://www.microsoft.com/office/com...@uwe&cid=6c953bd0-609e-4d43-bf9f-58d3a67ac392
Stretch out the center column to the right until you see the blue MVP icons
next to our names. Only current MVPs can sign in to Microsoft's site and get
a blue MVP icon next to our names on our posts. If I weren't a Microsoft
MVP, I wouldn't have the blue MVP icon next to my name on this post or any of
the others.
The way you keep treating me is the same way I get treated whenever someone
decides my skin is the wrong color, and that never turns out well. So I'll
just say it's been nice talking to you Mr. Linson and leave it at that.
Chris
Microsoft MVP
L
Larry Linson
As far as I know, the only way to see the MVP logo is to use the online
interface, which I normally do not do, because it is far less usable than
reading offline. I have done so, now, to check.
I will no longer insist you visit the private newsgroup to prove your
MVP-ness, will not automatically classify you as a troll, and offer a truce.
I treated you the way I did because you jumped in where you apparently did
not know background and publicly chastised another MVP for responding harshly
to a demonstrated troll -- who had threatened the life and threatened arson
against that very MVP, which troll had been arrested, held in jail, and
pleaded guilty to a lesser charge, and received probation (which he comes
very close to violating on a regular basis); and because I took your response
in this thread to be trolling -- a rhetorical question (and I see I'm not
the only one who seemed to think so).
I am quite certain, if you were interested, you could have visited the
private newsgroup and obtained an e-mail address for that MVP and conducted
your "lecture" privately. I neither know nor care about the color of your
skin, and it would not affect my treatment of you in any way.
Mike, who's putting on the Power User Workshop you mention, was guest
speaker at the most recent meeting of my User Group, the NTPCUG Access SIG,
http://sp.ntpcug.org/accesssig/, which does meet in Dallas. I am not aware
of demand for training to upsize to SQL Server, though you might find some
interested parties at the Power User Workshop. In using Access since Jan.
1993, I have never encountered a power user who was not, at least, interested
in moving on to developer level. There is another Dallas area Access user
group, Metroplex Access Developers, http://www.madtx.org, of which half or
more of the regular attendees have experience creating Access clients to SQL
Server and other server databases, both MDB/MDE and ADP/ADE.
Should you wish to discuss any of these matters privately, you can find a
working e-mail in the private newsgroup (without posting to reveal your
identity). There are other ways, too, of course. And I will not reveal your
identity.
interface, which I normally do not do, because it is far less usable than
reading offline. I have done so, now, to check.
I will no longer insist you visit the private newsgroup to prove your
MVP-ness, will not automatically classify you as a troll, and offer a truce.
I treated you the way I did because you jumped in where you apparently did
not know background and publicly chastised another MVP for responding harshly
to a demonstrated troll -- who had threatened the life and threatened arson
against that very MVP, which troll had been arrested, held in jail, and
pleaded guilty to a lesser charge, and received probation (which he comes
very close to violating on a regular basis); and because I took your response
in this thread to be trolling -- a rhetorical question (and I see I'm not
the only one who seemed to think so).
I am quite certain, if you were interested, you could have visited the
private newsgroup and obtained an e-mail address for that MVP and conducted
your "lecture" privately. I neither know nor care about the color of your
skin, and it would not affect my treatment of you in any way.
Mike, who's putting on the Power User Workshop you mention, was guest
speaker at the most recent meeting of my User Group, the NTPCUG Access SIG,
http://sp.ntpcug.org/accesssig/, which does meet in Dallas. I am not aware
of demand for training to upsize to SQL Server, though you might find some
interested parties at the Power User Workshop. In using Access since Jan.
1993, I have never encountered a power user who was not, at least, interested
in moving on to developer level. There is another Dallas area Access user
group, Metroplex Access Developers, http://www.madtx.org, of which half or
more of the regular attendees have experience creating Access clients to SQL
Server and other server databases, both MDB/MDE and ADP/ADE.
Should you wish to discuss any of these matters privately, you can find a
working e-mail in the private newsgroup (without posting to reveal your
identity). There are other ways, too, of course. And I will not reveal your
identity.
L
Larry Linson
Chris O''C said:If you know of a lot of people who need training to upsize to SQL Server and
convert their queries to tsql stored procedures, I could probably swing by
your town to give a free training session at a user group meeting either
before or after the Access workshop. The next Access workshop is in October
and I won't be able to make that one, but the following one will probably be
after the holidays. If you're not in Texas, it would probably be more
difficult to fit in my schedule. (That's why I asked where you are.)
I'm sure Mike would take a survey at the Power User Workshop, and I could
feel out my users (many of whom are end-users but a few are developers) and
ask at the MAD meeting... if there's enough interest, I'd be happy to have
you speak at my SIG, or maybe we could arrange space for a somewhat longer
presentation, if there's interest and you are still interested. NTPCUG
meetings are on the Third Saturday of each month, and MAD meetings are in
the evening on the Second Tuesday.
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.