Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 583
  • Last Modified:

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
0
oxygen_728
Asked:
oxygen_728
  • 5
  • 4
1 Solution
 
DrTechCommented:
insert into TABLENAME (Symbol, Date, Price) values ('INTC', '06/07/1982', 15);


...and so on
0
 
DrTechCommented:
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!
0
 
oxygen_728Author Commented:
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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
DrTechCommented:
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.
0
 
DrTechCommented:
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.
0
 
oxygen_728Author Commented:
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 =)
0
 
DrTechCommented:
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.
0
 
oxygen_728Author Commented:
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
0
 
oxygen_728Author Commented:
Errr... in the comment above, I meant an ID column.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now