Is it possible within Access to use a pass through query that joins tables from two different servers?
If not I have a workaround solution though this is a more intensive effort.
As always, you are all great here and any help is appreciated.
Good afternoon, I have a form with a subform and in the first txtbox of the subform in the GotFocus event I have a little procedure which checks the txtboxs on the parent to make sure that there is data in all four of the txtboxes. This works great the first time and it pops up a msgbox and it even setsfocus on the txtbox with no data in it, but if I tab into the subform a second time and there still is no data in one of the txtboxs on the parent form, nothing happens, no message and no setting focus on the txtbox with no data in it. Does anyone know of a way to get this procedure to re-set everytime a user tries to enter the subform? Thank you in advance to anyone offering and ideas and suggestions.
View 8 Replies View RelatedI have an SQL table that uses bigint on the primary key field. When I lilnk this table into Access 2010, this table is not even visible because bigint isn't recognized by Access.I have a field on this SQL table that I need to update and I was looking at using a Pass Through query in Access. I have a query in Access that creates a list of records that I need to update.
View 3 Replies View RelatedHi all, i'm currently working a MDB project that aims to develop a front end access solution for users of the sql database. The system is used only to made adjustments to the WHERE clause of the SQL pass-through statement. The SELECT and FROM statements are pre-determined and users won't need to update this. The result is a read only. You might be wondering why i don't use ADP instead, well one of my limitations is that i won't have write access to the database. They are afraid i will corrupt data...
I want to use a series of text and combo boxes to build my WHERE statement, which will be added to the main SQL statement and then pass-through to the server, making use of its much beta processing capabilities. However as i'm trying to read up on VBA, i still have very little knowledge on how this can be done. Its actually similar to the Filter By Form option in datasheet view when i open a pass-through query. However, conditions applied through here means the processing is done on the user's computer, through testing i found this to be unreliable as it causes access to hang frequently. I would love to have this filter by form view available up front to the user before he even executes the query. As i mentioned, he keys in the criteria/conditions and access builds an sql where statement and appends it to the main SQL statement.
any idea how to do this? how do i capture user input and make access construct a WHERE statement from it For example
textbox daterange
Enter range of dates in here: date 1 and date 2
the where statement would then be "WHERE date BETWEEN date1 AND date2
Or is there other more efficient alternatives? sorry i'm very new to VBA and form development, would appreciate lots of advice and answers.
Many Thanks
Eric
I am after getting an access 2003 database to look after and it contains SQL pass through query's. The database is a front end to a MS SQL server database with a connection string that is contained in a module.
I believe the SQL pass through queries are connecting to the same database as the rest of the application and somehow is using the connection string in the module. However I cannot find how that is configured on the SQL pass through queries.
Most documentation on the net seems to point at using ODBC to connect slq pass through queries to outside databases but I don't think this is the case. There are not Odbc sources set up for the database I am look at.
In a table of a SQL-Server 2008 I have a fieldname "Alter", the German word for age.
When sending a PT-qry to the server I get an error "Incorrect syntax near the keyword 'Alter'"
Even when I try to set "Select tblmytable.Alter as Age" I get the same error.
To change the fieldname is almost impossible, as there is to much code to change.
I made multiple pass through queries that receive their data via an ODBC link which is established. Currently the username and password is entered seperately in every query but I was wondering if it is possible (and how) to make both look for the values in a table (OR somewhere else if possible) for these values. The following code (with edited information) is used in all queries.
Code:
ODBC;DSN=DSNNAME;Driver=Firebird/InterBase(r) driver;Dbname=Database.FDB;CHARSET=NONE;PWD=Password;UID=User;
The reason that I would like to do this is to ensure that the user can change it easily.
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
I have developed some complex pass-thru queries in Access, looking at DB2 data. I am hoping to be able to make these queries 'prompted' for easy use for my less technical colleagues. If these were developed in IBM's QMF tool, I would build the prompt like &MemberID, for example, if I wanted the user to input a value for Member ID. Access pass-thrus don't recognize this, and it throws an error. Anyone have any ideas on how I can do this?
Thanks in advance for any help...
I have about 20 columns in a query and each row is a customer.
The columns are either pass/fail but are text fields and not a yes/no set up. I want to get the percentage of a pass in the column.
Access 2003
Excel 2003
I have a routine that exports the results of a query to an Excel file. Is it possible to input the formula into the query so that the Excel values calculate?
This is the formula I am trying to pass to the "AZ" column of the Data tab
Code:
MyCalc::"IF(T2="","0",TODAY()-T2)"
I get a result set from a pt qry from a sql server 2008 in vba. Me.Total is an unbound field in the report foot( correct translation of german "Berichtsfu"??)
Private Sub Berichtsfu�_Print(Cancel As Integer, PrintCount As Integer)
SELECT SUM((DATEPART(hour, Calctime) * 60 + DATEPART(minute, Calctime)) * Price / 60) AS TtlPrice ...
Set rs = CurrentDb.OpenRecordset("qryPT_TtlPrice")
Me.Total = rs("TtlPrice ")
On the access report I can format me.Total as currency, decimal, integer whatever I want (unless it makes no sense exept currency) and it works.
but
SUM(((DATEPART(hour, Calctime) * 60 + DATEPART(minute, Calctime)) * Price/ 60)* (case when CompanyID=1 then 1.66 else 1 end)) AS TtlPrice
The detail should be multiplied with 1,66 when the record belongs to company 1, for all others ist remains the same. In the Management Studio the results of both looks similar, same in the debug window of VBA editor, but I cannot format the result or lets say, the formating is wrong for the second example.
Neither in VBA
Me.Total = format(rs("TtlPrice "), " 0.000,00") or
Me.Total = format(rs("TtlPrice "),"0.000,00")
nor in the report int the properties of the field I can avoid this result: in the debug window of access the result looks OK, for example 29555.670000 (don't know where all the 0 come from), which should read as 29.555,67. But on the report the result with formated curreny in field property (currency, 2 decimals) shows 29.555.670.000,00
I use the following code to delete from a table all records except those meeting the WHERE criteria:
Code:
DELETE tblABC.*, tblABC.SubjectID
FROM tblABC
WHERE (((tblABC.SubjectID)<>99 And (tblABC.SubjectID)<>432));
I'd like to run this exact same query, but on many other tables, all of which are stored in tblTablesToClean (TableID, TableName).
Any good way to have Access loop through the list of tables in tblTablesToClean, each time passing the name of the table into the DELETE code and running the code, until all tables have been processed?
I have an access 2007 database connect to sql server 2008.I am running a pass though query to search between two dates (this query has been fine for years)
If I now run any search using parameters from 26th March 2014 to date - the query takes 10+ minutes to run.If I then change the date to 25th March 2014 to date - it runs in a nano second.I have not changed the back tables and I have not changed the format the data is saved in.
I have wrote some code which calls a query using querydefs and then pulls all the records into a table. This table is then exported to excel, however I seem to be having some trouble with the export and am finding it difficult to identify where the problem is coming from. When I run my code in step by step, more of than not the code passes fine and exports to excel. However, most of the time when I run the code as a whole without step by step, then the excel application will load but the workbook will not. The application then continues to close and the code completes without error? The code for the opening of the excel file is below.
The pause is a function i created to see if it was a problem of giving the exel application some time to load!
Code:
Sub MiseEnForme1_Excel()
Dim AppExcel As Excel.Application
Dim WkbExcel As Excel.Workbook
Dim WksExcel As Excel.Worksheet
[code]....
I'm trying to create a query that supplies a form with data. I want to pass a TempVar to the query that is selected a from previously opened form.The TempVar is setting correctly and I can see if this if I place a textbox (NewCID) on the form showing the TempVar. The problem I have is displaying on the records according to that TempVar. If I set the query manually, i.e. "|Test|" then records are displayed but if I use the TempVar, which also displays |Test| then no records are brought back.
think it's something do with the vertical bar and that fact the field I'm searching on is a memo field, both of which I've no control over. I also have to use the Like statement because of this.Here's the query that works...
SELECT *
FROM dbo_ASSETS
WHERE ASSET_CID Like "|Test|"
and the one that I want to use, that doesn't...
SELECT *
FROM dbo_ASSETS
WHERE ASSET_CID Like [TempVars]![tmpvarCID]
I've even tried referring to the textbox instead of the TempVar, i.e.
SELECT *
FROM dbo_ASSETS
WHERE ASSET_CID Like [Forms]![AssetsCID]![NewCID]
but that doesn't work either.
Hi there
When using parameters from one form to the next I normally hide the form and then reference the parameter textboxes in the next form to the hidden form. Is there a better way of doing this as I saw threads here mentioning passing a parameter. How do I do this? Thanks!
Dave
Hi,
I want to pass a value between two forms. I created a text field in one of the forms and tried to access it using:
aVariable = frmSearch.txtQryName.Text
It's not working for me. Is this correct?
Thank you,
B
I'm trying to create a PTQ and just cant seem to find the source table.
The name of the source table is PRM1_ORG_MTRX3_N
If I write my query like this, I get an error code that states username.PRM1_ORG_MTRX3_N is an undefined name. It adds my userid to the beginning of the table name.
SELECT
T128.AS_OF_DATE,
T128.LEVEL8,
T128.LEVEL8_ORG_NAME,
T128.LEVEL9,
T128.LEVEL9_ORG_NAME
FROM
PRM1_ORG_MTRX3_N as T128
WHERE
T128.AS_OF_DATE like '1/1/2007'
Then I rewrote the query like this, and I got and error code that said PRM1.ORG_MTRX3_N is an undefined name. Does anyone know what else I can try here to get this query going? Thanks
SELECT
T128.AS_OF_DATE,
T128.LEVEL8,
T128.LEVEL8_ORG_NAME,
T128.LEVEL9,
T128.LEVEL9_ORG_NAME
FROM
PRM1.ORG_MTRX3_N as T128
WHERE
T128.AS_OF_DATE like '1/1/2007'
Hello!!!
I have a big problem. I have form A which display list of user errors. I need to pass selected error from that form A to another form B which contain user data in main form and error data in subform (this is error which I want to pass from A).
Form B tells me which error was reported from particular user. Errors are predefined in separet Form (A).
I hope you will understand me. I need help, because I am losing my maind with this one.....
How to pass the values from the first page to the second page? The first page contains the lists of Area_Code. After selecting the area_code and the button being clicked the second page automatically call the Query1 (where the area_code's Criteria is Forms!MainForm!SubForm!txtAreaCode) and get the data back on the DataSheetView on the second page.
I found many people use the comboBox and AfterUpdate() to requery the data. But my case is different. Because on the first page, I have a list of area code which has the button to check the authorisation before continuing to the second page.
Thank you very much!
I need some help please on passing parameters such
as a recordsource to a report but not using OpenArgs
I heard that this can be done using a hidden form.
I have a calling form (form1) which opens a preview snapshot
form (form2) which in turn brings up a rptCurrentRecord (form3)
I can't get the openargs RecordSource SQL from form1 to form3
For arguments sake, Form3 in this case is really the report itself.
Hope this is not too confusing.
Thanks for any help
So I have a function (TradeRoof_AfterUpdate) inside my form (frmCustomers) that calls one of 2 functions (ActivateConditions or DeactivateConditions) in a module (modRender). ActivateConditions is then supposed to call on one of 2 functions, Activate or Deactivate, also inside modRender. None of these functions are supposed to return a value, just change visibility of a control and true to false or vice versa.
i don't know why, but for some reason, i can't pass a control from ActivateConditions to Activate or Deactivate. Here are the functions (DeactivateConditions and Deactivate are nearly identical to their counterparts):
Code: Public Sub ActivateConditions(Sticker1 As Control, Condition1 As Control, Optional Condition2 As Control, _Optional Condition3 As Control, Optional Condition4 As Control, Optional Sticker2 As Control) Sticker1.Visible = TrueIf Not IsMissing(Sticker2) ThenSticker2.Visible = TrueEnd IfIf IsMissing(Condition2) ThenActivate Condition1ElseIf IsMissing(Condition3) ThenActivate Condition1Activate Condition2ElseIf IsMissing(Condition4) ThenActivate Condition1Activate Condition2Activate Condition3ElseActivate Condition1Activate Condition2Activate Condition3Activate Condition4End IfEnd Sub
Code: Private Sub Activate(ActiveCondition As Control) ActiveCondition.Visible = TrueActiveCondition = FalseEnd Sub
when i hit the trigger on my form, i get this error: "Run-time error '91': Object variable or With block variable not set," and then it highlights "ActiveCondition.Visible = True" in my Activate function.
Why can't I perform this action? Any help is greatly appreciated!
-Jason
Can you do a pass through Query to a pivot table when your query has parameters? I'm reading like you can't, but nothing has been said concretely yet. When I do it, it gives me an error "Trouble Obtaining Data" when I try and set the layout.
View 1 Replies View RelatedSo I have 2 forms, an Edit Lot Form, and an Add Run Form. Each lot will contain several runs. What I want to do is be able to add a run associated with the specific lot by pressing a button "Add Run" in the lower right corner of the edit lot form (see attached images). So the information for the lot will carry over to the run form and autofill the fields pertaining to the Lot in that form.
View 2 Replies View RelatedI have a table with timestamp as one of the columns. I am having to use a passthrough query on this table to get a few other fields which are not available on the access front end. Now i am trying to filter those records based on a date range. But the passthrough wont let me give the date range! This is what i tried:
Code:
SELECT intake_taken, staff_7, accept, Reason_for_Rejection_party
FROM DBA.case_intake
WHERE matcode = 'S S' AND accept='N' AND intake_taken BETWEEN '06/01/2011 00:00:00 AM' AND '12/01/2011 00:00:00 AM';
2nd try:
[code]....