Importing Large Table - Reducing Time

[Alan]

Hello Again all

Following some advice from here an a bit of though from myself I have now
got a way of importing a text log file to access which allows me to retrieve
much information in a formatted manner. (Many thanks for the previous help)

The table created however can creat and store upto 750000 rows worth of
information and during the import process the remaining database functions
are unusable.

Is there a method of allowing the import to work in the background while
allowing users to continue to use the database for other purposes (whether
that resul;ts in a performance issue or not)

 

[Pieter Wijnen]

Not by using DoCmd.Transfer...
You'd have to create your own & use DoEvents to yield or put the Import in a
separate Front End

Pieter

 

[Alan]

Pieter

Thanks for the tip ... think this effort may be well above my level of
knowledge to achieve and may have to accept the 2-3 minutes upload time

Thanks For you help

Regards
--
Many Thanks

Alan

Not by using DoCmd.Transfer...
You'd have to create your own & use DoEvents to yield or put the Import in a
separate Front End

Pieter

 

[Pieter Wijnen]

Creating a separate FE for uploading wouldn't take much effort?

Pieter

Pieter

Thanks for the tip ... think this effort may be well above my level of
knowledge to achieve and may have to accept the 2-3 minutes upload time

Thanks For you help

Regards

 

[Albert D. Kallal]

Just create a another separate mdb that when launched does the import. You
can then continue working in your current database....


After all, you likely split your database anyway...right?

In case you have not split I explain what this is all about here:

http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm

 

[Alan]

Albert

Thanks for this - I do indeed store the logfiles in another mdb, however
have been calling the update to a linked table from the original one and
using a DoEvents statement within a transformation process of the original
log file.

Can you confirm what you are suggesting is that I have all the processing
work done automatically by calling(opening) the second database from the
origninal and having the code execute through the second one on open ??

Regards and
Many Thanks

Alan

 

[Albert D. Kallal]

Can you confirm what you are suggesting is that I have all the processing
work done automatically by calling(opening) the second database from the
origninal and having the code execute through the second one on open ??

Well actually I was kind of assuming that the user would click on a shortcut
or launch the second database to run this process.

However, as you point out I see little or no reason why you can't simply
launch a anohter application to run this update code.
(so yes it is what I am suggesting)

You could even consider using automation to do this also (however you can't
use automation in the runtime to open another database, but you can in the
full version ).

 

[Alan]

Albert

Sorry to be a pest and perhaps appear somewhat dim, can you point me in the
right direction to make this happen.

I would want the process to appear transparent to the user, so they are not
aware of any other database opening and work with the primary database
without interuption.

The Do Events allows me to do this with slight performance degredation,
however I can see the benefits of doing something this way as I have a number
of other large tables that I would import if I can get this concept to work

 

[Albert D. Kallal]

I would want the process to appear transparent to the user, so they are
not
aware of any other database opening and work with the primary database
without interuption.

As mentioned you'll have to use something like a shell command.

The code would look something like

strCurrentDir = currentproject.Path

' path to msaccess is found via:
strShellProg = q & SysCmd(acSysCmdAccessDir) & "msaccess.exe" & q
strExe = strShellProg

strShellProg = strShellProg & " " & q & strCurrentDir & "myrunCode.mde"

Shell strShellProg, vbNormalFocus

"q" is set to a double quote

The above is a code snippet I've used to shell out to another database
program. Obviously the vbnormalFocus and a few other parts would be changed
, but the above Should give you some ideas as to what I'm talking about.

Obviously the database myruncode.mde will run our program code (into you'll
be using a startup form or perhaps the auto exec macro to run the code).

 

[Alan]

Albert

Many thanks for all your help ... I will get a chance to try this later on
this week and see what sort of a mess I can make !!

Many Thanks for all your time

Regards

Alan