Error when refreshing data control

I have a data1 control with an excel app that is attached to an msflexgrid. I have to set the database name dynamically. For some reason when ever i refresh the database it refreshes OK but i get an error "Object doesn't support property or method" what is wrong.
the excel app has no formatted fields.

Welcome to the forums...:wave:

What's the code that you are using and which line is showing the error ?

Data1.refresh

Is there a reason you are using a dreaded Data Control? (and worse, the DAO one which has been marked as "do not use" since 1998?)

For an explanation of why it is a bad idea, see the article Why is using bound controls a bad thing? from our Database Development FAQs/Tutorials (at the top of this forum)

I use the DAO because its easy to connect a data base dynamically. I have tried the ado and all the info I get off the net says have to write 100 lines of code to get it to connect. No one can give me a simple connection string that works. I spent days trying to get it to connect and it worked once. next time i ran the program it didn't work. I like simple.

data1.databasename = "name.xml"
data1.recordsorce = "sheet1"
is simple
besides my data bases are very small, maybe 35K

If you can show me a simple connection string for the ado I will worship you for life.

sorry it's an xls file not an xml

A basic ADO structure:

Code:

---

  Dim cn As ADODB.Connection
  Dim rs As ADODB.Recordset

  Set cn = New ADODB.Connection
  cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source= " & App.Path & "\database.mdb;Persist Security Info=False;Jet OLEDB:Database Password=abcd;"
  cn.Open

  Set rs = New ADODB.Recordset
  rs.Open "SELECT * FROM tblsubject", cn, adOpenForwardOnly, adLockReadOnly '~~~ We are using SQL query. Database FAQ section in this forum contains more details about the parameters used here.

  While Not rs.EOF
    List1.AddItem rs.Fields("subjectname").Value '~~~ Adding records to a ListBox 
    rs.MoveNext    '~~~ Move to the next record
  Wend

  '~~~ Clearing the memory and closing the connection when done
  rs.Close
  cn.Close
  Set rs = Nothing
  Set cn = Nothing

---

...:wave:

Hmmmm
I think all the replies I have receive in the past had all sorts or useless trash in it. This looks quite simple. I am feeling a need to raise my hands in praise.
But not just yet hehehehe
I think I can just change datasorce to an existing excel file and be OK ***** BUT *****
How do i connect this adodb to a msflexgrid so when I make changes to the grid it will save it to the excel file.

I am great full for what you have given me so far, Hope I'm not asking for you to write a novel here.

Since you seem to be the best informed I have talked to so far, I have one more question. This program I am writing is for the heating company I work for. If I pull it off the company can't lay me off. If I fail I will be the next one let go. Needless to say it is very important to me an my family.
My hope after using vb6, I will still be able to use this program in win7. I have spent hours reading articles that tell me windows has decided to support VB runtime files. Am I safe to assume it will work after putting in the 1,000 + hours I have so far.

If you want to use the recordset throughout your form, then try declaring these two lines:

Code:

---

  Dim cn As ADODB.Connection
  Dim rs As ADODB.Recordset

---

in General Declarations of the form.

And use these two lines, in the Unload event of the form or somewhere:

Code:

---

  Set rs = Nothing
  Set cn = Nothing

---

Quote:

---

How do i connect this adodb to a msflexgrid so when I make changes to the grid it will save it to the excel file.

---

I have never user MSFlexGrid as well as connecting the datasource to it, before. So, i don't have an answer to that question.

And for the Windows7 compatibility, I think there's some changes needed to certain codes. Like, if you are having code for manipulating Registry, then there needs some changes. Like that.
Check this link to know more about it: What do I need to do to make sure my program works in Windows Vista?

Good luck..:thumb:

This is where I loose everyone. I know VB has to have the ability to open an Excel spread sheet and work within in. No one can tell me how or what to use. What grid should i use.

In looking at your code I am guessing I can connect the grid with something like this

msflexgrid1.datasource = adodb

and keep it open until the page is exited. then use

Set rs = Nothing
Set cn = Nothing

if I have multiple databases open on the same form how do I label them

adodb adodc adode

or

adodb1 adodb2 adodb3

This is why i like the DAO control. It's so easy to connect to data grids, text boxes, dynamic databases. it's just easier all the way around. Is there a control that takes it's place that is just as easy to use. Even a third party control.

For tutorial about manipulating Excel using VB6, check this one: http://www.vbforums.com/showthread.php?t=391665

Here's a tutorial about the advantages and disadvantages of data bound controls: http://www.vbforums.com/showthread.php?t=416218
....:wave:

Thank you very much

how can I use adodb with combobox like this
rs3.Open("select * from employees", db)
With ComboBox1
.DataSource = rs3
.DisplayMember = "FirstName"
.ValueMember = "EmployeeID"
End With

.DataSource = rs3 the program stops on this line and i get err message
Complex DataBinding accepts as a data source either an IList or an IListSource
any one can help please