Re: Inserting records into Informix using MS-ACCESS

It is of course impossible to use a passthrough query to select data out = of an access table and insert it into an informix table. The solution proposed by Eric, not to use a passthrough query, but a = standard MSaccess query works, but is as slow as death. On small tables = it is OK to use but on larger tables you would have to be masochistic to = use it. This is what I would do, for a table of any size, but then I am a unix = weenie. export the table as a tab delimited file. ftp the file to unix. sed 's/ /|/g' load the data using the load command in dbaccess. Which is what you originally intended to do and the most efficient for = large tables. rgds Peter Tashkoff <tashkop@iname.com> Zespri International Limited. Standard Disclaimers apply All rights reserved, no party may use this document to vilify another Zespri New Zealand Kiwifruit, The Worlds Finest >>> "Eric R. Gavin" <Eric_Gavin@maf.org> 11/02/98 10:21:41 >>> >I have a feeling that this action is not functionally possible within >MS-ACCESS. Actually, this is QUITE possible, and works quite nicely actually, I do it all the time. >I can get MS-ACCESS to update records in an Informix table using an >MS-ACCESS table as the source, but every time I try and insert using a >pass-thru query, MS-ACCESS sends the whole query to Informix. Therefore, >Informix is trying to insert records from an Informix table that does not >exist, instead of MS-ACCESS passing it the data. That's VERY strange that you can update but not insert. What ODBC driver are you using? I use Informix CLI-2.5 as my ODBC driver and everything works fine. One thing you have to remember though: MS Access requires a Primary Key on an attached table to do ANY sort of editing operations on it (UPDATE, DELETE, INSERT) With the CLI driver, it usually asks you what field you want designated as the primary key when you attach the table (a little weird) but that might = be because the data I'm operating on is owned by BAAN -- the funkiest ERP software I've ever seen in my life. (OK, so it's the ONLY ERP software = I've ever seen.) >The export option in MS-ACCESS does not work either. My ODBC does not >support this feature (OpenLink) I'm not surprised, I can't do this either. Nor should you need to. You should just be able to write an "Append" query, appending records from an Access local table into an Informix linked table. I do this almost every day now. >It seems my only option is to create a delimited text file from the >MS-ACCESS table and FTP it across. NOOOOOOOOOOOOOO!!! I'll do EVERYTHING in my power to save you from such a horribly UNIX-ish approach. DEATH TO ALL NON-GRAPHICAL UTILITIES I SAY... DEATH!! Sorry <ahem> you got me going. I guess it just stems from the time when I asked our UNIX Network admin to open a hole in the firewall for me. He spent a half an hour re-writing templates, and compiling blah blah. When I needed to open a second hole I said to him, "Isn't there a graphical utility for this?" knowing a LITTLE about firewall concepts. "Uhh, yeah, but I've never tried it." Three clicks and a drag later another hole was opened in a fraction of the time. Third hole needed to be opened (I can't do it cause they won't give me the passwords) and there he is at his little template again. <Eric SMACKS himself on the head> "Uggh, UNIX people..." (no offense intended of = course) >Any help would be appreciated. Let me know what your ODBC setup is like and maybe I can help out a bit. Eric Gavin Informix Newbie -- MS Access Guru