Thread: Migration Wizard - Access 97 Autonumber

I am trying to migrate an Access 97 database to PostgreSQL 7.3 using 
pgAdmin 1.4.12 and the Migration Wizard 1.4.12.  I want to keep using 
the Access 97 front end connected to the PostgreSQL backend.

The default type mapping didn't work for me but I discovered that 
mapping  Boolean -> int2 (instead of bool) and  Currency -> float8 (instead of money)
meant I needed no changes to my Access97 front end.

The problem I had was the Autonumber mapping.  By default Autonumber 
maps to int8 which causes Access 97 to think all records in the linked 
table are deleted.  If the PostgreSQL data type is changed to int4, 
Access97 works fine.

I noticed in the mailing list archives on 05 Dec 2002, a subscriber 
mentioned that he needed int8 for autonumber fields to migrate for his 
version of Access, which I can only assume is later than Access 97.  
Dave Page replied that he had now changed it from int4.  It would be 
handy if this feature was selectable like the other type maps, so that 
neither myself, nor the original subscriber are placed in the position 
of having to drop the migrated table just to fix the data type of the 
Autonumber field.

Otherwise this is a great program that has already saved me many hours 
of work.

Tim Cummings

Hi Tim,

It's rumoured that D Tim Cummings once said:
> I am trying to migrate an Access 97 database to PostgreSQL 7.3 using
> pgAdmin 1.4.12 and the Migration Wizard 1.4.12.  I want to keep using
> the Access 97 front end connected to the PostgreSQL backend.
>
> The default type mapping didn't work for me but I discovered that
> mapping
>   Boolean -> int2 (instead of bool) and
>   Currency -> float8 (instead of money)
> meant I needed no changes to my Access97 front end.

float8 is not a good idea for monetary amounts as there may be rounding
errors and other problems. numeric is a far safer option.
> The problem I had was the Autonumber mapping.  By default Autonumber
> maps to int8 which causes Access 97 to think all records in the linked
> table are deleted.  If the PostgreSQL data type is changed to int4,
> Access97 works fine.

In the 1.5.60 version, the autonumber columns will map through the type
map, so if they start off as a 4 byte integer, they shouold end up as int4
or whatever you have mapped in it's place.
You can download from http://www.pgadmin.org/pgadmin2/downloads

Regards, Dave.

Thanks Dave,

I understand that numeric is a safer option than float8 for Currency 
fields, but in the type map editor I can't set the precision and scale.  If I use numeric without setting precision and
scale,Access 97 sees 
 
the field as "text" which is definitely not what I want.  If I could 
set it to say numeric(15,4) then Access 97 would be much happier, and 
report the field as being double.  As it is I will go with float8 and 
live with the rounding problems.

I downloaded version 1.5.60 of pgAdmin II and the migration wizard but 
they wouldn't run.  So I went to www.microsoft.com/data to get the 
latest "MDAC".  This was more difficult than I thought, because their 
news items talked about MDAC 2.8 but their downloads had a whole bunch 
of different versions, in no particular order, some as SDKs, none of 
which were 2.8.  I went with MDAC 2.7 SP1 Refresh, whatever "Refresh" 
means.  Anyway I am pleased to report that after installing MDAC 2.7 
SP1 Refresh, pgAdmin II v 1.5.60 and the Migration Wizard worked like a 
treat and correctly mapped my Access 97 Autonumber fields to int4 data 
types in PostgreSQL.

The only other gotcha I found with the software was not to leave log 
level on full debug or it slowed everything down to a crawl.  Apart 
from that it has worked like a charm.

Tim Cummings


On Sunday, Jul 13, 2003, at 18:52 Australia/Brisbane, Dave Page wrote:

> Hi Tim,
>
> It's rumoured that D Tim Cummings once said:
>> I am trying to migrate an Access 97 database to PostgreSQL 7.3 using
>> pgAdmin 1.4.12 and the Migration Wizard 1.4.12.  I want to keep using
>> the Access 97 front end connected to the PostgreSQL backend.
>>
>> The default type mapping didn't work for me but I discovered that
>> mapping
>>   Boolean -> int2 (instead of bool) and
>>   Currency -> float8 (instead of money)
>> meant I needed no changes to my Access97 front end.
>
> float8 is not a good idea for monetary amounts as there may be rounding
> errors and other problems. numeric is a far safer option.
>> The problem I had was the Autonumber mapping.  By default Autonumber
>> maps to int8 which causes Access 97 to think all records in the linked
>> table are deleted.  If the PostgreSQL data type is changed to int4,
>> Access97 works fine.
>
> In the 1.5.60 version, the autonumber columns will map through the type
> map, so if they start off as a 4 byte integer, they shouold end up as 
> int4
> or whatever you have mapped in it's place.
> You can download from http://www.pgadmin.org/pgadmin2/downloads
>
> Regards, Dave.
>
>
>

-- 
_______________________________________________

D Tim Cummings,             tim@triptera.com.au
Triptera Pty Ltd             ACN    073 371 918
207 Hawken Drive             ABN 67 073 371 918
St Lucia QLD 4067            Ph +61 4 1877 8422
AUSTRALIA                    Fx +61 7 5543 4493
_______________________________________________