Solved

SQL-Execute out of Access

Posted on 2011-09-23
12
260 Views
Last Modified: 2012-05-12
I do have following code that doesn't work

Private Sub Form_Close()
Dim c As ADODB.Connection
Dim cmd As ADODB.Command
Dim Srv As Variant
Dim db As Variant
Dim rs As New Recordset

'Server_Info-------------------------------------------------
                    Srv = "7920"
                    db = "K"
'------------------------------------------------------------
Set c = New ADODB.Connection
c.ConnectionString = "Driver={SQL Server};Server=" & Srv & ";Database=" & db & ""
c.Open

c.Execute "DELETE FROM tblStatistikTmp_sb_Land"  
c.Execute "DELETE FROM tblStatistikTmp_sb_Index"
c.Execute "DELETE FROM tblStatistikTmp_sb_Sektor"
c.Execute "DELETE FROM tblStatistikChartTmp"        
c.Execute "DELETE FROM tblStatistikChartTmp_Spot"
c.Close

Set c = Nothing
End Sub

Connecting to the Server works on other issues when I execute a stored procedure and if I run the delete code on the SQL-Server directly, it works as well, no idea why it doesnt work out of the Access?
thx for any help
0
Comment
Question by:Kongta
  • 6
  • 4
  • 2
12 Comments
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 250 total points
ID: 36585891
What is the exact problem you are having with the code -- error messages, etc?

Do you have your references set for ADO?

From the toolbar in the VBA Editor:

Tools --> References -->  {ensure that the reference for "Microsoft ActiveX Data Objects 2.? Library" is checked)
0
 
LVL 33

Assisted Solution

by:Norie
Norie earned 250 total points
ID: 36585912
Why not do something with the Command object?

That's what you should be executing, not the connection.

Set cmd = New ADODB.Command

cmd.Text = "DELETE FROM tblStatistikTmp_sb_Sektor"

cmd.ActiveConnection = c

cmd.Execute
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36585975
<That's what you should be executing, not the connection.>

That's typically what I use as well, but connection.execute is a valid approach (it should work unless there is something else going wrong).
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 33

Expert Comment

by:Norie
ID: 36586060
Agreed, I posted that incorrectly using Execute with a connection should work.

I just thought using the Command object might help in finding the problem.

Also it was there, so why not use it.:)

0
 

Author Comment

by:Kongta
ID: 36586148
I do have Microsoft ActiveX Data Objects 6. activated already.

Using mbizup's code, I get an error on cmd.Text  ?!
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36586170
Kongta,

You're getting us confused.  I haven't suggested any code yet :)

Again - what was the exact issue (or error message) you were getting with the code you originally posted?
0
 

Author Comment

by:Kongta
ID: 36586200
Sorry, I just implemented my code in a blank form an ran it and it worked. Seems not to work in my other form which has a few graphs in it and three subforms, no idea why.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36586227
The code *should* fly syntactically.

Do any of the delete queries work (how about a delete query on a table that is unrelated to the form or subforms)?  Try them one at a time.

What are the recordsources of the form and subforms?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36586235
And what is the exact error message, if any?
0
 

Accepted Solution

by:
Kongta earned 0 total points
ID: 36586262
oh man, how stup... am I? Got it, haven't choosen/activated the procedure in the on the properties, damn

Shall I split the points, is that ok for you?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36586304
You're not stupid - that happens to everyone.  :)

A point split (or a deletion) is fine with me, but if you want to award points, you should click the "Accept and Award Points" button under your own last comment.  That marks your answer as 'accepted' so that people reading this know what the resolution was, and it lets you award some or all of the points at your own discretion to different comments as 'assisted'
0
 

Author Closing Comment

by:Kongta
ID: 36715177
thx, yeah, full in the code and didn't think about activation. rgds and nice weekend.
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Access 2016 - query 23 56
Normalization of a table 19 69
Access Open Report with SQL Parameter 11 28
Batch Export Reports (with multiple parameters) As PDF 2 20
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

815 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now