Run an SQL query without MS Access

[Duncan Rounding]

If you want to be able to run an SQL query on an MS Access database but don't have Access , or dont know how to run it in Access then here is a small utility that will do it for you.

Use strictly at your own risk and BACKUP YOUR DATABASE FIRST!

Rename the attached file from .txt to .vbs and then double-click it.
Browse to the database you want and then enter your SQL query.

* Thanks to Fergus and others from which some code snippets are taken.

UPDATE: Additional version uploaded with different folder pick code - as per Fergus' post below.

[Tonyo]

Hi

Appreciate you may not provide support for the script but when I double click I get the warning message 'You Didn't Select a File'.

Why is that.

regards Tony

[fergusw]

Hi

Appreciate you may not provide support for the script but when I double click I get the warning message 'You Didn't Select a File'.

Why is that.

regards Tony

This message only happens if you have not selected the ActinicCatalog.MDB file in the "Choose File" dialog box that pops up.

[Tonyo]

Yes indeed, but the message box pops up before it gives me an option to select the file.

[fergusw]

Try a slight adjustment to the folder/database picking code

'-----------------

'@author    Duncan Rounding - Genesis Technical Ltd - www.genesistechnical.com
'@date        2009/04/22
'@version    0.1
'@history
'0.1    Initial    2009/04/22

'-----------------

'This script will allow entry of a SQL script and execute it on a selected Microsoft Access database.
'Use at your own risk

'-----------------

ON ERROR RESUME NEXT
siteFolder = pickFolder(0)
mydb = siteFolder + "\ActinicCatalog.mdb"

sData = InputBox("Copy the SQL query you want to execute into the input box")

strAutoset = MsgBox("This will update the selected database with the SQL query." & vbcrlf & vbcrlf & "BACKUP THE YOUR DATABASE BEFORE CONTINUING" & vbcrlf & vbcrlf &"Select 'Yes' to go ahead and update the database - wait for the update to finish." & vbcrlf & "Select 'No' to cancel." ,4)
If (strAutoset = vbYes) then
    Set OBJdbConnection = CreateObject("ADODB.Connection")
    OBJdbConnection.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & mydb
    SQLQuery = sData
    Set Result = OBJdbConnection.Execute(SQLQuery)
    OBJdbConnection.Close    
    msgbox("Completed - Database updated")
Else
    msgbox("Aborted - Database not updated")
End If

Function pickFolder(strStartDir)
    Dim SA, F
    Set SA = CreateObject("Shell.Application")
    Set F = SA.BrowseForFolder(0, "Choose the folder that contains the Actinic database you wish to work on.", 0, strStartDir)
    If (Not F Is Nothing) Then
          PickFolder = F.Items.Item.path
    End If
    Set F = Nothing
    Set SA = Nothing
End Function

[Tonyo]

Hi

I think I have just answered my own question.
Having looked at the script again I realised that what I have is different to what you have just shown.

##########################
ON ERROR RESUME NEXT

Set ObjFSO = CreateObject("UserAccounts.CommonDialog")
ObjFSO.Filter = "Access Databases|*.mdb|All Files|*.*"
ObjFSO.FilterIndex = 1
InitFSO = ObjFSO.ShowOpen
If InitFSO = False Then
Wscript.Echo "You didn't select a file!"
Wscript.Quit
End If
##########################

Change this line
If InitFSO = False Then - to equal True

Now it works.

Was the script intended to work this way or is changing that option just a work around.

EDIT -- I tell a lie. No it doesn't work. It is by passing the file selection request.
regards Tony

[fergusw]

did you try my version?

[Tonyo]

Hi Fergus

Thanks for posting your version of the script.
That has fixed it for me.

kind regards Tony