AS400

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using Access as for my Asset Tracking and Rental Management Application.
I have a table linked to AS400 to pull updated customer information. My IT
Dept doesn't want to risk my team changing anything. So I have to use
Make-Table to create my own Customer Table. When I need to update the data
every week, I run Delete Query to delete records from MY TABLE; then run Make
Table Query to pull AS400 Info. But before I can run APPEND Query I have to
change the data type of the Customer ID field from "TEXT" to "NUMBER".

Is there an easier way? I would like to set a macro, query, or VBA code
that will update my customer table with a click of a button.
 
Tricia Young said:
I am using Access as for my Asset Tracking and Rental Management Application.
I have a table linked to AS400 to pull updated customer information. My IT
Dept doesn't want to risk my team changing anything. So I have to use
Make-Table to create my own Customer Table. When I need to update the data
every week, I run Delete Query to delete records from MY TABLE; then run Make
Table Query to pull AS400 Info. But before I can run APPEND Query I have to
change the data type of the Customer ID field from "TEXT" to "NUMBER".

Is there an easier way? I would like to set a macro, query, or VBA code
that will update my customer table with a click of a button.
Click to expand...
 
Hi

What you are trying todo sounds very similar to what I needed to do in my
DB, what I did was to create a linked table from the source DB in mine, I
also created a duplicate table of the source with a different name as my
Destination table, assuming that this is linked to other tables and as data
records associated in other tables the table can't be deleted due to
referential integrity, I then set about creating a macro to use the data
without affecting the source, the Macro was along these lines:-

SetWarnings - No
DeleteObject - Delete Linked Table
TransferDatabase - Create a Link to the Soucre Table
OpenQuery - Delete Destination table
OpenQuery - Append From Soucre to Destination
OpenQuery - Update From Soucre to Destination
SetWarnings - Yes

The Macro was later converted to VBA but this works great fo me

Hope This Helps
 
Tricia said:
I am using Access as for my Asset Tracking and Rental Management
Application. I have a table linked to AS400 to pull updated customer
information. My IT Dept doesn't want to risk my team changing
anything. So I have to use Make-Table to create my own Customer
Table. When I need to update the data every week, I run Delete Query
to delete records from MY TABLE; then run Make Table Query to pull
AS400 Info. But before I can run APPEND Query I have to change the
data type of the Customer ID field from "TEXT" to "NUMBER".

Is there an easier way? I would like to set a macro, query, or VBA
code that will update my customer table with a click of a button.
Click to expand...

Delete the link and replace it with a passthrough query. They are read-only so
there is no chance of changing the data.
 
I am not sure why you are using a Make Table query if you are first deleting
all the records. I would suggest you change your approach a little. The
problem with a Make Table query is that it uses default lengths for text
fields which is usually 50 characters. This is a definite performance
problem. I think what you really want is an append query. This will also
solve your data type issues because the data types will be established by
your existing table:
Dim dbf as Database
Set dbf = CurrentDb
'Delete the old data
dbf.Execute("DELETE * FROM MyTable;")
'Load the new data
dbf.Execute("INSERT INTO MyTable SELECT AS400Table.* FROM AS400Table;")

Simple as that
 
What about changing the ODBC connection to Read Only? Then you can link the
table in and have fun!
 
Back
Top