Manufacturing Information Solutions Forum Index Manufacturing Information Solutions
Your Place for Support and Discussions
 
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Create an SQL Pass-Through Query Using Code

 
Post new topic   Reply to topic    Manufacturing Information Solutions Forum Index -> Microsoft Access
View previous topic :: View next topic  
Author Message
mistux
Site Admin


Joined: 25 Jun 2004
Posts: 1042
Location: South Bend, Indiana USA

PostPosted: Thu Jan 15, 2009 6:06 pm    Post subject: Create an SQL Pass-Through Query Using Code Reply with quote

Here is how you can create Query using code. It will show up in your database window after you click on an entry in it then hit F5 to refresh the display.

Code:

DoCmd.Hourglass True

  Dim cat As ADOX.Catalog
  Dim cmd As ADODB.Command
  Dim SPTQueryName As String    'Query name
  Dim strSQL As String  'Your query
 
  Set cat = New ADOX.Catalog
  Set cmd = New ADODB.Command
 
  SPTQueryName = "MyTestSP"
  strSingleQ = Chr(39)  'Single quote mark
   
  strSQL = "sp_report DepositDetail show TxnType, Date, Account, Amount Parameters DateFrom= {d" & strSingleQ & "2009-01-01" & strSingleQ & "},  DateTo= {d" & strSingleQ & "2009-08-23" & strSingleQ & "}"
 
  cat.ActiveConnection = CurrentProject.Connection

  Set cmd.ActiveConnection = cat.ActiveConnection

   'Delete query if exists, then recreate it using current strSQL criteria
   cat.Procedures.Delete SPTQueryName

  cmd.CommandText = strSQL
  cmd.Properties("Jet OLEDB:ODBC Pass-Through Statement") = True
 
 'Modify the following connection string to reference an existing DSN for your system.
 
 cmd.Properties _
     ("Jet OLEDB:Pass Through Query Connect String") = _
       "ODBC;DSN=QuickBooks Data;SERVER=QODBC;OptimizerDBFolder=%UserProfile%\QODBC Driver for QuickBooks\Optimizer;OptimizerCurrency=O;OptimizerAllowDirtyReads=N;SyncFromOtherTables=Y;IAppReadOnly=Y"

  cat.Procedures.Append SPTQueryName, cmd

  Set cat = Nothing
  Set cmd = Nothing

DoCmd.Hourglass False
MsgBox "done"


NOTE: The sample code in this article uses both ADO and ActiveX Data Objects Extensions for Data Definition Language and Security (ADOX). For this code to run properly, you must click References on the Tools menu in the Visual Basic Editor and make sure that the following two references are selected:

Microsoft ActiveX Data Objects 2.1 Library
Microsoft ADO Ext. 2.6 for DDL and Security
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Post new topic   Reply to topic    Manufacturing Information Solutions Forum Index -> Microsoft Access All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group