Run SQL Update via VBA

[jo15765]

I know you can run a SQL select statement via VBA by using the following:

Please Login or Register  to view this content.

To run an update query how would I do it? Just change Select to Update? In the select statement you give the table that you want to update the records in...how would it go with an Update statement?

[alansidman]

You will need to use this format in your SQL statement

Please Login or Register  to view this content.

More information on SQL here

http://www.w3schools.com/sql/sql_update.asp

[jo15765]

Awesome...I thought that was what would be needed, but I wasn't 100% sure. I am going to show you what I am trying to do (and this is probably the most AWFUL way to do it) I have my code now set as:

Please Login or Register  to view this content.

[jo15765]

I have modified my code to be set up like this....It debugs fine, compiles fine, but it's not actually updating the fields in my table! What is going on?

Please Login or Register  to view this content.

[JosephP]

that should not compile since you are passing arguments to functions that don't take any. you also never actually run any SQL commands in it.

[jo15765]

that should not compile since you are passing arguments to functions that don't take any.

Are you referencing me passing upRP from my main module to the two functions? If I place a msgBox in the two functions and ask it to show upRP it will give me the correct table name, so it is passing the data from the Array to the Functions?

you also never actually run any SQL commands in it.

Is the update that I am attempting to run in the function not SQL?

How would I need to re-write this procedure so that it actually executes?

[JosephP]

Your function declaration

Please Login or Register  to view this content.

does not declare any parameters so you cannot pass it arguments. the code you posted will not run. even if it did, the functions do not do anything other than assign some SQL to string variables. they don't actually run it-for that you would need a

Please Login or Register  to view this content.

type of statement.

[jo15765]

Your function declaration

Please Login or Register  to view this content.

does not declare any parameters so you cannot pass it arguments.

Holy cow, IDK how I missed that! I modified the functions to accept the parameter like so:

Please Login or Register  to view this content.

The functions do not do anything other than assign some SQL to string variables. they don't actually run it-for that you would need a

Please Login or Register  to view this content.

type of statement.

Where would I place that line of sql so that the functions execute? Where in my code would I place that?

[jo15765]

I have it functioning properly now...Thank you for pointing the error in my ways. Now I said it is working probably not the most efficient way of coding it. I modified my code to this:

Please Login or Register  to view this content.

[JosephP]

I would just have the function return the SQL to run. I don't really understand why you are passing arguments to the functions if you don't use them?

[jo15765]

I would just have the function return the SQL to run. I don't really understand why you are passing arguments to the functions if you don't use them?

I'm not following what you mean?

[JosephP]

I mean that you pass upRP to the functions but never use it. since the only difference in SQL seems to be number of days, you could have one function and pass that

Please Login or Register  to view this content.

[jo15765]

Oh that is much cleaner and nicer than the monstrous code I was working with! I will definitely use that, thank you