How do I execute multiple SQL Statements in Access’ Query Editor?

by

You can easily write a bit code that will read in a file. You can either assume one sql statement per line, or assume the ;

So, assuming you have a text file such as:

insert into tblTest (t1) values ('2000');

update tbltest set t1 = '2222'
       where id = 5;


insert into tblTest (t1,t2,t3) 
       values ('2001','2002','2003');

Note the in the above text file we free to have sql statements on more then one line.

the code you can use to read + run the above script is:

Sub SqlScripts()

   Dim vSql       As Variant
   Dim vSqls      As Variant
   Dim strSql     As String
   Dim intF       As Integer

   intF = FreeFile()
   Open "c:\sql.txt" For Input As #intF
   strSql = input(LOF(intF), #intF)
   Close intF
   vSql = Split(strSql, ";")

   On Error Resume Next
   For Each vSqls In vSql
      CurrentDb.Execute vSqls
   Next

End Sub

You could expand on placing some error msg if the one statement don’t work, such as

if err.number <> 0 then
   debug.print "sql err" & err.Descripiton & "-->" vSqls
end dif

Regardless, the above split() and string read does alow your sql to be on more then one line…

More Related Contents:

  1. How does one decompile and recompile a database application?
  2. How do you use version control with Access development?
  3. Microsoft Access condense multiple lines in a table
  4. Bang Notation and Dot Notation in VBA and MS-Access
  5. How do I make a stored procedure in MS Access?
  6. How can I get table names from an MS Access Database?
  7. Syntax error in insert into statement – what’s the error?
  8. Is there any way to create multiple insert statements in a ms-access query?
  9. Difference between Microsoft.Jet.OleDb and Microsoft.Ace.OleDb
  10. Microsoft Access DBEngine.OpenDatabase() breaks for Network-Paths – Error 3050
  11. Is it possible to create a recursive query in Access?
  12. VBA: Difference in two ways of declaring a new object? (Trying to understand why my solution works)
  13. What is the equivalent of Select Case in Access SQL?
  14. How do I access the selected rows in Access?
  15. MS Access LIMIT X, Y
  16. Prevent enabling of shift bypass – Access 2010
  17. Access 2007 – Left Join to a query returns #Error instead of Null
  18. Access query producing results like ROW_NUMBER() in T-SQL
  19. Pivot Query in MS Access
  20. Setting up an MS-Access DB for multi-user access
  21. MS Access prepared statements
  22. How can I modify a saved Microsoft Access 2007 or 2010 Import Specification?
  23. Null values for variables in VBA
  24. Custom row source for combo box in continuous form in Access
  25. MS Access query returning Chinese characters – possible table corruption?
  26. Using a centralized VBA module in multiple Access databases
  27. How to execute a query in ms-access in VBA code?
  28. How to add a menu item to the default right click context menu
  29. VBA – Run Time Error 3271 using DAO object
  30. Force all users to disconnect from 2010 Access backend database

Leave a Comment