Addnew (1 Viewer)

[bob makepeace]

Hi,

I was wondering if there is a quicker method
of appending records from a recordset to a table other than going through each field using the .addnew method.

It may be a silly question but my application
is taking too long loading data when
used over a phone line.

thanks

bob

 

[Pat Hartman]

I'm not sure why you have coded this yourself. But, a bound form would be faster if you're working with one record at a time or an append query will usually be faster than a similar code loop to append multiple records.

 

[bob makepeace]

Hi,

I'm not using linked tables as I have
about 200 users and I've seen systems crash
and have a lot of problems using them with more than 20 users.

I'm bringing down data using jet and recordsets. It's works fine over the network but it's slow over a telephone line.

I guess there is nothing more I can do to speed things up.I can't use SQL to append
from a recordset I assume.

thanks for replying

bob

I guess

[This message has been edited by bob makepeace (edited 10-03-2000).]

 

[MHM]

Faster than .AddNew and then For Each fld In Recordset.Fields... might be the this SQL:

SQL="INSERT INTO ThisTable SELECT * FROM ThisTable WHERE ....;"

Or you could do:

SQL="INSERT INTO ThisTable VALUES ....;"

Mic

 

[bob makepeace]

Hi,

Thanks.

The thing is I'm transferring data
from a server recordset to a local table
so I'm bring down the data into a recordset
and doing an .addnew to a local recordset.

Can I do that in SQL, ie. insert from one
database to another? Would it be quicker?

thanks for your help

bob

 

[bob makepeace]

Hi again,

Have looked up how to use SQL with an extenal database and tried it.

Unfortunately it is far slower than when
using a recordset because the data is
on a network I guess.

Thanks for trying

bob

 

[Pat Hartman]

Are you saying that you are reading a text file in code? If so, why not try using the TransferText Method and let Access do the work. Depending on the structure of the text file you might need to import the file ONCE manually to create an import spec that you can then refer to in the TransferText Method.

 

[bob makepeace]

Hi,

No, I'm connecting to an access database
and bringing down a selection of data
using recordsets.

Linked tables don't work with 100+
users and certainly not over a telephone
link I'm afraid.

I tried using SQL for external databases
but that's even slower.

The system is very quick, even over a WAN,
but can't think of anything to speed it
up over a telephone link.

thanks very much for you help

best regards

bob

 

[BillP]

This sounds like it is causing you some grief. I'm not sure if this will help because I don't know enough about your application. Can you buffer the data coming in? Would that help? You could create a user defined type, and create an array of this new type. The array can grow dynamically using "redim" "preserve". Once you have all of your data, you can then iterate through it to add it to your table. It might be a better way to deal with the volume of traffic.

 

[Atomic Shrimp]

I have a situation where field operatives need to have their database updated from a central table and also need to be able to send back updates using only GSM phones (very slow and sometimes unreliable), in the end I did it like this:

-On the remote computer, Make a folder called 'C:\in' and create a shortcut on the desktop for it.

-From the central database, email an excel spreadsheet (this can be fully automated) to the remote workstations, (it should be possible to minimise the size of this if you work it so that only new/changed records are sent.

-On the remote workstation, when the email is received, the user simply drags the excel attachment out of the email and drops it onto the shortcut to c:\in.

-On startup of the remote database, it looks in 'C:\in' for the spreadsheet, if it finds it, it imports and applies the data to it's own table(s).

A similar process can be used to get data back to the central database. One of the advantages of this method is that the remote station need only remain connected for long enough to download the email (also the remote computer connects to an ISP rather than direct to Head Office)

HTH

Mike

 

[bob makepeace]

Hi,

Thanks, I'll look into the arrays idea.

Re emailing data, the system has been live
6 months now and it would be too big a change I'm afraid.

There isn't a great deal of data being
brought down to the PC, it only takes
about 10 seconds to log on over the network.
Trouble it takes 10 minutes to do the same
over a telephone link which one senior
user isn't happy with!

thanks again for your suggestions

bob

[This message has been edited by bob makepeace (edited 10-06-2000).]

 

[BillP]

This is probably clutching at straws, but if the process is slow over the phone lines, it might be because of the overhead associated with access. Maybe it might be quicker converting to a delimted text file for the transmission and then importing at the other end. It sounds like an awful lot of trouble for something that should be so simple, but it might be worth experimenting with.

 

[bob makepeace]

Hi,

Thanks. It's worth thinking about.

I've managed to cut login time down to
6 minuites - I'll just have to wait and see
if this senior manager wants to escalate
things.

We may have to move to a SQL server back end
just to keep him happy!

thanks a lot for all your suggestions

best regards

bob

 

[David Mack]

We tackled the same problem using the same idea as BillP. The idea to speed up dial in user updates to a main db seemed pretty simple--minimize the size of the transferred file. Keep it text. Avoid sending an Access app. You will minimize size by a factor of at least 100.

Output the data to a delimitted file and you will realize a significant transmission time decrease. It is not difficult to automate the entire process with Outlook.

Dave Mack

 

[bob makepeace]

Hi,

Thanks David. It looks like we
will be able to keep this particular
senior manager happy by using an ISDN line.

The system is then just as quick as over the network.

Thanks for the advice though, I'll bear it
in mind in future.

best regards

bob