Re: microsoft sql, excel/access data transfer problems

I don't use the MS tools, but if it's the field name causing the problem then you could try delimiting the field name "CU#">500 or creating an LF or View to rename the fields.

An SQL View would be better than an LF. An SQL View is equivalent to a non-keyed LF. This Redbook would be a good resource to learn about SQL vs DDS:
"Modernizing IBM eServer iSeries Application Data Access - A Roadmap Cornerstone: SG24-6393-00"

A view can be as simple as:
CREATE VIEW v1(CUNUM,IMYTD) AS (SELECT CU#,IMYTD$ FROM ddsfile);


George Applegate wrote:
How do I deal with some of these issues? If anyone has "been there,
done that", I'd appreciate knowing your workarounds, etc.

I created an odbc data source using CA ODBC administration and chose
naming convention "SQL naming convention". In excel, if I go "Data",
"Import External Data", then "New Data Base Query", I can select the
source and it gives me a list of files.

If I select my CUSTMAST, then it lets me select fields I want.. Then
I encounter some problems. My customer number is a field CU#; it will
let me select that field but if I try to select "CU# < 5000", it gives
me an error (SQL5001 - column qualifier or table undefined), it
doesn't like the "#", I think. If I leave out any selecting, it will
bring in the CU# column, I just can't narrow down my data selection
using that field.

Also, I have some fields in other files that have a "$" - i.e.
"IMYTD$". It doesn't even allow me to include those fields. Gives me
an SQL token error "$" not valid" SQL0104 (on PC)

Then, thought I'd be fancy and try to bring in columns from two files,
i.e. from INVHIST, bring in MFG, Part#, and then get the Item Descr
from the INVMAST file. It gives me an error (Query wizard cannot join
tables in your query. You must join tables manually in Microsoft Query
by dragging the fields to join between the tables), but it doesn't
really explain or show how to do that.

Has anyone worked with this like this? I know I can use traditional
file transfer in client access and even the excel plug-ins, but this
method really seems powerful and would like to get my arms around it.
Is there a way to avoid the "#" and "$" problems? This also does the
transfer very, very fast, and seems very flexible.

Do I have to redefine my files on the AS/400 without the # and $
characters, or create logicals that rename the fields??? What is the
best way to approach this? I'm not exactly a DDS wizard either; can I
just somehow do a rename in my existing DDS for the file? BUt I would
think then my existing programs that reference that file would be
toast?

Another question. If I would open access, can I link directly to an
AS/400 data-base? How do I do this? Can I avoid making or defining
fields in access and linking them to the as/400 - can it just grab
them automagically from the as/400 so to speak? Can I link directly to
an AS/400 data-base? How do I do this?

I'd appreciate any help or any pointers.

Thanks,
ga

George Applegate
gappleg8@xxxxxxxxxx
.

Relevant Pages

  • Help with Grouping and NULL Values!
    ... ID CreateDate CloseDate ... If I wrote this exact same query in MS SQL, ... NULL values (i can rename null to whatever i want here or even just ...
    (microsoft.public.access.queries)
  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)
  • Re: Populating a list -- table structure?
    ... I had made a report already and figured out about adding the ... your responce below, but thanks to your help with SQL, I was able to get the ... It takes a summary from a select query and gives the ... KitID, long integer ...
    (microsoft.public.access.forms)
  • How do I do Paging through a large dataset via Stored Procedures
    ... Paging by dynamically altering the SQL Query ... Create stored procedures ... SELECT * FROM STUDENTS ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Populating a list -- learning Access
    ... It is kinda funny -- I started using databases in the early 80's with dBase and, for years, never knew that I knew SQL! ... If you are on a form or report, the most important property is the NAME, because that is how you refer to it in code. ... I don't mean to skip your responce below, but thanks to your help with SQL, I was able to get the query to pull the info the way I needed. ... Queries (just shows the QBE grid for convenience -- ...
    (microsoft.public.access.forms)