Hi All,

I have been doing a fair bit of coding for an application that extracts data from a MS Access database into various Excel workbooks. I have tradtionally written SQL and passed it onto DAO to extract the data. This has worked quite well in the past however I am now trying to use other databases like SQLServer and MySQL. Those apps use slightly different syntax then Access. Obviously this cause me problems in that I have to create multiple type of SQL strings.

My question really is about the pros/cons of writing the SQL and passing it through to DAO or calling a stored query and passing parameters through DAO.

Is either method faster or more secured than the other?

Here's an example of my 2 different but similar methods:

Passing SQL:
  Set db = OpenDatabase("c:\mydb.mdb")
  Set rs = db.OpenRecordset( _
     "Select * From myTable Where myName = 'Sam'; " , dbOpenSnapshot)
  Range("A1").CopyFromRecrodset rs
  db.close
Using a stored query:
  Set db = OpenDatabase("c:\mydb.mdb")
  Set qd = db.QueryDefs("qrymyTable")
  qd("myName")= "Sam"
  Set rs = qd.OpenRecordset()
  Range("A1").CopyFromRecrodset rs
  db.close