Delete query problem (1 Viewer)

[54uydf]

Hello,
I have a DB I made in Microsoft SQL server, and I connected it to access, and now I'm making the interface for it.
I created a Delete query. I want to put a button in my form that runs this query BUT it's not working! I successfuly done this with selection queries. but when I get the window to chose the qry (when I add new button) the delete query isn't on the list. I clicked on "pass through" icon and chose the DB for this query, then I'm able to select it in the list and add to form, but it gives me errors like "incorrect syntex near * and near dbo_tblx " then I see that the SQL code is changed from this

DELETE FROM dbo_tblx
WHERE (((dbo_tblx.x)=[Enter number:]));

to this

DELETE *
FROM dbo_tblx
WHERE (((dbo_tblx.x)=[Enter number:]));

any advice on how to make that query run from my form??

 

[G81]

one option is to create a stored procedure on your sql database with the delete query and then for the passthrough you'll have "exec sp_storedprocedurename"

That's the proper way of doing it if you have an sql server backend and access as the front.

 

[54uydf]

one option is to create a stored procedure on your sql database with the delete query and then for the passthrough you'll have "exec sp_storedprocedurename"

That's the proper way of doing it if you have an sql server backend and access as the front.

thank you very much for replying, can you please explain how to do that in more detail or direct me to a place where it's described, I'm kinda new in all of this.

 

[G81]

Sure.

How to create a stored procedure:

http://msdn.microsoft.com/en-us/library/ms345415.aspx

SQL Delete Query:

http://www.w3schools.com/Sql/sql_delete.asp

in your example you would have the following to create the stored procedure:

[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff][COLOR=#000000]CREATE PROCEDURE sp_yourdeletequery[/COLOR]
[/COLOR][/SIZE][/COLOR][/SIZE]@x as integer
AS
BEGIN
DELETE FROM tblx
WHERE tblx.x = @x
END
GO

Then you would have to execute the stored procedure with the number you are entering.

e.g. exec sp_yourdeletequery 123456

 

[G81]

I forgot to ask. What version of SQL and Access are you using.

You should be able to use your original method if you'd rather do it that way, as long as you are using compatible versions. From memory I think the access version needs to be the same as or newer than the version of SQL if you want to edit tables.

 

[54uydf]

SQL server 2005 access 2007...I managed to make the stored procedure..I think lol
but I can't find a way to add it to my access...

 

[G81]

a pretty good step by step guide here

http://p2p.wrox.com/access/5197-pass-through-query-input-parmeter.html

quite simple, only a couple small bits of vba to pass through the parameter and once you've done it once, its easy to duplicate for new stored procedures.

Im still not sure why your original delete query isnt working. I tried it on a test database at home and it works perfectly.

 

[G81]

I just had another thought. Does the user which is connecting to the linked tables have write permissions?

trying to understand why:

but when I get the window to chose the qry (when I add new button) the delete query isn't on the list.

 

[54uydf]

the only user at the moment is me..so I believe I have all the permmisions lol
umm, when I make a regular query (no path through), I don't see it on the list. but if I run the qry from the side bar it works fine.
Once I change it to path through, I DO see it on the list, but it gives me errors when I try to run it (even from the side bar)..maybe it's because I put in the code "[]" to get the input..maybe it's not recognized in the sql server..

 

[SOS]

Is this an ADP or just Linked Tables from SQL Server? If linked tables you can do it just like they were Access tables. If an ADP you have to handle it like it was on SQL Server.

 

[54uydf]

I did something called ODBC.. I don't know what ADP is.

but I just tried to run a del qry from a form that is based on a DB I made in access, and still it's not on the list!!! Is there something wrong with my computer? lol..I'm starting to think so!

 

[SOS]

I did something called ODBC.. I don't know what ADP is.

Then you probably linked.

but I just tried to run a del qry from a form that is based on a DB I made in access, and still it's not on the list!!!

Okay, that statement has me totally confused. Not sure at all what you mean there. What List? How are you running the query? What is the query's SQL for it?

 

[54uydf]

Then you probably linked.



Okay, that statement has me totally confused. Not sure at all what you mean there. What List? How are you running the query? What is the query's SQL for it?

what I want is to push a button on a form to run the DELETE qry.
so I made a table, made a Delete qry like I showed in my first post, and then made a form and I'm trying to put the button on it. so I need to chose the function for button, I chose Miscellaneous -> Run Query, and then there should be a list of the queries I have, to connect to the button. only the DELETE queries r not on that list!

 

[G81]

The following might explain why:

http://www.access-programmers.co.uk/forums/showthread.php?t=16389

You could try adding your delete query to a macro and calling that instead. The macro should show up in the wizard regardless of whats included in the macro.

 

[54uydf]

yay I'm not the only person with this problem..
I think I'll realy try to do a macro and be done with it.

Thanks for all the help!!

 

[54uydf]

Ok, if in the future someone has this problem and finds this post DO THIS:

CREATE-> MACRO->macro -> ACTION: OpenQuery, Action Arguments: (chose the qry u made)

save the macro. add the button and make it run this macro.

THAT'S IT! I should've done this hours ago

 

[G81]

great stuff. still look into running stored procedures in the future though if this is going to be used heavily.