importing data from mainframe into access tables.

[BarneyO]

I'm trying to import datea from a mainframe flat file into
several access tables. I don't seem to be able to import
signed numeric data with assigned decimal places into the
access tables I created.

If I use straight numeric, the data will load but I loose
the sign and the assigned decimal place. If I use signed
numeric, the Import Wizard refuses to load the data in.

I can make adjustments to the data at the Mainframe level
but not after the data has been loaded into the Access
tables. The import refreshes occur weekly and unattended.

Question: what record layout do I use on the sending
mainframe file? And what field format do I use in the
receiving Access tables?

 

[Joe Fallon]

Try taking 2 steps instead of one.
1. Get the data into Access any way you can.
Place it in a "staging table" that you purge between runs.

2. Once you have the data, use queries to "build" it correctly and then
append it to the real tables.

 

[John Nurick]

Hi Barney,

The general idea seems to be to import the signed numeric fields from
the mainframe into *text* fields in Access (in a staging table, as Joe
says). Then run a query using a custom VBA function to convert the
string representation of the signed field into a value suitable for an
Access number field.

IIRC there may be further complications if the mainframe output the flat
file in EBCDIC and it was subsequently converted to ASCII by software
that didn't understand the signed fields. Search
http://groups.google.com for
"signed numeric" OR "signed field" VBA
for more.