SQL-Execute out of Access
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.:)
I just thought using the Command object might help in finding the problem.
Also it was there, so why not use it.:)
ASKER
I do have Microsoft ActiveX Data Objects 6. activated already.
Using mbizup's code, I get an error on cmd.Text ?!
Using mbizup's code, I get an error on cmd.Text ?!
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?
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?
ASKER
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.
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?
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?
And what is the exact error message, if any?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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'
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'
ASKER
thx, yeah, full in the code and didn't think about activation. rgds and nice weekend.
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).