Access for AS400 files...

[poolban]

Hello,

I am using ACCESS for generating report based on AS400 files. I have two directories in the AS400 system and both of them contain the same files – one is PROD (P) environment and the other one is TEST (T). Here, P is the AS400 library for PROD files and T is the same for TEST files. So far, I am using queries/reports based on TEST and once they are fine and accepted, I will produce the same from PROD data files.

What I find in the Access query tabs, all the SQL (for a sample file FILEA) are having the syntax like “select T_FILEA.field1, T_FILEA.field2 ….. from T_FILEA”. I have modified the SQL also to add some more tables (join) and more condition and I have followed the same syntax as T_FILEB etc., etc.

When I move the query/reports to point to PROD environment, do I have to change all the SQL to make them like P_FILEA.field and P_FILEB.field?

I will create new DSN (using AS400 Client Access) pointing to the prod environment. I will as usual modify my queries based on that DSN. I am wondering whether the SQL script will automatically change or I have to do the editing in the script.

Any help is highly appreciated,

Thanks in advance,

Pool

 

[Pat Hartman]

I'm going to offer two solutions - Clean and Dirty.

Clean:
Rename the linked tables to remove the database qualifier added by Access. This will leave your file names as FILEA, FILEB, etc. Therefore when you switch to linking to the production system, the table names won't change on you. However, this will of course break everything in your database. If you have A2K, there is a feature (which I recommend normally be turned off) that will propagate name changes and fix the various queries affected. Check the documentation, it may also fix forms/reports and code as well. Anything it does not fix you must fix manually.

Dirty:
If you can't get the name auto correct feature to work properly or you don't have A2K and you're too lazy to fix everything yourself -
Rename the linked tables to remove the database qualifier added by Access. This will leave your file names as FILEA, FILEB, etc. Create a query for each linked table and name that query with the original linked name. So, you would end up with queries named - T_FILEA, T_FILEB, etc. All of your forms/reports/queries would now use the new query instead of using the linked table directly. They would not need to be changed at all. According to the documentation, this should not cause a performance hit. But since it will cause some confusion, the clean method is preferable. The trick that makes this work is that for the most part, queries and tables are interchangeable to Access.

 

[poolban]

Thanks a lot, I picked up the clean one! I have modified the SQL commands manually and it works. But, there is one small question. Now since I am trying for both TEST (T) and PROD (P), I have created two DSNs.

Can I associate the DSNs to the tables dynamically? First of all, I can't change the DSN of an existing table - I delete the table in Access and recreate it with the new DSN. I doubt whether this is the clean way of changing the DSN to an existing table of ACCESS.

Thanks again for the help.

Regards.

 

[Pat Hartman]

You don't need to delete the linked tables to change their DSN. If you need to you can relink the tables with code. I don't have a sample that does ODBC datasources but you can find a sample that does Access tables in solutions.mdb that will get you started. If you only need to do it infrequently and can do it manually, you can do it with the Linked Table Manager. Just check the box at the lower left corner and you will be prompted for a new datasource for the tables selected from the list.