?
Solved

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

Posted on 2003-03-27
9
Medium Priority
?
574 Views
Last Modified: 2007-12-19
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
Comment
Question by:oxygen_728
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 6

Accepted Solution

by:
DrTech earned 360 total points
ID: 8223084
insert into TABLENAME (Symbol, Date, Price) values ('INTC', '06/07/1982', 15);


...and so on
0
 
LVL 6

Expert Comment

by:DrTech
ID: 8223098
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
 

Author Comment

by:oxygen_728
ID: 8223149
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
Industry Leaders: 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!

 
LVL 6

Expert Comment

by:DrTech
ID: 8223176
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
 
LVL 6

Expert Comment

by:DrTech
ID: 8223177
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
 

Author Comment

by:oxygen_728
ID: 8223208
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
 
LVL 6

Expert Comment

by:DrTech
ID: 8223240
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
 

Author Comment

by:oxygen_728
ID: 8226684
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
 

Author Comment

by:oxygen_728
ID: 8226709
Errr... in the comment above, I meant an ID column.
0

Featured Post

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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question