Re: ODBC with AS400
- From: "Bob Barrows [MVP]" <reb01501@xxxxxxxxxxxxxxx>
- Date: Wed, 13 Aug 2008 12:31:54 -0400
Only if you write it that way.
I misspoke in my original post. I should have said that you would
append all the new records from the linked table into the local table:
Insert into localtable
select primarykeyfields, CDate(TRTDAT) from linkedtable
Subsequent refreshes can use an Upsert query which is only possible in
Jet AFAIK. Something like this:
Update localtable h right join linkedtable a
on h.primarykeyfields = a.primarykeyfields
set h.TRTDAT_date = CDate(a.TRTDAT)
WHERE h.TRTDAT_date <> CDate(a.TRTDAT)
Ryan wrote:
Two quick questions about update queries. The first time I run an
update query it is going to return every record in the Update To
table, and the next time I run the query it will update each field
that has changed and add any new records that have been added,
correct? --
Please remember to mark this post as answered if this solves your
problem.
"Bob Barrows [MVP]" wrote:
Do it ahead of time: create a new table containing the primary key
fields from the linked table and a new date/time field (indexed).
Run an update query to update the new date/time field with the value
from the matching record in the linked table.
Then join this table to the linked table and filter using the
date/time field.
Ryan wrote:
I have used CDate([TRTDAT]), but since there are 1.8 million
records, it takes almost an hour to run the query. Any advice?
--
Please remember to mark this post as answered if this solves your
problem.
"Bob Barrows [MVP]" wrote:
In order to use BETWEEN, you have to convert the data in TRTDAT to
dates instead of using Format to create a string:
DateFilter: CDate([TRTDAT])
should work.
Ryan wrote:
I forgot to mention that the dates from the AS/400 come in looking
like this 2008-07-01. In the linked table access shows this
column as a text field, but according to the AS/400 documentation
this is a number field. --
"Ryan" wrote:
I have an ODBC connection with an AS/400. Everything with works
great except when I add a date parameter.
DateFilter:Format$([TRTDAT],'MM/DD/YYYY') shows the date with the
formating. My problem is when I add a parameter, Between
[Forms]![Run Report]![StartDate] And [Forms]![Run Report]![End
Date], it shows all days and years. What I mean is that it only
looks like its filtering on the month so if my parameters are
07/01/2008 and 07/15/2008, it will show records with 07/21/2008
and 07/01/2006 and so on. Anyone know why it would show other
days and years than whats specified in the where clause? --
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will
get a quicker response by posting to the newsgroup.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get
a quicker response by posting to the newsgroup.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
.
- References:
- ODBC with AS400
- From: Ryan
- RE: ODBC with AS400
- From: Ryan
- Re: ODBC with AS400
- From: Bob Barrows [MVP]
- Re: ODBC with AS400
- From: Ryan
- Re: ODBC with AS400
- From: Bob Barrows [MVP]
- Re: ODBC with AS400
- From: Ryan
- ODBC with AS400
- Prev by Date: RE: Query of Non-Hidden Queries
- Next by Date: Re: ODBC with AS400
- Previous by thread: Re: ODBC with AS400
- Next by thread: Re: ODBC with AS400
- Index(es):
Relevant Pages
|
