[Fwd: Re: Access 97 DB to Postgres Migration Questions]
От | Andrew Ayers |
---|---|
Тема | [Fwd: Re: Access 97 DB to Postgres Migration Questions] |
Дата | |
Msg-id | 3F184B3A.6030005@eldocomp.com обсуждение исходный текст |
Список | pgsql-general |
Raymond wrote: > Have an Access 97 database being migrated to Postgres 7.3.3. Access 97 clients > will query and write to the Postgres database across a WAN via ODBC. When you say "Access 97 clients" - are you meaning the clients accessing the database are using Access 97 as the front-end? That is what I am taking as your meaning. One thing I ran into, and this concerns Memo fields (and issues regarding updates and speed when accessing them) - I thought I would throw it out, to help others. Basically, my front-end application, which used an Access 97 back-end database, was written in VB6, and used DAO. When I converted the application over to use PostgreSQL via ODBC, using the latest ODBC driver, I was having issues with updating TEXT-type fields (which I had converted the memo fields to) in a certain manner. In order to get around this issue, I changed the TEXT fields to large VARCHAR fields (on the order of 80,000 - 128,000 bytes wide) - but then any SQL selects involving those fields ran *very* slowly. I hacked my way around this problem, restructuring the SQL and how I used the statements, coding my way around issues... I ended up running into some problem I couldn't get past - I don't remember what it was, probably speed related or something. In the end, what I found was that if I switched to using ADO instead of DAO, the problems went away. I converted those fields back to TEXT fields, the speed issue didn't seem a problem anymore, and my update issue on those fields was solved as well (however, I have recently run into other problems stemming from things that DAO allowed that ADO doesn't allow - but that is another story). I think if I was developing *any* application in the future that used any database backend via ODBC (or maybe otherwise) - I would stick to straight SQL for all interaction with the database - it just seems like the best way to go all around. If you can do this with your Access system, that would probably be best. > 2) Is an ODBC type conversion table available for Access to Postgres? As noted, most types should convert OK. As I noted above, memo fields map to TEXT fields. Autonumber fields will likely become SERIAL sequence fields. Also, yes - via the ODBC driver, booleans act "funky" - there is a flag that you can pass (easiest if you set up a DSN-less connection, if possible - otherwise configure the driver) to switch the operation to the way Access and VB expect (IIRC, PG has it as 1=true, 0=false, while Access likes -1=true, 0=false) - in my application, I just left the driver as default, and had code that during the read, would take the value and CBOOL it (to convert the 1/0 to true/false), and on a write, would take my variable and ABS() it (to convert true=-1, to +1 - and false=0 stays 0) - this worked OK for me. Not pretty, but it works. > Lastly any caveats or recommendations from those with previous Access 97 / > Postgres experience would be greatly appreciated. Your greatest hurdle may be the actual conversion of the data from Access to PostgreSQL. There are Access templates and such to do it. Myself, because of the control I wanted/needed - I ended up writing a VB program to convert the data, which would squirt the data over the ODBC connection to the new tables. My program can convert several of the types properly. It can also convert just the table defs, or the data as well (so you can convert the tables, then later the data "over-and-over" if you need to, without recreating the tables). It also has a bunch of other functions. It isn't perfect, but it worked OK for me. Good luck on your conversion, and I hope my comments help you or someone else out there in the future... Andrew L. Ayers Phoenix, Arizona -- CONFIDENTIALITY NOTICE -- This message is intended for the sole use of the individual and entity to whom it is addressed, and may contain informationthat is privileged, confidential and exempt from disclosure under applicable law. If you are not the intendedaddressee, nor authorized to receive for the intended addressee, you are hereby notified that you may not use, copy,disclose or distribute to anyone the message or any information contained in the message. If you have received thismessage in error, please immediately advise the sender by reply email, and delete the message. Thank you.
В списке pgsql-general по дате отправления: