logo
down
shadow

Execute SQL Server Pass-Through Query From Access VBA


Execute SQL Server Pass-Through Query From Access VBA

By : Hannes Seynaeve
Date : November 22 2020, 01:01 AM
around this issue I have an UPDATE pass through query saved in Access 2007. When I double-click on the pass through query it runs successfully. How can I get this query to run from VBA? I'd like it to run when my "splash screen" loads. , Use the QueryDef's Execute method:
code :
CurrentDb.QueryDefs("Q_UPDATE_PASSTHROUGH").Execute
CurrentDb.QueryDefs("Q_UPDATE_PASSTHROUGH").Execute dbSQLPassThrough


Share : facebook icon twitter icon
MS Access / SQL Server pass-through query

MS Access / SQL Server pass-through query


By : hyh0220
Date : March 29 2020, 07:55 AM
I hope this helps you . My query does run, but returns no results: , You need to surround your date parameters with single quotes.
code :
Inventory.EffectiveDate >= '2011-12-30'
SET NOCOUNT ON;

SELECT 
   inv.EffectiveDate, 
   inv.Quantity, 
   inv.SourceType,
   inv.PickingLocation, 
   inv.SourceInventory,
   loc.LocationId,
   cust.CustomerName,
   prod.ProductId,
   lFrom.LocationId as lFrom,
   lTo.LocationId as lTo
FROM  dbo.Inventory AS inv
LEFT OUTER JOIN dbo.Products  AS prod  ON prod.Product   = inv.Product
LEFT OUTER JOIN dbo.Locations AS loc   ON loc.Location   = inv.Location
LEFT OUTER JOIN dbo.Customers AS cust  ON inv.Location   = cust.ConsignmentLocation
LEFT OUTER JOIN dbo.Inventory AS src   ON src.Inventory  = inv.SourceInventory
LEFT OUTER JOIN dbo.Locations AS lFrom ON lFrom.Location = src.Location
LEFT OUTER JOIN dbo.Inventory AS trg   ON trg.Inventory  = inv.TargetInventory
LEFT OUTER JOIN dbo.Locations AS lTo   ON lTo.Location   = trg.Location
WHERE  
    inv.SourceType IN ('Q', 'G', 'P', 'A', 'B') 
    AND inv.EffectiveDate >= '2011-12-30' 
    AND inv.EffectiveDate <= '2011-12-31'; -- suspect you want < '2012-01-01' here
    -- unless your column doesn't store time.
How to execute SET IDENTITY_INSERT ON/OFF with a pass-through query from Access VBA?

How to execute SET IDENTITY_INSERT ON/OFF with a pass-through query from Access VBA?


By : Ajali
Date : March 29 2020, 07:55 AM
To fix the issue you can do
[It] complains that the passed string has to be one of DELETE, INSERT, SELECT, PROCEDURE or UPDATE.
code :
Dim cdb As DAO.Database
Set cdb = CurrentDb
Dim qdf As DAO.QueryDef
Set qdf = cdb.QueryDefs("Passthru")
qdf.Connect = cdb.TableDefs("YourExistingLinkedTableName").Connect
qdf.ReturnsRecords = False
qdf.SQL = "SET IDENTITY_INSERT " & TheRestOfYourSqlCommand
MS Access Generic Pass-Through Query to SQL Server

MS Access Generic Pass-Through Query to SQL Server


By : Blues
Date : March 29 2020, 07:55 AM
wish helps you This seems to do the trick -- at least in the case where I don't need any records back. to C Perkins for steering me in this direction.
code :
Dim strSQL As String
Dim qdef As DAO.QueryDef

Set qdef = CurrentDb.CreateQueryDef("") 'temporary pass-through
strSQL = "exec dbo.sp_whatever"

With qdef
  .Connect = gConnect
  .SQL = strSQL
  .ReturnsRecords = False
  .Execute
End With

Set qdef = Nothing
Can I run CurrentDb.Execute from MS Access with an SQL Server Query in it?

Can I run CurrentDb.Execute from MS Access with an SQL Server Query in it?


By : shu
Date : March 29 2020, 07:55 AM
This might help you Your syntax looks like T-SQL, if you're going to use CurrentDb.Execute, you need to use Jet/ACE SQL. That means: specifying ALL tables directly after UPDATE, no CONVERT, no GETDATE(), and & as the preferred concatenation operator.
code :
CurrentDb.Execute "UPDATE tableA a INNER JOIN tableB b ON a.IdNum = b.ID" _
                   & " SET a.Commentary = 'Date: ' & Date() & ' | FirstName LastName | ID: ' & b.ID " 
How to execute an external .sql file pass-through query in ms-access

How to execute an external .sql file pass-through query in ms-access


By : ayca
Date : March 29 2020, 07:55 AM
I hope this helps . As @GordonThompson advises, simply read the contents of you .sql (which is a text file like any other programming script) into the .SQL property of a QueryDef (referencing your saved query).
code :
Sub ReadAndSaveSQL()
    Dim LoadFileStr As String
    Dim qdef As QueryDef  

    ' READ .SQL INTO A STRING VARIABLE
    With CreateObject("Scripting.FileSystemObject")
          LoadFileStr = .OpenTextFile("C:\Path\To\Script.sql", 1).readall
    End With

    ' INITIALIZE QUERYDEF
    Set qdef = CurrentDb.QueryDefs("mySavedPassThroughQuery")

    qdef.SQL = LoadFileStr                       ' REWRITE QUERY'S SQL
    qdf.Close                                    ' CLOSE AND SAVE QUERY
    Set qdef = Nothing                           ' UNINITIALIZE QUERYDEF

    DoCmd.OpenQuery "mySavedPassThroughQuery"    ' OPEN SELECT QUERY TO SCREEN
End Sub
Related Posts Related Posts :
  • How to create a database snapshot in SQL Server 2008 R2
  • Query speed and expressions with constant value
  • How to do a complex calculation as this sample
  • Passing a ResultSet into a Postgresql Function
  • Netsuite custom formula field using a CASE statement with multiple WHEN conditions returning "invalid expression&qu
  • Execute query command (dynamic sql) runs faster than ordinary query
  • How to extract only numbered rows from a column
  • INSTEAD OF DELETE trigger (Postgresql)
  • Oracle Apex, when a report with sql is created, how do i get it return after entering in to a text box?
  • Select records based on two tables where one column in one table starts with the records from another tables column usin
  • How to write select using two ids combination in Oracle 11g
  • Frequently use of 'UPDATE STATISTICS WITH FULLSCAN' is normal?
  • need query for this simple pulling data sql server
  • Sql query by using decode to show grades of employee
  • Not a single function error in Oracle
  • Oracle SQL optimizer's behavior when dealing with ORs and row-independent predicates (such as functions returning same v
  • Combining output of two or more select statement
  • Must Declare Scalar Variable Exception
  • Postgres Calculate Difference Using Window Functions
  • SQL Server : unsure how to retrieve selected records
  • SQL unique field: concurrency bugs?
  • Query to get the next identity?
  • SQL Server Adding summing values based on the month
  • Join on month if available else join on latest month
  • How to solve error: ORA-01406 fetched column value was truncated?
  • Selecting Field Multiple Times using OR
  • Which SQL datatype can be used to store mobile numbers in numeric forms, without including characters like brackets and
  • Oracle SQL - "Not a group by expression" after adding CASE statements
  • Runnning total of unique string values in column over days
  • VB.Net to AccessDB by SQL - INSERT INTO with parameters error
  • SQL/Excel/VBA - UPDATE query: 'Syntax Error (missing operator) in query expression'
  • Code to combine two sql queries
  • Setting SQL Date field value based off Week Number and Day Number Field
  • SQL query to find matching records but with differences in case sensitivity?
  • How to sort record inside views in SQL Server 2008
  • How to link the output of teradata sql query to an excel sheet?
  • Matching sets in SQL when cross-referencing
  • error even after following Microsoft format in SQL
  • Increase price by percentage, results rounded
  • Can we Use "Case" in a ColdFusion Query-of-Query
  • displaying 'Active' for a row which has 'A' as value
  • Using pattern search in SQL Server Query
  • SQL - how to group transactions for a certain date pattern?
  • How to Index SQL with multiple AND conditions nested inside OR
  • SQL rank with priority
  • Does BigQuery support SubQueries?
  • Any ideas for persisting H2 Database In-Memory mode transaction?
  • How to improve SQL query performance (correlated subqueries)?
  • Filter repeated columns in Oracle
  • SQL Server join on whichever column has value populated
  • use sql stored procedure to insert data which is returned from a query stored in a table
  • Query not working1
  • SQL - using an alias in a where clause in a subquery
  • How to search for a whole word with special char using oracle contains?
  • Production Perfomance of using SELECT * statements?
  • String concatenation not working as expected in SELECT statement
  • How to detect which stored procedures UPDATE or INSERT into a certain TABLE?
  • Select statement inside CASE - SQL
  • .Net Float error 4 - float(4) = 4.44089209850063E-16
  • How to show as column to recursive rows in a table With SQL Server
  • shadow
    Privacy Policy - Terms - Contact Us © ourworld-yourmove.org