How to issue direct SQL commands from VB

If you are using ADO, you have a connection object. You can then do:

cn.execute "Delete From TableName"

Not quite sure I was clear...
I have a .SQL file (many SQL commands which delete tables and create them etc.)
How do I invoke that .SQL file from VB?

I do this regularly for a data mining application. There are actually two possiblities you may consider, both of which are illustrated here

Private Sub MySQLCommand()
 'Create a command object and a connection object

 Dim cmd as new adodb.command
 Dim mConnection as new adodb.connection

 'Now make a connection to your database. I will
 'assume you will be using the JET odbc driver and
 'a MS access database.

 mConnection.ConnectionString= & _
      "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Persist Security Info=False;Data Source=" & _  
      "C:\My Documents\AnyAccess.mdb"
 mConnection.open

 'Now assign the connection object to your command object

 cmd.ActiveConnection=mConnection

 'You can use the execute method of the command object to
 'run some SQL. Lets assume I have view of the data
 'called  MyView that I want to remove.

 cmd.CommandText = "DROP VIEW MyView"
 cmd.Execute
   
 'If you want to build an SQL query to extract records,
 'you can extend this process a bit by going to an ADO
 'recordset.

 dim MyRst as new adodb.recordset
 dim strSQL as string

 strSQL="Select Field1, Field2, Field3 from Table1 " & _
       "Where Field1=1 "

 MyRst.Open _
        strsql,mConnection,adopendynamic,adlockoptimistic

 'MyRst now contains the selected data.  I use the strSQL
 'to build the SQL prior to running into the open method
 'of the ADO.  This way I can build a rather extensive SQL
 'statement and plug it in where I want to.
end sub

You could read the SQL file as a regular text file to extract the SQL commands, and then execute them. I'm not sure on a way to directly execute the entire file. Can't you put them in a stored procdure?

Not quite sure I was clear...
I have a .SQL file (many SQL commands which delete tables and create them etc.)
How do I invoke that .SQL file from VB?

With your clarification, read in the .SQL file using the filesystem object. Reading in the file will build a string for you that you can pass to the cmd.Commandtext or cmd.execute XXXXXXX

dim fso as new filesystemobject
dim fstream as scripting.textstream
dim strSQl as string

set fstream=fso.opentextfile(strfilename,forreading,false)
strsql=fstream.readall

cmd.execute strsql

close...but how do I pull in the external .SQL file and execute it?

close...but how do I pull in the external .SQL file and execute it?

close...but how do I pull in the external .SQL file and execute it?

close...but how do I pull in the external .SQL file and execute it?

"how do I pull in the external .SQL file and execute it?"


Partially Originally from: http://www.mvps.org/vbnet/

   Dim fno as Integer
   Dim fname as String
   dim sqltext as string

   fno = FREEFILE
   fname = "comamnds.sql"

   Open fname For Input As #fno
       sqltext = Input$(LOF(fno), fno)
   Close #fno


   ' From emoreau  
   cn.execute sqltext  ' (or use cmd.exec sqltext)


Which is basically what edwinson said, but without using the horrendous (imo) filesystemobject.

If there's multiple commands, then you must have a way to separate them in the file (eg with a ';') - so you may have to split the sqltext first then execute each command separately, eg, using a ';' on a line by itself:

 Dim s() as string, i as long

 s = split(sqltext, vbnewline & ";" & vbnewline)
 for i = 0 to ubound(s)
    cn.execute s
 next

b6106b, the BEST way I have found to achieve this is as follows:

'read the entire file into a string
  Dim strBatch As String
  Open "MySQLFile.sql" For Input As #1
  strBatch = Input(LOF(1),#1)
  Close #1
  Dim sqlServer As SQLDMO.sqlServer
  Set sqlServer = New SQLDMO.sqlServer
  sqlServer.Connect "MySQLServer"
  sqlServer.Databases("MyDatabase").ExecuteImmediate Replace(strBatch,"GO",vbCRLF & "GO" & vbCRLF), SQLDMOExec_ContinueOnError
  sqlServer.DisConnect
  Set sqlServer = Nothing

This requires a reference to SQL-DMO object library (>= SQL7) or SQL-OLE object library (SQL 6.5). This correctly handles the multiple statements in the batch and executes the sql statement(s) as if run through Query Analyzer.

The SQL-DMO/SQL-OLE libraries are available to allow you to completely manage a SQL server, you can if you wish build a complete Enterprise Manager application using these libraries.

open the .SQL file with FSO
Set tstream = FSO.OpenTextFile(yourfile,forreading)
SqlStr = tstream.ReadAll()
Con.Execute(SqlStr)

puranik_p, a couple of points on your otherwise sensible suggestion.

1) FSO is an extra control which is really not necessary when there is a perfectly good method in standard VB to read information from a file
2) The ADO execute method has a limitation in respect of multiple sql statements in a batch seperated by GO instructions and it doesn't work.

b6106b, please follow up on this thread.

Rejecting proposed answer.

Experts, please guide me to accepting the best comment herre.

Thanks
costello
Community Support Moderator @ Experts-Exchange

TimCottee deserves the points.

I think i could also deserve the points...

I'll give you some angelIII, plenty to spare!

:-)

b6106b, A Moderator will handle this for you.
Moderator, my recommended disposition is:

    Split points between: TimCottee and angelIII

DanRollins -- EE database cleanup volunteer

Per recommendation, force-accepted.

Netminder
CS Moderator

TimCottee: points for you at https://www.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=20328902