multiple appends

I'm using ADO to connect to an Access backend.

I've got multiple sets of values (records) to append to my database. Is there a way I can do this all in one query or do I have to do one query for each record?

Thanks
jakyra

one by one unfortunately, you may be able to use a loop to speed things up?

can you give me a rough idea of what your table looks like and the what the update query you are running looks like please

We do reviews of children's books. So we have bibliographic data (although authors/illustrators are in a many-to-many relationship with the books): Title, ISBN, publisher, etc; review data: reading level, genre, culture, and reviewer; and state information: editing status, etc.

The update query will be pulling data from an array of classes and, for one, will look something like this:

INSERT INTO Table( Title, PublicationDate, Publisher, ISBN, OriginalDate, Pages, Price )
Values ("Title1" , 2003 , 5 , "0-12-345678-9", 0, 32 , 16.99)

Thanks
jakyra

What are you using to get this information to update, where is it coming from a web page (asp) or a spread sheet (excel), can i see yopu sql coding for the update, there is a alternative wehere you could generate a macro in access but need to know how you data is be prepared to be imported

This isn't being done in Access; This is a web app. It's data coming from a class modual.

I haven't written the update page yet because I wanted to see if I could do something like load the data into an array and append the whole array at one time.

I can't use an Access macro. I'm on the server.

Can i see the first page of where you are entering the data into so i can get an understanding of what you are trying to put into the tables, because if you are wanting to enter a mass amount of data you have got to input it some where, this is what i need to see.

I can't show you the code because it's not written yet.

I'm not getting the data that I upload to the database from a form. I'm getting it from a download from another website (the Library of Congress). My users enter ISBNs, I validate them, then I send the ISBNs to the LOC and I get back a datastream.

Then, I take the data from the LOC download, organize it, clean it up, and put the data into a class. I get several books at one time, so I have an array of these classes.

I get the data out of the classes with methods like books[i].GetTitle, books[i].GetPages, books[i].GetYear, etc. and loop through the array.

the easist way to do this is to right a simple vb script to get you download data into csv format and then upload it to the database that way. i have done something like this a few years ago ill see if i can dig out the script

Thanks!

You can also use a batch update where you open a recordset to the data, add all your new rows, then update the database. In this case you'd be using a recordset object instead of using SQL strings to update the database (assuming you're using ASP).

So I've got 3 choices
1) open the recordset and do "manual" appends (I've done this in VBA)
2) write the information to a file, then upload that to the recordset. (Haven't done this)
3) do successive append queries.

right?

My question is now: how expensive are these methods? What am I looking at for server load? My understanding is that holding a recordset open is quite expensive.

I'm looking at up to 40 records for one group of data, and 80 for the other.

Thanks
jakyra

<blockquote id="quote"><font size="1" face="geneva, verdana, arial" id="quote">quote:<hr height="1" noshade id="quote">Originally posted by jakyra
[I'm looking at up to 40 records for one group of data, and 80 for the other.
<hr height="1" noshade id="quote"></blockquote id="quote"></font id="quote">

How many people are running an update at one time and/or how often are they doing it?

I have a pretty low volume site.

There might be a few times that more than one submission will occur simultaniously, but I think that quite often there will only be one. I imagine that there will be some peak times where I could get as many as 8 running it at one time, but those would be low volume entries (1-2).

For each person, the submissions should be happening more on a monthly/bi-monthly basis. I would be surprised if people use it more often than that. Some people will only enter once a year.

jakyra

I wouldn't worry about the overhead of either of the methods with that kind of load. Not that it isn't good to think about, but probably not worth investing the time in optimizing.

Yeah, this database is a lot about me practicing good technique. It's a low load, special interest site that I don't want the general public even knowing about. I have security, but it's certainly crackable. But part of the purpose for me is learning good techniques so that when I move up to a larger system with more people I have high efficiency, etc.

Thanks
jakyra