asked on

VB.net MS ACCESS database update syntax (inserting data) (easy)

Hi. My Database Table (MS ACCESS) looks like this.

Symbol Date Price
INTC 06/07/1982 15.00
INTC 06/08/1982 15.01
INTC 06/09/1982 15.05
MU 06/05/1985 1.00
MU 06/06/1985 1.01
MU 06/07/1985 1.05
....

As you can see there are 3 columns.

I have about 600 megs of data like this.

I want to add records into this table...

For Example, I want to add:
INTC 06/10/1982 15.06

I have been using DB.Execute(cmdstring, , ADODB.CommandTypeEnum.adCmdText)) where DB is my database connection.

I am only familiar with command strings which extract data from my database into a recordset, such as:
"SELECT Symbol,DDate, Price FROM Full WHERE Symbol = 'INTC' And DDate >= #06/07/1982# And DDate < #06/07/1983#"

I assume there is a command string which will update the database...

Unfortunately I haven't found a good source for these SQL statements...

Thanks for your help!

Brian

ASKER CERTIFIED SOLUTION

DrTech

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DrTech

Another approach:

dim rs as new adodb.recorset

set rs=db.execute("select * from TABLENAME")

rs.addnew
rs("Symbol") = "INTC"
rs("Date") = "06/07/1982"
rs("Price") = 15

rs.updatebatch

You might want to test which method performs the best, since you have 600 Megs of data.

You also might consider, if MS Access is the right database to use with such large amounts of data!

oxygen_728

ASKER

Well, It's fast at what I want it to do.

I just extract a series of dates and prices for 1 stock quote at a time between 1 date and another.

I'm curious... will inserting into the database put the "inserted" data into the right "spot?"

My database is sorted first by Symbol, then by Date.

Does it keep the sorting, I wonder?

Because... I forsee a problem with a slowdown in qeuries (of extracting data from the database) in the future if it inserts records all out of order...

Thanks for the help!

Brian

DrTech

You have to understand, that the records in a table are never stored in any particular order. You have to sort the data with the order by, statement when you select it out of the database. Thus, there is no such thing as "inserting rows in the right place in a table".

I know that it seems like Acces is always storing new records in the bottom of the table, but that is just a coincidence. If you compact the database, the records might turn out different. This is how all databases works (at least the ones i know of).

If your queries perform badly, you might be able to improve performance by adding som indexes on the relevant columns.

DrTech

oxygen_728

ASKER

Ok thanks DrTech

I've considered adding a primary key, but that requires an ID row in my table... which adds a couple hundred megs to my file size =)

DrTech

You should ALWAYS have a primary key on your table!

Couldn't you use a combined key? Isn't the combination of Symbol and date unique?

That way you will not have to add a new column. However I suspect the filesize to increase anyway, because of the index that will be created. But I think it will make your queries much faster.

oxygen_728

ASKER

For all intents and purposes, my queries are virtually instant on a 1.7 ghz machine...even queries which extract 3000 records. So the only thing I'm concerned about is the database size...

Until my queries take more than half a second on the average machine, I'll probably lean towards the option with smaller file size.

If they do start to slow down, I would take your advice and implement a combined key.

Thanks very much for your help DrTech!

Brian

oxygen_728

ASKER

Errr... in the comment above, I meant an ID column.