Big problems with tables

[http://www.visual-basic-data-mining.net/forum]

hello,

I'm new with MS Access and I'm having a problem with it;
in company where I work, they got installed UNIX on server, they are
programming with COBOL and they use FLEXGEN.
I was told to study RELATIVITY and MS Access and do some queries, but I
found out that linking tables with ODBC is to slow, becasue databases on
UNIX are huge and I couldn't change primary key, wich I found out was
crucial for Access working so slowly, then I tried importing tables and I
change primary keys as I needed to and then Access was working just fine. My
problem is that I need to update those tables every night. In Access
database that i created are 5 tables related, I was trying with macros, but
i can't update tables.

Any suggestions?

 

[Guest]

This can definitely be done, you may need a slightly different approach. If
you get slow response times from the Unix DB, try generating extracts that
you can apply to your Access DB. You will need some coding to handle some of
the housekeeping chores. Look at the TransferText action to load data from
an extract file.

 

[Guest]

Hi,

in addition ask the Unix DB Admins if they are able to ftp the extracts to
your drive, if not you can add some VBA in your Access DB to do the ftp from
there. There is an example from myself somewhere in this newsgroup how to do
that.

You should think of a trigger to check if the newest data is already
imported when opening your Access DB -> Table with just the date from the
last import. Then you can check on this and decide if the import needs to be
done or already happened on that day. -> Automatic import (and ftp if
necessary) not manual!

Cheers,
Bernd

 

[http://www.visual-basic-data-mining.net/forum]

thank you for your anwsers

but I don't understand what do u mean vith "generating extracts"?

I looked at TransferText and I did my own two examples of txt files and
imported them in MS Access, then I did the relationships and tried macro
with TransferText, but when macro did his job, there were duplicate values
in my database

then I use primary key on imported tables and I couldn launch macro, becasue
error appeared "violation of primary key"

I'll try more, but I'm open for any suggestion

 

[Guest]

Hi,

That sounds like that you get already the needed tables as text files (=
what we had called 'extracts') from the UNIX database and tried to import
theses text files with the TransferText command.

Unfortunately is it not possible just only to add the rows/data which is not
yet in your table in Access, the TransferText command will add the whole
files. Did you try to import to some other tables, use an 'unmatched query'
(see query wizards) and add only the found ones with an 'append query' or
'update query'?

Regards,
Bernd