How to copy selected rows into another table 3

[eman6]

(1)
What's the easiest way to copy selected rows from a table (connected to MS Access DB via ADO) into another table (Also conected to MS Access via ADO), for the purpose of creating sample grouping of data.

(2)
May I also ask your recommendation of a book (or a web page) that clearly explains programming with MS Access and Visual Basic 6.
I have difficulties understanding how relationships between tables exactly help accessing data. I understand avoiding redunduncies, but still unclear to me how the relational linking actually works in favour of accessing data.

(3)
I read somewhere at Microsoft's web site that ADO does not support Dynamic Cursor Type. Is this true? How bas is this?

Many thanks in advance.

Eman_2005
Technical Communicator

http://www.smartistek.com

 

[topci]

Hallo,

in case of your question, i will suggest you next:
Try with two ado recordsets

Set ConAcces = "DNS=... here put the name of DATAbaseconnection"

Quiry = "SELECT x, sum(amount) as somefield FROM SampleTable Group by x order by x"
rsAdo1.open quiry, ConAccess
Quiry = "select * from NewTable"
rsAdo2.open Quiry, conAcess

Do while not rsAdo1.Eof
rsAdo2.AddNew
rsAdo2(0) = rsAdo1(0)
rsAdo2(1) = rsAdo2(1)
rsAdo2.update
rsAdo1.MoveNext
Loop

rsAdo1.Close
rsAdo2.Close

Both table need to be created in Acces...

I hope this will help you to get some Ideas how to fix your problem.

Lp,DaT

 

[eman6]

Cool, I shall try this.
I guess for this part:

    rsAdo2(0) = rsAdo1(0)
    rsAdo2(1) = rsAdo2(1)

I can loop through the fields.

However, I would appreciate it very much if you help me understand what this part is actually doing:

"SELECT x, sum(amount) as somefield FROM SampleTable Group by x order by x"

Thanks a lot.


_______________________________________

Eman_2005
Technical Communicator

http://www.smartistek.com

 

[fredericofonseca]

Do not use two recordsets as sugested, as it is slower, unless you need to do something with the records between reading and inserting into the second table.

If both tables are on the same database just use a standard SQL "insert into my destination_tbl select ... from my source_tbl where ...".


Also regarding the select you are querying. if you have a group by you don't need a order by as the group already does that.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[eman6]

Grazie Frederico per il tuo suggerimento

However, I am not an sql guru at all, which means I am unfortunately not familiar with the statement you suggested.

Then what's the difference between group by and order by.
I understand order by, but not group by. What does it exactly do?

_______________________________________

Eman_2005
Technical Communicator

http://www.smartistek.com

 

[fredericofonseca]

Insert into" is a way of inserting values into a table.

The values inserted can be either "hardcoded"
e.g.

insert into my_tbl values ('My name is john', 'My surname is smith') (this inserts a single record into table my_tbl, placing "My name is john" on the first field of the table, and "my surname is smith" on the second field.


or it can be the result of a sub-select which is the example I have you .
On this case you wish to insert into a table (destination_tbl) the records resulting from a select on another table (or set of tables).


So if you have ANY select statement, you can output the result into another table by using the "insert into" example I gave you.


As for the group by.

If you have a table (invoices) as follows.

customer_id, invoice_number, invoice_total
1, 1, 12300
1, 2, 200
2, 3, 1230
3, 4, 2000
2, 5, 2500

If you do a "select * from invoices, order by customer_id"
your output will be
1, 1, 12300
1, 2, 200
2, 3, 1230
2, 5, 2500
3, 4, 2000

Now if you use the group by you will be telling the SQL engine that you with to have the results "grouped" by a particular field.

so if you do "select customer_id, sum(invoice_total) from invoices group by customer_id) your final output will be
1, 12500
2, 2730
3, 2000

e.g. the SQL engine groups the records selected under the common field, and then outputs single rows for each unique value of that field.


(and I'm not Italian, but Portuguese, but I appreciate the italian gesture. Do understand it well.)




Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[eman6]

Frederico
This is great! Thank you very much indeed.
Your explanation clarifies all.

I have a comment though, if I may:
It is a shame that database technology does not provide a facility to insert a whole record at once.
You still have to insert field by field :-(
Is this a Microsoft Access limitation? Is such a thing possible in Oracle, for example? I just wonder.
Because if I was the person who engineered a database the first place, I would certainly think of providingg for record insertions. I am sure you would too, wouldn't you.

Thanks again. I appreciate your help whether you are Italian, Portugese or whatever else


_______________________________________

Eman_2005
Technical Communicator

http://www.smartistek.com

 

[fredericofonseca]

Where did you get that idea

You can insert a full record, or if you use a select you can insert a whole bunch of them.

If you wish and if your table design allows it you only need to insert the required fields to comply with ny constraint.

e.g. on the above invoices table I could insert a record as follows

insert into invoices (customer_id, invoice_number) values (1,22), omitting field invoice_total.

At a later stage I could do
update invoices set invoice_total = 88333
where customer_id = 1 and invoice_number = 22

Either way I am populating a group of fields, but I am ALWAYS updating/creating a record



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[eman6]

Sorry, I'm a little confused.
I got this idea because when I have a recordset called myRec and I type a . after it, I don't see a property called record, like this:
myRec.record(recordnumber)

You see what I mean?
in your example:

   insert into invoices (customer_id, invoice_number) values (1,22)

You are inserting the fields customer_id and invoice_number.
I know you are inserting a record anyway, but you have to populate it field by field.

If I have twenty fields in a record and I want all of them inserted, I don't want to waste time stating each field.

I want all the fields from this record to populate the corresponding fields in the new record without having to name the each of the fields explicitely.
I don't even want to have to loop through the fields.

Let us say I want to replace a record from one recordset by a record from another recordset. I want to be able to do something like:

  SecondRS.Record(7) = FirstRS.Record(5)

Or at least to add the record as a record.

   AddRecord in SecondRS = FirstRS.Record(5)

But this does not exist, or does it??

_______________________________________

Eman_2005
Technical Communicator

http://www.smartistek.com

 

[fredericofonseca]

When doing an insert as "insert into ..." you don't need to name the fields unless one of the following occurs.

1- You are not inserting values into ALL the fields.
See my first example "insert into my_tbl values ('My name is john', 'My surname is smith')"
No need to mention fields here as I was assuming a table with only two fields, name and surname

2- The order of the values you are inserting does not match the order of the fields on the table.
New example.
table customer (
customer_id numeric
customer_name char)

insert into customer values ('my_name', 123) will fail because the parameters type do not match.
So a solution is to do
insert into customer (customer_name, customer_id) values ('my_name', 123)

The above is standard SQL. Nothing to do with ADO or any other type of data provider.


As for your particular ADO problem there is no way that I know of to do that without some code.

One option is to create a function where you pass the source recordset, the destination recordset and the function will do that for you.

Sub copyRSRecord(ByVal rsSource As ADODB.Recordset, ByVal rsDest As ADODB.Recordset)
Dim F As ADODB.Field
    rsDest.AddNew
    For Each F In rsSource.Fields
      If F.Type <> adChapter Then rsDest(F.Name).Value = F.Value
    Next F
    rsDest.Update
End Sub

The above assumes that both recordset are the same.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[eman6]

OK, now all is clear and shiny

That's all for now, Frederico, thank you again very much for your patience and explanations.

_______________________________________

Eman_2005
Technical Communicator

http://www.smartistek.com

 

[Jasen]

In it's simplest form, you could write something like this:

SELECT * INTO MyTable2 FROM MyTable1

And that would grab everything from MyTable1 and insert it into MyTable2.

If your target table is going to be in a different access database, you make it like:

SELECT * INTO MyTable IN 'db2.mdb' FROM MyTable

And of course, you can filter what rows get inserted by using WHERE's and etc.

________
Remember, you're unique... just like everyone else.

 

[fredericofonseca]

Carefull with this last form of sql.

It is what Access call a Make Table query and is not portable.
Also implies that the new table does not exist (though if running with warnings off this will be deleted).

Ansi standard is "insert into ... select from ...)

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Jasen]

Right, my version combines "create table" and "insert into" into a single statement. If you already have an existing destination table with data in it, using the "insert into" as per Frederico is the way to go.

________
Remember, you're unique... just like everyone else.

 

[eman6]

So, this means I can use the

SELECT * INTO MyTable2 FROM MyTable1

technique the first time I create a new table from selected rows of the existing one. After that, I have to use the Insert for subsequent such operations.

That's useful to know actually for other cases I have elsewhere
Thanks for the hint jasen.

However, so far no such solution that works with ADO :-(

_______________________________________

Eman_2005
Technical Communicator

http://www.smartistek.com