Update Query from Excel VBA module

[gcutter]

I need to run a query against a SQL DB in Excel so I can use Task Scheduler
and run the Excel File every 6 hours. I created the update query in Access.
SQL Server Name: SQL
Database: Production
Table: Job

Heres the SQL query from Access:
UPDATE Job SET Job.Priority = 0
WHERE (((Job.Job) Like "F*") AND ((Job.Status)="Active") AND
((Job.Priority)>0) AND ((Job.Sched_Start) Is Not Null));

 

[Joel]

Excel should be able to set from the worksheet menu create the query for up
to 3 parameters. when you get to the last menu (the one with finish) select
edit query, then finish. The Query Editor will come up. Press the SQL
button. then edit the SQL to add in your 4 filter.

1) worksheet menu : Data : Import External Data - Import Data - New Database
query

2) Select Access Database and your file.

3) first menu select the columns.

4) 2nd menu set up you filters.

5) go to Finsh menu and proceed as stated above.


Yo can record a macro while performing the query. The Command Text portion
of the query is the SQL.

 

[gcutter]

Nevermind I wrote the Update query on the SQL Server and set it up to run
every morning at 1AM as a Scheduled Job Type T-SQL.

UPDATE Job Set Priority = '0'
From Job
WHERE Status ='Active' and Priority > 0 and Job Like 'F%' and Sched_End Is
Not Null