Using Access form fields in pass-through queries to MYSQL

I don't know if this is thebest place for this, but here goes....

I have an Access FE using MYSQL 4.1 BE. I have several forms and PassThrough queries (using MYSQL syntax not supported or available in Access).

How can I use form field values to use as criteria for these queries?

FOr example, in a pure Access database. I may have a form 'Employees' with a text box 'EmployeeName'. THen have a query....

SELECT * FROM tblEMployees WHERE EmployeeName = [Forms]![Employees]![EmployeeName]

If I were to add this variable to my pass-through queries, it errors. How can I do the same thing, passing my form fileds to an Access Query? I am trying to do this right in the SQL view of the queries, not in VBA or Macros or anything.

Thanks,
Scott


Post your answer or comment

comments powered by Disqus
Run time error '3305'

Invalid connection string in pass through query

I have inherited an Access database which seems to be having problems connecting to an Oracle table. It successfully connects to the Oracle table in other Subs but the one having problems uses a different method to connect that I am not familiar with, if I debug it shows the line pasted below as being the problem:

qdf.Connect = "DRIVER={Oracle in OraHome92};SERVER=******;UID=******;PWD=******;DBQ =******;DBA=W;APA=T;FEN=T;QTO=T;FRC=10;FDL=10;LOB= T;RST=T;FRL=F;MTS=F;CSR=F;PFC=10;TLO=O;"

in the following context:

Set qdf = dbs.CreateQueryDef("qUnique", strSQL)
qdf.SQL = strSQL
qdf.Connect = "DRIVER={Oracle in OraHome92};SERVER=******;UID=******;PWD=******;DBQ =******;DBA=W;APA=T;FEN=T;QTO=T;FRC=10;FDL=10;LOB= T;RST=T;FRL=F;MTS=F;CSR=F;PFC=10;TLO=O;"
qdf.ODBCTimeout = 0
qdf.ReturnsRecords = True

' Open query in Datasheet view.
DoCmd.OpenQuery qdf.Name
Set RS = dbs.QueryDefs("qUnique").OpenRecordset

Whereas the following connection works fine:

strConnectString = "driver={Oracle in OraHome92};DBQ=******;UID=******;PWD=******; "
Set wrkODBC = DBEngine.Workspaces(0)
Set dbDW = wrkODBC.OpenDatabase("", False, True, strConnectString)
dbDW.Close

Any help is appreciated, if more info is required feel free to ask.

I am having difficulty finding a solution to my problem. I am using MS Access 2003 and I'm trying to create a pass-through query to a SQL Server. I wish to run the same SQL on multiple databases on the server using a single query. I know I could use a giant union query for this but i prefer not to use that method because I do not want to have to update this query every time a new database is added to the server. After searching for information I saw that some people had listed two possible solutions to this issue. Either use a Cursor and loop through the databases in the Master.sys.databases table, or use the built-in yet not officially supported stored procedure sp_MSforeachdb. My attempts at using both of these methods give me the same error. Whatever procedure I use only seems to return the first database in the list and then stops the loop. My SQL for the Cursor method is below:


	Code:
	SET NoCount ON

DECLARE @DBName VarChar(200)
DECLARE @SQL nVarChar(500)

DECLARE DBCursor CURSOR FOR
SELECT Name FROM master.sys.databases WHERE Name Like '%analysis'

OPEN DBCursor
FETCH NEXT FROM DBCursor INTO @DBName

WHILE @@FETCH_STATUS=0
BEGIN

SELECT @SQL='SELECT ' + '''' + @DBName + '''' + ' AS DBName, Name AS Company, ID AS CompanyID FROM ' + @DBName +
'.dbo.Company WHERE ID>0'
EXEC sp_ExecuteSQL @SQL

FETCH NEXT FROM DBCursor INTO @DBName

END

CLOSE DBCursor
DEALLOCATE DBCursor

The SQL I am trying for the built-in stored procedure is below:


	Code:
	EXEC sp_MSforeachdb 'IF ''?'' Like ''%analysis'' SELECT ''?'' AS DBName, Name AS Company, ID AS CompanyID FROM ?..Company
WHERE ID>0'

Both of these SQL statements return the same results but they both stop after only reading from 1 database. The SQL Server database I am connecting to is version 2005. If anyone could help on this I would greatly appreciate it. Thanks!

How do I export a pass-through query to Excel. I know how to export an Access query using docmd.transferspreadsheet.

I'm passing select queries to Pervasive SQL. I'd like to be able to export the query results directly to Excel.

So here's my setup. We have a database with several tables that have over 255 fields. So we must use pass-through queries to access some of the data.

Usually no problem.

Here is my pass-through query:

qry001: SELECT db001.table001.field001 from db001.table001

If I execute this, it instantly spits me to datasheet view with all the millions of records ready for viewing.

If I modify it to:

SELECT db001.table001.field001 from db001.table001 WHERE db001.table001.field001 = '123'

It works fine and displays the one record instantly.

Now on to the problem

When I make a new query, using this pass-through query as a data source such as:

SELECT qry001.field001 FROM tblLocal001 LEFT JOIN qry001 ON tblLocal.field001 = qry001.field001;

This query takes forever to run.

And the thing is, tblLocal001 only has one record in it. In production, this table will have thousands so I need to understand why this query is taking so long and fix it if possible.

Thanks for your help

Im trying to create a pass through query to sql server using vba in access 2000. The only trouble is every example ive come across including the one with access 2000 uses old dao syntax. Anybody got an example of a pass through query with a parameter that they could post here.

Much appreciated

I am having problems editing pass through queries. I have several stored procedures set up on an SQL server and pass through queries set up in access. Each stored procedure uses 3 input parameters (2 of which are datetime data types). The queries run when I manually enter the parameters in the sql view of the queries but I would like the users to enter the parameters in text boxes in a form and these variables to be passed to the pass through queries/reports which will in turn open them.

I have tried forcing the pass through query to look up the textbox values on a loaded form but they do not recognise the datetime variables.

I have also used recordsets to open the stored procedures in VBA. This works fine but I cannot think how to show the results in the queries or reports.

I have also tried the OpenQuery command but can not think of a way of passing the parameters to the pass through query.

Any ideas would be gratefully appreciated.

Thanks in advance,

Anna

Hi Guys, I'm trying to make a pass through query which connects to a postgresql server. However, When I try running it by clicking on a button, I get:

"Run-time error '3306':
Query must have at least one destination field

I have tried debugging it and all the values in the parameters are being recieved from the forms. and the first parameter is being highlighted. In the occasion I did try running the query, all the fields were null (expected) apart from PAdviser_ID, which was 18.

I've attached my code, if anyone wants to have a look.


	Code:
	Dim MyDb As DAO.Database, MyQry As QueryDef, MyRS As DAO.Recordset
   Set MyDb = CurrentDb()
   Set MyQry = MyDb.CreateQueryDef("")
   
   MyQry.Parameters("PAdviser_ID") = [Forms]![frm_MainMenu]![CurrentReport]
   MyQry.Parameters("PProvider_ID") = [Forms]![frm_ReportSelect]![Provider_ID]
   MyQry.Parameters("PIntroducer_ID") = [Forms]![frm_ReportSelect]![Introducer_ID]
   MyQry.Parameters("PPlanGroup_ID") = [Forms]![frm_ReportSelect]![PlanGroup_ID]
   MyQry.Parameters("PPlanType_ID") = [Forms]![frm_ReportSelect]![PlanType_ID]
   MyQry.Parameters("PDateSpecific_Start") = [Forms]![frm_ReportSelect]![DateSpecific_Start]
   MyQry.Parameters("PDateSpecific_End") = [Forms]![frm_ReportSelect]![DateSpecific_End]
   MyQry.Parameters("PDate_Start") = [Forms]![frm_ReportSelect]![Child26]![DateList_Start]
   MyQry.Parameters("PDate_End") = [Forms]![frm_ReportSelect]![Child24]![DateList_End]
   
   MyQry.Connect = "ODBC;DRIVER={PostgreSQL};DATABASE=testing;SERVER=10.0.0.2;PORT=5432;Uid=xxxxxxxxxx;Pwd=xxxxxxxx;"
   
   MyQry.ReturnsRecords = True
   
   If Forms![frm_MainMenu].[CurrentReport] = 18 Then
    MyQry.SQL = "select * from reports as (& PAdviser_ID & ','& PProvider_ID &','& PIntroducer_ID & ',' & PPlanGroup_ID & ','
& PPlanType & ',' & PDateSpecific_Start & ',' & PDateSpecific_End & ',' & PDate_Start & ',' & PDate_End) & ' as 
employee_first_name varchar,employee_last_name varchar,date_issued date,client_first_name varchar,client_middle_names
varchar,client_surname varchar,tblplantypes.plantype_group varchar,plangroups.plangroups_group
varchar,tblproviders.provider_company varchar,policy_number varchar,sum_assured numeric,benefit varchar, premium
numeric,brokerage numeric, comments text);'"
   ElseIf Forms![frm_MainMenu].[CurrentReport] = 13 Then
    MyQry.SQL = "select * from
reports([Forms]![frm_MainMenu]![CurrentReport],[Forms]![frm_ReportSelect]![Adviser_ID],[Forms]![frm_ReportSelect]![Provider_ID],[Forms]![frm_ReportSelect]![Introducer_ID],[Forms]![frm_ReportSelect]![PlanGroup_ID],[Forms]![frm_ReportSelect]![PlanType_ID],[Forms]![frm_ReportSelect]![DateSpecific_Start],[Forms]![frm_ReportSelect]![DateSpecific_End],[Forms]![frm_ReportSelect]![Child24],[Forms]![frm_ReportSelect]![Child26])"
   ElseIf Forms![frm_MainMenu].[CurrentReport] = 23 Then
    MyQry.SQL = "select * from
reports([Forms]![frm_MainMenu]![CurrentReport],[Forms]![frm_ReportSelect]![Adviser_ID],[Forms]![frm_ReportSelect]![Provider_ID],[Forms]![frm_ReportSelect]![Introducer_ID],[Forms]![frm_ReportSelect]![PlanGroup_ID],[Forms]![frm_ReportSelect]![PlanType_ID],[Forms]![frm_ReportSelect]![DateSpecific_Start],[Forms]![frm_ReportSelect]![DateSpecific_End],[Forms]![frm_ReportSelect]![Child24],[Forms]![frm_ReportSelect]![Child26])"
   ElseIf Forms![frm_MainMenu].[CurrentReport] = 25 Then
    MyQry.SQL = "select * from
reports([Forms]![frm_MainMenu]![CurrentReport],[Forms]![frm_ReportSelect]![Adviser_ID],[Forms]![frm_ReportSelect]![Provider_ID],[Forms]![frm_ReportSelect]![Introducer_ID],[Forms]![frm_ReportSelect]![PlanGroup_ID],[Forms]![frm_ReportSelect]![PlanType_ID],[Forms]![frm_ReportSelect]![DateSpecific_Start],[Forms]![frm_ReportSelect]![DateSpecific_End],[Forms]![frm_ReportSelect]![Child24],[Forms]![frm_ReportSelect]![Child26])"
   End If
   
   
   
   MyQry.Execute (MyQry.SQL)
   Set MyRS = MyQry.OpenRecordset()
   MyRS.MoveFirst

   Debug.Print MyRS!attribute_id, MyRS!attribute_name, _
      MyRS!attribute_value

   MyQry.Close
   MyRS.Close
   MyDb.Close

If you want any more information, or to ask any questions then feel free to ask.

Cheers,
Ben

Hi!

We have an old application (as an Access Front-end to a sql-server) that I need to make changes to.

I have set up a pass-through query to the SQL-server and it works flawlessly, and I have also tied it up to the sub-form, which again is attached to the main form.

When I execute the sub-form independently it shows the data just as I would expect, but whenever I try to open the main form containing the sub-form, I get an error saying:

'You can't use a pass-through query or a non-fixed-column cross tab query as a record source for a subform or subreport.'

I've been looking everywhere for a solution, and everyone suggests I have to set up the columns properly - but I'm unable to find out where I can set these. I've read numerous posts regarding this, but I'm still not able to locate the "ColumnHeadings" referred to.

I've also found references to "child link" etc that needs to be disconnected for this to work, but I don't know how to do this, and I'm not even sure that's the solution.

I would have used a regular query if it wasn't for a specific masking function that we have implemented on the Sql Server, hence the pass-through query.

Can anyone give me a few pointers?

Thanks in advance.

Best regards,

Beck

Hello,

This takes alot of different things into account but i'm thinking its the query itself that is the main problem. Apologese if i am mistaken and put this in the wrong place.

I have a Pass through Query which is actioned when a button gets pushed on a form.

Basically the functionality is that of websites when you are ordering things. So when you put in your post code it finds the addresses for you to select one and it puts it into the main form.

The problem i'm having is multipul use. When say Person 1 put the post code in They're looking for it stalls for 5 seconds (to access the data i'm assuming) Then brings up the data they've selected. However. with more than one person using the database at any given time if Person 2 then comes along and searches for the post code they want within close proximity to person1's search. it will return person1's results in person2's search.

It could be any number of factors. The VBA code used to access the query could be wrong. The method used to do this could be flawed for all i know. or the ODBC connection might be set up wrong. So again i apologise if this isn't the right part of the forum to post.

i know its a bit vague on detail so if there is any information you need to assist i can provide.

Cheers

Hi there,

I've, today, created my first pass-through query (wahooooo!) which links my Access 2007 db to a query in SQL Server.

Is there a way that I can refresh the SQL Server query from Access?

For example, when I "refreshall" from Access, the pass-through always remains at the same number of records. Because I have no visible access to SQL Server, I was advised that the query in SQL Server needs to be refreshed each time and that I can do this via Access, but not sure how to.

My apologies if I am using incorrect terminology here, but I hope you get a gist of what I am asking.

Many thanks.

I have an Access front-end connected to Oracle tables. When the user opens up the main form to add a new entry, I need to be able to pull the next sequential record number. However, I can't get this to happen, without filling out the entire record, saving, and then it will committ the number.

Can this be done with a pass-through query to the Oracle table, find the next sequential number, and then add it to my record id (ie, 06-1050, with 06 being the year and 1050 the next sequential number). I do have two fields in the Oracle table TXTYear and LNGNumber. This concatenated field is related to three additional tables that have more information. Each of these tables need to cascade a new record.

Confused? me too. any help would be appreciated.

Hey All,

I need a little help with one of my update queries.
Pretty much all I want to do is update one table (in Access 2007) based on the results from my pass-through query that pulls data from SQL Server.

Please look at my update query below:

UPDATE
[SQL (2) Vendor cost export]
INNER JOIN
[Product Cost]
ON
([SQL (2) Vendor cost export].[Item id] = [Product Cost].Product_Id)
AND
([SQL (2) Vendor cost export].[Vendor id] = [Product Cost].Pharmacy)
SET [Product Cost].[Phamracy Product ID] = [SQL (2) Vendor cost export]![Vndr item id], [Product Cost].Cost = [SQL (2) Vendor cost export]![Std cost];

[SQL (2) Vendor cost export] is the pass through query that pulls data from SQL server and Product Cost is the table I am trying to update based on the results from pass-through query.

Access gives me an error message: "Operation must use an updatable query" when I try to run this.

Can you see any errors? Any help would be greatly appreciated.

Thanks


Hi
i have an Access Front End, using linked table and pass through queries to a SQL Server BE.

one of the tables on the SQL Server, holds records of 85,000 organisations.

In access i have a form with a list box for the list of organisations, but because of the large number of organisation records, i query the organisation name by text in a text box, before populating the list box.

at the moment i have the 85,000 records on a linked table, and then i query the linked table in VBA using
WHERE (tblOrg.Org.text) Like ""*"" & [Forms]![Org]![search] & ""*""))

my question is, would a pass through query be faster than querying the 85,00 0 records in the linked table, and if it is possible how would i do it?

my pass through query sql is
SELECT org_id, org
FROM dbo.tblOrg
GROUP BY org_id, org

can i adhere the text search parameter to it.

apologies if this is loose questioning

regards
Roosn

Hi all,

I have an MS Access 2007 front end displaying data provided by parameterised pass through queries to SQL. Although the data comes across without any problem it's not presented as I'd like it in the form. I'm referring particularly to a text box which contains a brief description of the results of an examination. The 'Report' field in the SQL table is a Varchar (Max) due to the number of characters and when the query is executed manually the entire amount of text is displayed, but when executed as through VBA the textbox limits the display to 255 characters.

Any thoughts...??

Any help is appreciated I'm banging my head against the wall here.

Andy

I have an access database with a pass through query that essentially calls a Stored Procedure on a SQL Server database.

This stored procedure takes 5+ minutes to complete - it imports 100K+ records from a file, and does some number crunching as well.

I have a button on a form that I want the user to click, once clicked it prepares the text file to import (strips the header row, and quotation marks..etc.. Then runs this stored procedure.

The problem I am having is - the file is getting prepared but when I try to run pass through query I am getting an ODBC Timeout.

The code I am using is simple to run the query:

Code: With DoCmd .SetWarnings False .OpenQuery "ImportLynkData" .SetWarnings True End With I have also tried using ADO to run this by creating a connection object, and a command object and running the command - but I still get an ODBC timeout.

Am I missing something? Any help or suggestions are greatly appreciated. Thanks in advance

sb

Hi!
I want update a table (in Access 2010) based on the results from a pass-through query that pulls data from an old Fox Pro database.
With the following query I push data into my access table (everything works fine):
Code: INSERT INTO Bolle ( esercizio, numero_documento, ...) SELECT Bolle_Arca.[esercizio], Bolle_Arca.[numero_documento], Bolle_Arca.[testata_codice], ... FROM Bolle_Arca WHERE Bolle_Arca.[testata_codice] NOT IN (SELECT Bolle.testata_codice FROM Bolle) When I try to update the same table using the same pass-through query:
Code: UPDATE Bolle INNER JOIN Bolle_Arca ON Bolle.riga_codice = Bolle_Arca.riga_codice SET Bolle.esercizio = Bolle_Arca.esercizio , Bolle.numero_documento = Bolle_Arca.numero_documento, ... WHERE Bolle.spedito = False and Bolle.data_invio_mail_spedito is null and (Bolle.tracking is null or Bolle.tracking = '') and Bolle.testata_codice = Bolle_Arca.testata_codice; I get this error: "Operation must use an updatable query"

Any help would be greatly appreciated.

Thanks

Hello,
I have an Access 03 front end with linked tables via ODBC to SQL Server 2000.
My objective is to have the processsing for my INSERT/UPDATE queries occur on the SQL Server side and not access,
since performance is better this way. The INSERT/UPDATE queries are wrapped in Pass-Through queries on the Access Side
and make use of SQL Server's Global Temp tables exclusively. All of these queries are successfully sent from Access
to SQL Server and give expected results.
The problem that I am having is I am not sure how to drop the Global temp tables. I THOUGHT I could execute in VBA like such...

	Code:
	    Dim db As DAO.Database
    Dim connectedQRY As DAO.QueryDef
    Dim qryPlaceHolder As DAO.QueryDef 
 
    'WELL_RUN_##DROP_TABLE1 contains ODBC Connection string
    Set db = CurrentDb()
    Set connectedQRY = db.QueryDefs("WELL_RUN_##DROP_TABLE1")
    connectedQRY.Execute dbExecDirect '

Got an extensive Access program on my PC with a backend mysqldb with a provider. I know how to get a Pass Through Query running for a simple query but I get stuck with the following SPT query (SQL Pass_through):
SELECT Bankbook.Date, Bankbook.SeasonID, Bankbook.Data1, Bankbook.Data2
FROM Bankbook WHERE (Bankbook.SeasonID=GetParameterWaarde('strActiefSe ason'))
The parameter (season) is set during the startup of the program.
What do I have to include in the query so that the query accepts the parameter?
I need a very detailed answer as I don't know much about SQL.
Thanks, Harry

Is it possible to create a report based on a pass through query? I recently tried to do that, but everytime I try to do anything to that report (run it or modify it) the database locks up on me. Is there a way to do this?

I have just created a database to house all the information on the sales reps in my company. Then I created a pass through query to connect the sales rep information with the order information - so that I could see order volume by sales rep. I would like to be able to enter the sales rep number and a time period on a form and then be able to click a button to generate a report (via the pass through query) to show that sales rep's volume. Do you have any suggestions as to how to do that? What I tried to do (that isn't working) was create a report directly tied to the pass through query. Any assistance you can provide would be greatly appreciated.

here is the situation, I'll try to be as detailed as possible

I have two tables in SQL 2008 R2, on windows 64 bit, with 40 gigs of ram, and 4x quad core processors.

Table 1, has about 60,000 recods in it
Table 2 has about 25,000,000 record

Both of these tables are in SQL, (database size about 60 gigs).

Im accessing them using Access 2007 links.

Its an update query to update the cost and quantity available
which looks like

UPDATE InventorySuppliers
SET BoydQuantityAvailable = Boyd0004Books.SumOfQOH, Cost = Boyd0004Books.Cost, BoydOldCost = InventorySuppliers.Cost, BoydAvailDate = { fn NOW() }
FROM Boyd0004Books INNER JOIN
InventorySuppliers ON Boyd0004Books.ISBN = InventorySuppliers.LocalSKU
WHERE (InventorySuppliers.SupplierID = 4) AND (NOT (InventorySuppliers.BoydQuantityAvailable = Boyd0004Books.SumOfQOH)) or (InventorySuppliers.SupplierID = 4) AND (NOT (InventorySuppliers.Cost = Boyd0004Books.Cost))

When these changes take place, then a trigger takes over and does some calculations on the fields.

My problems are as follows. The queries either
1) Runs completely, and then the Access database doesnt close out the query telling me its done, (and I know it finished because when I run the same query in SQL, it tells me no changes to be made). I heard there is a bug that causes this, but I cant find a solution.
or
2) The query takes forever, hours even.

If I shut the trigger off, and run the query in SQL, takes maybe 30 seconds, but shutting the trigger off isnt an answer.

My question is this, is there a way to have SQL not run the triggers until after I've made all of the updates, or is there a better ay to run the query to makeit more efficient? I've tried everything I can think of, including only updating the cost, and then the quantity, but that just makes the trigger run twice.. I've even tried running in Access 2010 and no difference.

Appreciate the input, I've been trying things non stop for days.

Hi
I was wondering if someone can help me with a passthrough query.

1) I have setup an ODBC connection on my system from my Sybase Server
2) I have tested this connection by linking some tables via OSBC from the Link Table manager
3) I have set up the connection correctly in the properties section of the query window to produce a Pass Through Query


	Code:
	ODBC;DSN=xxxxx Sybase Server;UID=xxx;PWD=xxx;LANGUAGE=us_english;DATABASE=xxx


	Code:
	SELECT dbo_action_log.* FROM dbo_action_log

However I get the following error message when I try to run the Pass Through query


	Code:
	ODBC -- call failed
[Microsoft]ODBC SQL Server Driver][SQL Server]dbo_action_log not found. Specify owner.objectname or use sp_help ...... etc.

I notice that I need to do a Pass Through query to Sybase but is Pass Through queries exclusive to SQL Server only?

Thanks

I created a stored procedure called BuildAttendanceEvents_GroupByEmp
the parameters of this procedure is @LoadFrom, @LoadTo

To run this stored procedure from the MS Access, I created a pass-through query, at the Database Window. This query is connected to the same database where the stored procedure is located at. In this pass-through query, I wrote the following code to run it:

DECLARE @LoadFrom datetime, @loadTo datetime;
EXEC BuildAttendanceEvents_GroupByEmp @LoadFrom, @LoadTo;



I was expecting that this pass-through query will ask me to enter it parameters values, but it didn't ask.

Thus, how can I convert this pass-through query to a parameterized query specially if I would like to automate it??

Thanks,

Hi all,

First post here and i have a question.

I currently have a pass through query the run a stored proc from sql.
The Stored proc take 1 parameter which is the timeid. In our access DB file we have a table called DateStamp that holds the time is for the current period.

How do i call the stored proc from sql while passing it the value from the date stamp table (which is only 1 row and hold the time id value)?

my current code is :

	Code:
	Exec  p_TMIreport @iTimeId=201208



I have a pass-through query that is passing a pair of variable parameters to execute an oracale procedure.

Here it is:

Exec P_AssetTagUpdate ('ABC', 'DEF')

note, here we are trying to change a pc's tag number from ABC to DEF.

User gets the message:

ODBC call failed
[Oracle][ODBC] Syntax error or access violation (#0)

Another note: the user can execute the exact statement in oracle SQL +. Anyone know what the problem is?

Thanks.


Not finding an answer? Try a Google search.