Excel Query that returns nothing 1

[par60056]

Does anybody know if there is a way to execute a series of sql commands from Excel? Everything that I have found requires a destination and that the query return data.

 

[ide]

first in the project window, Tools/References menu enable
Microsoft Access 9.0 Object Library
and
Microsoft DAO 3.6 Object Library
(office 2000)

in a module call this main sub. The "apAcc.DoCmd.RunSQL strSql" row runs an sql (string) command yielded from a text file with the function (ReadSQLString) bellow. You can also use an sql string stored in a cell (worksheets(1).range("d2&quot.value ...) or other.

You can get a recordset object with openrecordset function.

You must set db (and sql) path..

Sub main()
Dim strSql As String
Dim dbWrk As Database
Dim apAcc As New Access.Application

Dim rstWrk As DAO.Recordset

Set apAcc = CreateObject("Access.Application&quot
Call apAcc.OpenCurrentDatabase("D:\TEMP\dk.mdb", False)
Set dbWrk = apAcc.CurrentDb

'fsql2.sql contains 3 rows; "DELETE * FROM Table2" ...
strSql = ReadSQLString("d:\temp\", "fsql2.sql&quot
apAcc.DoCmd.RunSQL strSql

Set rstWrk = dbWrk.OpenRecordset("SELECT * FROM TABLE1&quot
rstWrk.MoveLast
rstWrk.MoveFirst
MsgBox rstWrk.RecordCount

End Sub

Public Function ReadSQLString(Path As String, FileName As String) As String

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Dim fs, f

Set fs = CreateObject("Scripting.FileSystemObject&quot
Set f = fs.OpenTextFile(Path + FileName, ForReading, TristateFalse)
SQLSTring = ""
Do While f.AtEndOfStream <> True
SQLSTring = SQLSTring + f.ReadLine + &quot; &quot;
'Debug.Print SQLSTring ' Print data to the Immediate window.Loop
Loop
f.Close
ReadSQLString = SQLSTring
End Function

ide

 

[par60056]

Thank you. This got me in the right direction. All I needed to do was reference the DAO class.

Then the following worked.


Dim mydbe As DBEngine
Dim mywks As Workspace
Dim mydb As Database

Set mydbe = New DBEngine
Set mywks = mydbe.CreateWorkspace(&quot;main&quot;, &quot;me&quot;, &quot;&quot;, _
dbUseODBC)
Set mydb = mywks.OpenDatabase(odbcSource, _
dbDriverComplete, False, myConnectString)

' Build big sql statement
'
' Run sql statement
mydb.Execute sqlString
mydb.Close
mywks.Close