i face this problem often that if a adodb connection is made using a
Adodc control(And connection string and recordsource specified at run time) then it's recordsource cannot be changed at any other point in the program, by assigning it to a new value.Even if i do write the conn. string & recordsource statement again, the recordsource remains same.

In that case i have to use another Adodc control that has the new recordsource.so my form gets crowded of adodc control's.

Can there be a way to just assign the new recordsource?
mind it i don't use define any ado objects like connection, recordset, command as i have trouble understanding these concepts. plz give an easier way.   

[Edited by aak_neo on 22-02-2007 at 10:07 PM GMT]

____________________________
You never see past the choices you don't understand

Hello. You don't post any code, so I can't figure out why it doesn't work. I can just drop some reflections.

Basically, an Adodc control is nothing but a recordset with four buttons and a textbox. So you're free to change its RecordSource property at runtime. Only, take care to set it when its connection is closed yet - I mean, before opening the connection.
About the ConnectionString property, you can right click on the Adodc control to copy its connection string and then paste it into your code. The Adodc connectionstring wizard is useful this way to create the connection string without syntax mistakes.

I also wonder why you are using an Adodc control (surely you need it), but I suggest you to consider using a pure Recordset object instead, and implement your own custom buttons/commands to move thru it. You can find plenty of topics about it, with sample code, by simply typing "Recordset" into the Search page of this forum.

Hope it helps.

____________________________
Real Programmer can count up to 1024 on his fingers

Well i have gone way ahead with my project so can't revert to using pure recordset object due to scarcity of time.i used the adodc
for simplicity as i read it in this forum somewhere.
this what i'm trying to do,

Private Sub Form_Load()
  With Adodc1
       .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
           App.Path & "\Database1.mdb;Persist Security Info=False"
        .RecordSource = "select * from chdetails order by ID DESC"
   End With


   With MSChart1
  Set .DataSource = Adodc5
   .ShowLegend = True
  End With

so my mschart1 displays all records in the database1 graphically
then on click of a button i have to search a record in the database and show its graph as follows
-->dat(0) is date, dat(1) is time used to search the specific record in the db
Private Sub Command1_Click()

With Adodc2
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
           App.Path & "\Database1.mdb;Persist Security Info=False"
        .RecordSource = "select ID from chdetails where (DATES='" & dat(0) & "'" & " AND TIM='" & dat(1) & "')"
   End With

With MSChart1
  Set .DataSource = Adodc2
   .ShowLegend = True
  End With
end sub
so now the chart uses adodc2's record source to display so i get a single record display on chart for specified date & time.

what i tried earlier was use the same adodc1  and change the recordsource only, and set chart's data source to it again,but still chart showed all the records.

is there a way to close adodc1's connection and change recordsource?? if i will be able to do that then it will reduce many such adodc's on my ENTIRE project.

____________________________
You never see past the choices you don't understand

quote:

---

aak_neo wrote:...is there a way to close adodc1's connection and change recordsource?

---

THANKS A LOT, Mr. yronium. for that suggestion of recordset object. actually adodc control doesn't have any active connection property,but recordset has.  

1. i studied recordset and now i removed all the 3 adodc's and implemented the application with a single recordset. its the complex look of using conn & recordset object that had scared me from using them.but not any more.

i was makin a chart viewer that accepts start-id & end -id from user and displays the data in between them. so there are 3 display instances of the same chart
                          1.displays start-id record (cmd1 click)
                          2.displays end-id record (cmd2 click)
                          3.displays  data in between them(cmd3 click)
when i press 1,2,3 it displays correct records on database except the axis, series labels cmd3.
is it due to the recordset object? i have used this code for cmd3:

Private Sub Command3_Click()
sql = "SELECT * FROM chdetails WHERE ID BETWEEN " & start_id & " AND " & end_id
On Error Resume Next

rs.Open sql, connString, , , adCmdText
rs.Requery
With MSChart1
  Set .DataSource = rs
   .ShowLegend = True
  End With
  Set MSHFlexGrid1.DataSource = rs
  Text4.Text = end_id - start_id  ' no.of records displayed
  rs.Close
  End Sub

2. Another form of mine has 3 charts displaying different fields from same db.so they have to have their data source with 3 different recordsets correct? so again i have used 3 adodc's as their data sources can't i have multiple recordset objects to same connection object coz when i tried assigning another recordset object it doesn't work at all. then again will i need a new connection?    

____________________________
You never see past the choices you don't understand

No, you do not need three Connection objects to open three recordsets on the same database.
For instance, assuming you are using a MSAccess database named MyDb.mdb, you need to use one single Connection object, say it's named conn1, to connect to it. You keep it open, so every time you need to retrieve some data from the MyDb.mdb file, you can open a recordset using the same open connection conn1. So, if you need to retrieve all the records from the table named Table1 you open a recordset, named rs1, and if you need also to get the first ten records from the Table2 table you open another recordset, rs2. If you don't need anymore to keep the first recordset object open, you can close and re-open it on second table's top 10 records.
But the connection is still the same: you will need another one if you have the need to operate into another .mdb file in the same time the first one is still connected. In this case you'll declare another Connection object and open it.

Say you got to copy in the table ExtractedItems, contained in the ExtractedData.mdb file, some records from ProvidingTable and AdditionalTable contained in the OriginalData.mdb file, and take a log of it into another table, LogTable, into the OriginalData.mdb file.
The pseudo-code would be like following:

Dim cn1 As ADODB.Connection, cn2 As ADODB.Connection Dim rs1 as ADODB.Recordset Dim cnt, total ' when you need to connect to the first .mdb file, you open a connection ' (usually during application startup) cn1.Open on the file "OriginalData.mdb" ' open the recordset on the first table to read rs1.Open on table "ProvidingTable", with cn1 ' now you need to connect to the second .mdb cn2.Open on the file "ExtractedData.mdb" Loop thru rs1     cn2 insert into ExtractedItems table, from the first table     rs1.MoveNext     cnt = cnt + 1   ' increase records counter End loop rs1.Close ' open the recordset on the second table to read rs1.Open on table "AdditionalTable", with cn1 Loop again thru rs1     cn2 insert again into ExtractedItems table, from the second table     rs1.MoveNext     cnt = cnt + 1   ' increase records counter End loop rs1.Close ' get the updated recordcount from the ExtractedItems table rs1.Open "Select Count ExtractedItems", with cn2 total = rs1.Fields(0).Value rs1.Close and Nothing    ' we don't need anymore the recordset cn2.Close and Nothing    ' no need to keep the second .mdb connected anymore cn1 insert into LogTable(RecordsAffected, TotalExtracted, Date) values (cnt, total, CurrentDate) ' when you finished working on the first .mdb, you can close and destroy it ' (usually when unloading the application) cn1.Close and Nothing

Hope it's clear. There are many other samples on this site. Have a search.

About the chart I don't know it as I never used it, but the connection code seem to be okay, so I argue your problem belongs from the chart control. Hope somebody else can say more.

____________________________
Real Programmer can count up to 1024 on his fingers

Thanx for the elaborate example. it made my idea of the recordset
much more clearer.but it hasn't quite solved my problem.Actually i didn't frame my question properly? i.e

can i have multiple recordsets to the same table?

In ur example u are either working on two different db or two different tables of same db. but nowhere u have shown a different recordsets to same table. i know u will say then i should use other
coding manipulations of same recordset to retreive data but a chart control does nothing but read a datasource(recordset) directly. so my question is simple that,

Is it possible to have two recordsets rs1,rs2 such that they have same connection string & access the same table in the db?  

sql1 = "SELECT * FROM chdetails WHERE ID BETWEEN " & start_id & " AND " & end_id
sql2 = "SELECT * FROM chdetails WHERE ID=2582"

rs1.Open sql1, connString, , , adCmdText
rs2.Open sql2, connString, , , adCmdText

notice both access same table chdetails.
sorry for the trouble but it gave me a method to transfer data from one db to other.

____________________________
You never see past the choices you don't understand

quote:

---

aak_neo wrote:Is it possible to have two recordsets rs1,rs2 such that they have same connection string & access the same table in the db?  

sql1 = "SELECT * FROM chdetails WHERE ID BETWEEN " & start_id & " AND " & end_id
sql2 = "SELECT * FROM chdetails WHERE ID=2582"

rs1.Open sql1, connString, , , adCmdText
rs2.Open sql2, connString, , , adCmdText

notice both access same table chdetails.

---

u r right i can have two different recordsets to the same table. i used a flex grid this time to check out whether the second recordset got created it indeed gets created & i can see it on flex grid.

but somehow the chart doesn't show this & gets blanked out completely so i suppose i will be stuck with the same 3 adodc's as
1.charts not working for the new recordset as flex grids do
2.my table is updated constantly so updating all recordsets becomes a problem.

also,a clone is just the same recordset so i don't think it can show something different than the parent recordset like only select no. of fields, records etc.

i m new to the forum so plz tell me if the topic is solved do i
have to click 'solved'. does that mean end of discussion as there are many questions i would like to ask you but not related to this problem only.

thanx for helping me out so much  

____________________________
You never see past the choices you don't understand

quote:

---

aak_neo wrote:...but somehow the chart doesn't show this & gets blanked out completely so i suppose i will be stuck with the same 3 adodc's as
1.charts not working for the new recordset as flex grids do
2.my table is updated constantly so updating all recordsets becomes a problem.

---

quote:

---

...also,a clone is just the same recordset so i don't think it can show something different than the parent recordset like only select no. of fields, records etc.

---

quote:

---

i m new to the forum so plz tell me if the topic is solved...

---