I've been stumped on this for quite a while. I don't know if it's so simple
that I just can't see it, or it's really possible. (Obviously, I HOPE it IS
possible.)
I'm trying to get my queries to run from VB. My pass-thru query retrieves
data from our AS/400 that I use to build a local table (on my PC). My pass-thru
and local do in fact work together when I run them interactively. But I want,
no make that NEED, to run them from VB.
Here's the code that Access built for the local query:
INSERT INTO tblAcctsRecAgin g_Details
( CustID,
LocID,
CustClass,
Serv,
PeriodYear,
PeriodMonth,
AgeCode,
ChgType,
ChgDesc,
90dayAmtBilled,
90dayUnpaid )
SELECT
qryAcctsRecAgin g_0040_90days_4 20_BE.UTCSID,
qryAcctsRecAgin g_0040_90days_4 20_BE.UTLCID,
qryAcctsRecAgin g_0040_90days_4 20_BE.UTRCLS,
qryAcctsRecAgin g_0040_90days_4 20_BE.UTSVC,
qryAcctsRecAgin g_0040_90days_4 20_BE.UTPEYY,
qryAcctsRecAgin g_0040_90days_4 20_BE.UTPEMM,
qryAcctsRecAgin g_0040_90days_4 20_BE.UTAGE,
qryAcctsRecAgin g_0040_90days_4 20_BE.UTTTYP,
qryAcctsRecAgin g_0040_90days_4 20_BE.UTTDSC,
qryAcctsRecAgin g_0040_90days_4 20_BE.UTTAMT,
qryAcctsRecAgin g_0040_90days_4 20_BE.UTUNPD
FROM tblAcctsRecAgin g_Details
RIGHT JOIN qryAcctsRecAgin g_0040_90days_4 20_BE
ON tblAcctsRecAgin g_Details.LocID =
qryAcctsRecAgin g_0040_90days_4 20_BE.UTLCID;
(Please don't tell me that my (BE) pass-thru query name is too long. While that
may be true, the fact of the matter is...it WORKS.)
Here's my pass-thru (qryAcctsRecAgi ng_0040_90days_ 420_BE) query:
SELECT distinct
b.UTCSID,
b.UTLCID,
b.UTRCLS,
b.UTSVC,
b.UTPEYY,
b.UTPEMM,
b.UTAGE,
b.UTTTYP,
b.UTTDSC,
b.UTTAMT,
b.UTUNPD
FROM tblCXLIB.UT420A P as b
WHERE
((b.UTAGE='C') AND
(((b.UTPEMM)=8) AND ((b.UTPEYY)=4)) Or
(((b.UTPEMM)=7) AND ((b.UTPEYY)=4)) )
ORDER BY
b.UTRCLS,
b.UTSVC,
b.UTPEYY,
b.UTPEMM,
b.UTTTYP,
b.UTTDSC;
I have modified the local query to:
INSERT INTO tblAcctsRecAgin g_Details
( CustID,
LocID,
CustClass,
Serv,
PeriodYear,
PeriodMonth,
AgeCode,
ChgType,
ChgDesc,
90dayAmtBilled,
90dayUnpaid )
SELECT
UTCSID,
UTLCID,
UTRCLS,
UTSVC,
UTPEYY,
UTPEMM,
UTAGE,
UTTTYP,
UTTDSC,
UTTAMT,
UTUNPD
FROM tblAcctsRecAgin g_Details
RIGHT JOIN (<my pass-thru "BE" query>)
ON tblAcctsRecAgin g_Details.LocID = tblCXLIB_UT420A P.[UTLCID];
BUT...I'm getting an error #3131 message of "Syntax error in FROM clause". I
feel that I'm getting closer, but I still haven't gotten it quite "right". I've
tried varaiations of this FROM clause, but to no avail.
Can anyone help? Have any thoughts/suggestions?
Thanks in advance. 8 2613
Tcs wrote: I've been stumped on this for quite a while. I don't know if it's so simple that I just can't see it, or it's really possible. (Obviously, I HOPE it IS possible.)
I'm trying to get my queries to run from VB. My pass-thru query retrieves data from our AS/400 that I use to build a local table (on my PC). My pass-thru and local do in fact work together when I run them interactively. But I want, no make that NEED, to run them from VB.
<Snipped Queries>
BUT...I'm getting an error #3131 message of "Syntax error in FROM clause". I feel that I'm getting closer, but I still haven't gotten it quite "right". I've tried varaiations of this FROM clause, but to no avail.
Can anyone help? Have any thoughts/suggestions?
Thanks in advance.
Okay, we got the queries, obviously they've been saved. (Right?) You didn't
show us the code in question that runs these queries, but it should be
just:
docmd.openquery "<Insert Local Query Name Here>"
If your saved Local Query runs, this will run.
Also, looking through this carefully, your modified local query probably
doesn't know about the table tblCXLIB_UT420A P. It only knows about your
<my pass-thru "BE" query>, as you put it. You should probaly change that
to qryAcctsRecAgin g_0040_90days_4 20_BE from your previous query. Double
check your right join on statement.
--
Christopher Lewis
On Wed, 01 Sep 2004 00:33:56 -0500, Christopher Lewis <ch******@cox.n et> wrote: Tcs wrote:
I've been stumped on this for quite a while. I don't know if it's so simple that I just can't see it, or it's really possible. (Obviously, I HOPE it IS possible.)
I'm trying to get my queries to run from VB. My pass-thru query retrieves data from our AS/400 that I use to build a local table (on my PC). My pass-thru and local do in fact work together when I run them interactively. But I want, no make that NEED, to run them from VB. <Snipped Queries>
BUT...I'm getting an error #3131 message of "Syntax error in FROM clause". I feel that I'm getting closer, but I still haven't gotten it quite "right". I've tried varaiations of this FROM clause, but to no avail.
Can anyone help? Have any thoughts/suggestions?
Thanks in advance.
Okay, we got the queries, obviously they've been saved. (Right?) You didn't show us the code in question that runs these queries, but it should be just: docmd.openquer y "<Insert Local Query Name Here>"
If I just wanted to run the saved queries, then yes, this is what I would do.
(And have done so elsewhere.) But I don't just want to run the saved queries.
The parameters are variable. So I build my SQL statements in VB. (I've looked
at them, VB has built what I have told it to build.) So in THIS case, I use the
DoCmd.RunSQL statement, using my built statements. THIS...is what I can't get
to work. My assumption being is that my syntax is incorrect somehow. If your saved Local Query runs, this will run.
Also, looking through this carefully, your modified local query probably doesn't know about the table tblCXLIB_UT420A P. It only knows about your <my pass-thru "BE" query>, as you put it.
Actually, tblCXLIB_UT420A P is a linked table to the AS/400. Access hasn't had a
problem with it before.
You should probaly change that to qryAcctsRecAgin g_0040_90days_4 20_BE from your previous query.
I've tried that. It doesn't work.
Double check your right join on statement.
It's what Access used. It sounds right, it seems right, I just don't know if I
got the syntax right.
MAYBE...MAYBE my problem is more fundamental...
Perhaps when using the DoCmd.RunSQL command, Access doesn't know that the
pass-thru query, is in fact, a pass-thru query. Maybe it's trying to run it
locally. Hmmm...if this is true, then how WOULD I run a pass-thru query using
this command?
"Tcs" <ts****@eastpoi ntcity.org> wrote in message
news:e9******** *************** *********@4ax.c om... On Wed, 01 Sep 2004 00:33:56 -0500, Christopher Lewis <ch******@cox.n et>
wrote: MAYBE...MAYBE my problem is more fundamental...
Perhaps when using the DoCmd.RunSQL command, Access doesn't know that the pass-thru query, is in fact, a pass-thru query. Maybe it's trying to run
it locally. Hmmm...if this is true, then how WOULD I run a pass-thru query
using this command?
Bingo! DoCmd.RunSQL assumes a local query. I don't think it can be used
for a pass-thru. You could define a saved Query with the proper pass-thru
settings for Connect, etc., and then modify the SQL of that query using
your code and then execute the saved query. I do this all the time.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
On Wed, 1 Sep 2004 13:10:10 -0500, "Rick Brandt" <ri*********@ho tmail.com>
wrote: "Tcs" <ts****@eastpoi ntcity.org> wrote in message news:e9******* *************** **********@4ax. com... On Wed, 01 Sep 2004 00:33:56 -0500, Christopher Lewis <ch******@cox.n et> wrote: MAYBE...MAYBE my problem is more fundamental...
Perhaps when using the DoCmd.RunSQL command, Access doesn't know that the pass-thru query, is in fact, a pass-thru query. Maybe it's trying to run
it locally. Hmmm...if this is true, then how WOULD I run a pass-thru query using this command?
Bingo! DoCmd.RunSQL assumes a local query. I don't think it can be used for a pass-thru. You could define a saved Query with the proper pass-thru settings for Connect, etc., and then modify the SQL of that query using your code and then execute the saved query. I do this all the time.
Okay. You're saying I'd actually 'run' my saved query with the DoCmd.OpenQuery
command, which should work, since my queries work interactively. But I would
programatically CHANGE my saved query before doing so? How would I do that?
Could you provide an example?
Thanks!
"Tcs" <ts****@eastpoi ntcity.org> wrote in message
news:51******** *************** *********@4ax.c om... On Wed, 1 Sep 2004 13:10:10 -0500, "Rick Brandt"
<ri*********@ho tmail.com> wrote:
Okay. You're saying I'd actually 'run' my saved query with the
DoCmd.OpenQuery command, which should work, since my queries work interactively. But I
would programatically CHANGE my saved query before doing so? How would I do
that? Could you provide an example?
CurrentDB.Query Defs("QueryName ").SQL = strNewSQL
CurrentDB.Execu te "QueryName" , dbFailOnError
(I never use DoCmd.OpenQuery )
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
On Wed, 1 Sep 2004 15:35:54 -0500, "Rick Brandt" <ri*********@ho tmail.com>
wrote: "Tcs" <ts****@eastpoi ntcity.org> wrote in message news:51******* *************** **********@4ax. com... On Wed, 1 Sep 2004 13:10:10 -0500, "Rick Brandt" <ri*********@h otmail.com> wrote:
Okay. You're saying I'd actually 'run' my saved query with the DoCmd.OpenQuer y command, which should work, since my queries work interactively. But I would programatically CHANGE my saved query before doing so? How would I do that? Could you provide an example?
CurrentDB.Quer yDefs("QueryNam e").SQL = strNewSQL CurrentDB.Exec ute "QueryName" , dbFailOnError
(I never use DoCmd.OpenQuery )
Wow. I haven't had time to do everything that I need to do...yet, but I DID
modify the contents of my query, by using the command you said to use. WOW. It
works. And it's so...so...simpl e.
Now, another question, or two, if I may. Does the '.SQL' imply that it's a
pass-thru query? (What DOES the '.SQL' mean?) Would I use:
CurrentDB.Creat eQueryDef("Quer yName").SQL = strNewSQL
if my query didn't exist?
Thank you, thank you, thank you!
"Tcs" <ts****@eastpoi ntcity.org> wrote in message
news:qf******** *************** *********@4ax.c om... Wow. I haven't had time to do everything that I need to do...yet, but I DID modify the contents of my query, by using the command you said to use. WOW.
It works. And it's so...so...simpl e.
Now, another question, or two, if I may. Does the '.SQL' imply that it's a pass-thru query? (What DOES the '.SQL' mean?) Would I use:
The SQL propery simply contains the SQL statement for the query. The graphical
query design grid is merely a tool for letting Access create the SQL in the
background. You can go into SQL view for any query to look at the statement.
The command I gave you sinply allows you to change the SQL statement from code.
It is the Connect property that specifies that a query is a Pass-thru.
Non-Pass-Thrus will have no Connect property while Pass-Thrus will.
Would I use: CurrentDB.Creat eQueryDef("Quer yName").SQL = strNewSQL if my query didn't exist?
It's a bit more than that. CreateQueryDef would create an in-memory QueryDef
object and then you would need to append that to the database's QueryDefs
collection to make it permanent. If you look at the help file for
CreateQueryDef you will see examples of its usage.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
On Wed, 1 Sep 2004 19:09:42 -0500, "Rick Brandt" <ri*********@ho tmail.com>
wrote: "Tcs" <ts****@eastpoi ntcity.org> wrote in message news:qf******* *************** **********@4ax. com... Wow. I haven't had time to do everything that I need to do...yet, but I DID modify the contents of my query, by using the command you said to use. WOW.
It works. And it's so...so...simpl e.
Now, another question, or two, if I may. Does the '.SQL' imply that it's a pass-thru query? (What DOES the '.SQL' mean?) Would I use:
The SQL propery simply contains the SQL statement for the query. The graphical query design grid is merely a tool for letting Access create the SQL in the background. You can go into SQL view for any query to look at the statement. The command I gave you sinply allows you to change the SQL statement from code.
It is the Connect property that specifies that a query is a Pass-thru. Non-Pass-Thrus will have no Connect property while Pass-Thrus will.
Would I use: CurrentDB.Creat eQueryDef("Quer yName").SQL = strNewSQL if my query didn't exist?
It's a bit more than that. CreateQueryDef would create an in-memory QueryDef object and then you would need to append that to the database's QueryDefs collection to make it permanent. If you look at the help file for CreateQueryD ef you will see examples of its usage.
Thank you....VERY much! This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: rigga |
last post by:
Hi,
I am trying to code a menu for a webpage, the menu is broken up to groups
i.e:
Home
Group1
 menuitem1
 menuitem2
 menuitem3
|
by: pmatos |
last post by:
Hi all,
I've been having questions about strings, references,
initializations... I've created code (which will not compile due to a
reference problem) and I'd like comments on why this won't work and any
other comment to the code with efficiency in mind.
#include <iostream>
#include <string>
|
by: Chad Z. Hower aka Kudzu |
last post by:
A few of you may recognize me from the recent posts I have made about Indy
<http://www.indyproject.org/indy.html>
Those of you coming to .net from the Delphi world know truly how unique and
"huge" Indy is both as a project, in support, development, and use. But
Indy is new to the .net world.
Indy is a HUGE library implementing over 120 internet protocols and
standards and comes with complete source. Its an open source project, but
not...
|
by: Jack Addington |
last post by:
I've got a fairly simple application implementation that over time is going
to get a lot bigger. I'm really trying to implement it in a way that will
facilitate the growth. I am first writing a WinForms interface and then
need to port that to a web app. I am kinda stuck on a design issue and need
some suggestions / direction.
Basically I have a business layer that I want to use to process any
dataentry logic (row focus changes, data...
|
by: Bob |
last post by:
Hallo,
I have to make a web application in Javascript/ASP for tenniscourt
reservation (based on Access database). I would like to do everything with
one page, because the user must be able to change his day, hour or
tenniscourt choice before really reserving it . I tried like this:
The first SELECT contains the date (next 30 days) and is filled dynamically.
No problem.
When the date is chosen (by clicking), the second SELECT must...
| |
by: Vagabond |
last post by:
I am trying to write a PHP script for tracking information and storing it in text files. The program has gotten to the point that I now need several php pages for the program to work however I can't find information on passing information from one PHP page to another. I know how to read the $_ENV with CGI to pass the information but am trying to learn PHP over CGI.
Is there a way to read the URL information for something like this example...
|
by: manmit.walia |
last post by:
Hello Everyone,
Long time ago, I posted a small problem I had about converting a VB6
program to C#. Well with the help with everyone I got it converted.
But I overlooked something and don't understand why it is doing this.
Below is my code, I would be greatfull if someone can guide me through
the right path or even help me solve this issue.
Problem: The old tool which was written in VB6 works perfect. But I
needed to convert this to C#...
|
by: CuTe_Engineer |
last post by:
hii,
i have cs assignment i tried to solve it but i still have many
errors , plzz help mee :"< it`s not cheating becuz i`ve tried & wrote
the prog. i just wanna you to show me my mistakes
#these are the operations
+ = ,
- = ,
* = ,
1/ = only if 0 not in .
|
by: vijaykumardahiya |
last post by:
Hello Sir,
I have a simple Issue but It is not resolve by me i.e input parameter
are not store in Ms-Access.
I store the input parameter through Standard Action <jsp:useBean>.
jsp:useBean call a property IssueData. this property exist in
SimpleBean which create a connection from DB and insert the data.
At run time servlet and server also show that loggging are saved in DB.
But when I open the table in Access. Its empty.
Ms-Access have...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |