SQL Server Passthrough query as basis for a DAO recordset in Access

by

You need to use a QueryDef object to create a Pass-Through query, then open the Recordset via the .OpenRecordset method of the QueryDef. The following code works for me:

Dim qdf As DAO.QueryDef, rst As DAO.Recordset
Set qdf = CurrentDb.CreateQueryDef("")
qdf.Connect = "ODBC;Driver=SQL Server;Server=.\SQLEXPRESS;Trusted_Connection=Yes;"
qdf.SQL = "SELECT GetDate() AS qryTest"
qdf.ReturnsRecords = True
Set rst = qdf.OpenRecordset
Debug.Print rst!qryTest
rst.Close
Set rst = Nothing
Set qdf = Nothing

More Related Contents:

  1. How to programmatically create an ODBC Linked Table to a SQL Server View and have it be editable?
  2. Calling stored procedure using VBA
  3. ODBC Call Failed with stored procedure – Pass through query
  4. Why is 1899-12-30 the zero date in Access / SQL Server instead of 12/31?
  5. #DELETE viewing SQL Server table in Access
  6. How to make a passthrough / passthru query editable?
  7. SQL Server – error when creating stored procedure
  8. SQL Server insert if not exists best practice [closed]
  9. Are GUID collisions possible?
  10. SQL Server Escape an Underscore
  11. Bang Notation and Dot Notation in VBA and MS-Access
  12. Does a foreign key automatically create an index?
  13. best way to convert and validate a date string
  14. Why use the INCLUDE clause when creating an index?
  15. Why do table names in SQL Server start with “dbo”?
  16. Add alternating row color to SQL Server Reporting services report
  17. LIKE vs CONTAINS on SQL Server
  18. How do I enable MSDTC on SQL Server?
  19. An attempt was made to access a socket in a way forbidden by its access permissions
  20. TSQL Try / Catch within Transaction or vice versa?
  21. Is there any difference between DECIMAL and NUMERIC in SQL Server?
  22. SQL Server history table – populate through SP or Trigger?
  23. TSQL: Create a view that accesses multiple databases
  24. cross apply xml query performs exponentially worse as xml document grows
  25. Check if a temporary table exists and delete if it exists before creating a temporary table
  26. What is the sql connection string I need to use to access localhost\SQLEXPRESS with Windows Authentication or SQL Authentication?
  27. How to copy to clipboard using Access/VBA?
  28. SQL Error with Order By in Subquery
  29. How to change default language for SQL Server?
  30. migrate data from MS SQL to PostgreSQL?

Leave a Comment