MacRena
asked on
Connect to Access database in ADO
<PAQ Readers - there wasn't a precise solution at the time I closed this question, but I'm going to continue posting to it as I learn things.> Mac
************************** ********** ********** ********
Hello Experts,
I want to create a connection to an Access database without using Data-Bound controls...
M$ VB 6.0 Programmers Guide page 453:
"...A Data control is in reality an instance of a class that is a data source, but classes that have been set up to act as data sources can be much more powerful than a Data control..."
So, I plan to create a Data-Aware class, set it's DataSourceBehavior to vbDataSource, then create a new Data-Aware class and set its DataBindingBehavior to vbSimpleBind (a Consumer class), then use that DataSource and Consumer as the bridge to the Access database.
What I want to do is have a global instance of my database available from which to create recordset objects as instances of the Consumer class (maybe create a different Consumer class for each table or combined views), similar to how Access has 'stored queries'.
Am I barking up the right tree? Will the connection be read/write or a snapshot? Of course I need it to be dynamic, but I'm not sure if the Consumer will be able to write back to the .MDB through the DataSource.
If there is a better way to connect to the .MDB, I'd be grateful for a pointer - I want to save time later on by spending more time planning.
Thanks very much!
Mac
**************************
Hello Experts,
I want to create a connection to an Access database without using Data-Bound controls...
M$ VB 6.0 Programmers Guide page 453:
"...A Data control is in reality an instance of a class that is a data source, but classes that have been set up to act as data sources can be much more powerful than a Data control..."
So, I plan to create a Data-Aware class, set it's DataSourceBehavior to vbDataSource, then create a new Data-Aware class and set its DataBindingBehavior to vbSimpleBind (a Consumer class), then use that DataSource and Consumer as the bridge to the Access database.
What I want to do is have a global instance of my database available from which to create recordset objects as instances of the Consumer class (maybe create a different Consumer class for each table or combined views), similar to how Access has 'stored queries'.
Am I barking up the right tree? Will the connection be read/write or a snapshot? Of course I need it to be dynamic, but I'm not sure if the Consumer will be able to write back to the .MDB through the DataSource.
If there is a better way to connect to the .MDB, I'd be grateful for a pointer - I want to save time later on by spending more time planning.
Thanks very much!
Mac
ASKER
ronenr,
Thanks for replying.
Are you saying that all I need to do is to create a global Connection? I like that - seems too simple, though.
How will I create Views for my reports? How will I connect the Forms to the DataSourse so that Textboxes I create on the Form will inherit an active connection to the database?
Hmmm, are you saying I can do this without using class Interfaces?
We usually create a global connection in the main form and the rest of the forms use it.
You can pass you connection to data controls and the connect you textboxes or dbcombos, etc. to those data sources.
You can pass you connection to data controls and the connect you textboxes or dbcombos, etc. to those data sources.
you might want to take a look at the book
Professional Visual Basic 6 Business Objects
Rockford Lhotka
Worx Press
ISBN ISBN: 186100107X
covers EXACTLY the scenario you are looking at.
Professional Visual Basic 6 Business Objects
Rockford Lhotka
Worx Press
ISBN ISBN: 186100107X
covers EXACTLY the scenario you are looking at.
ASKER
ronenr,
you said...
>>"You can pass you connection to data controls and the connect you textboxes or dbcombos, etc. to those
data sources."<<
I think you're referring to Data-Bound controls on the Form, aren't you? This would make sence given what I understand about the Data-Bound control, but can I do it that way without using this control?
Arthur_Wood,
I'm leaving right now to drive to the next county (really) to get that book. Hope it's got more than the rest of the slim-pickings out there :)
Mac
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Arthur_Wood,
This is a very complicated book. Can you give me a hint as to where they cover the scenario?
(are you referring to the Global Connection Object, or the Data-Aware Class Interfaces?)
ASKER
also, my form can see rsEmployees
(i put
MsgBox rsEmployees.RecordCount
in the Load event and it returned 837)
but i can't seem to connect my DataCombo to the Key field in the rsEmployees
**************************
Set cmbAkey.DataSource = rsEmployees
cmbAkey.BoundColumn = "Akey"
cmbAkey.DataField = "Akey"
cmbAkey.ListField = "Akey"
**************************
it's just empty. do i have to loop through the rs and load the elements into the combobox? from mdougan's comment in my previous question, i understood that i don't have to do that.
if i use a Data-Bound control (Data1) and connect a regular ComboBox to it, i first have to set the DatabaseName to the .MDB file, and the RecordSource of Data1 to my table.
but for the DataCombo, i'm using a RecordSet for the DataSource, and it's already set to my table, so why isn''t my DataCombo seeing the data?
ASKER
well, in the Form_Load event,
Do Until rsEmployee.EOF
cmbAkey.AddItem [rsEmployees]![Akey]
rsEmployees.MoveNext
Loop
returned an error "Method or Data Member not found" on AddItem. So, i think that i shouldn't have to load them (duh)
btw
Do Until rsEmployee.EOF
MsgBox [rsEmployees]![Akey]
rsEmployees.MoveNext
Loop
returns the 837 record's [Akey] strings.
grrrrrr
Well, you were on the right track, but the thing that determines the list is a separate "datasource", only this prperty is called the "rowsource", and the listfield has to be a field in the recordset pointed to by rowsource. Unfortunately, I don't think that you can set the rowsource at run time, you can only set it at design time, which means that you cannot set it to a recordset object. Instead, you'd have to have a data control on the form that is just used to fill this list.
Again, my recommendation is to not use "bound" controls at all. If so, I'd get rid of the data combo, and just use a standard combo box. Don't set anything for datasource, or datafield or anything for this list. Then, the code that you had above will work:
Do Until rsEmployee.EOF
cmbAkey.AddItem [rsEmployees]![Akey]
rsEmployees.MoveNext
Loop
Actually, after you load the list, if you want to set it's datasource etc to the recordset, you can do that (but I still don't recommend using this binding method)
Again, my recommendation is to not use "bound" controls at all. If so, I'd get rid of the data combo, and just use a standard combo box. Don't set anything for datasource, or datafield or anything for this list. Then, the code that you had above will work:
Do Until rsEmployee.EOF
cmbAkey.AddItem [rsEmployees]![Akey]
rsEmployees.MoveNext
Loop
Actually, after you load the list, if you want to set it's datasource etc to the recordset, you can do that (but I still don't recommend using this binding method)
#listening
ASKER
Arthur_Wood,
Oh, you mean the whole book is written toward the COM implementation. Thanks for turning me on to such a powerful book. It does deal with this Interface design as I need it to. I thought that you were referring to a particular page (I see that chapter 8 is all about this).
mdougan,
Yes, I see that I am going to have to make a decision about that data control in every form. I am very sure that I don't want to create a new connection every time, but I don't know whether VB Connection Manager will reuse them (keeping the overhead down), or if I will take a performance hit, and ground my design in DAO, which is what I'm trying to avoid. I also don't know if I'll be LateBinding my controls if I do that (need to study that more). And, finally, I really want to do something this big totally in the COM interface paradigm. Unfortunately, I'm (as usual) in a hurry to get this decision made and on to the actual work.
Regarding your recommendation of unbound fields, I am under a little pressure to follow the Access paradyme in this regard. This program is already in Access, and this project is going to be an upgrade for users who aren't going to want to click a "Save" button - they know and like the 'bound' interface and I have to stay with that. I might have the data write back in every control's _Exit event, but that's not a good design. I'm not sure how to implement that, but thanks for the advice.
<reading...>
Oh, you mean the whole book is written toward the COM implementation. Thanks for turning me on to such a powerful book. It does deal with this Interface design as I need it to. I thought that you were referring to a particular page (I see that chapter 8 is all about this).
mdougan,
Yes, I see that I am going to have to make a decision about that data control in every form. I am very sure that I don't want to create a new connection every time, but I don't know whether VB Connection Manager will reuse them (keeping the overhead down), or if I will take a performance hit, and ground my design in DAO, which is what I'm trying to avoid. I also don't know if I'll be LateBinding my controls if I do that (need to study that more). And, finally, I really want to do something this big totally in the COM interface paradigm. Unfortunately, I'm (as usual) in a hurry to get this decision made and on to the actual work.
Regarding your recommendation of unbound fields, I am under a little pressure to follow the Access paradyme in this regard. This program is already in Access, and this project is going to be an upgrade for users who aren't going to want to click a "Save" button - they know and like the 'bound' interface and I have to stay with that. I might have the data write back in every control's _Exit event, but that's not a good design. I'm not sure how to implement that, but thanks for the advice.
<reading...>
As with anything, there are trade offs. By including data controls on your forms to fill the contents of your listboxes, each user is going to probably have multiple open connections to the database at any one time, which is a waste of resources. Also, relying on a form-based control would make it harder to migrate your application to a web environment if you should ever decide to do that.
However, manually filling the lists, and handling the unbound loading of the screen and recordset takes a lot of extra code.
I understand keeping the interface in line with something the user's are comfortable with. Good choice. However, behind the scenes you don't have to actually have your textboxes bound to the recordsource. If you think about it, you only have to update the recordset when the user tries to navigate to a different record (or close the form). You will "navigate" to a different record if you do any of the MoveNext, MovePrevious, MoveFirst or MoveLast commands, or if you Find, Sort, Filter, Refresh or Requery the recordset. Normally, it's pretty easy to spot these items, and you can always just do a LoadRecordsetFromScreen and then Update before you move, find etc.
You don't have to do it after each field is updated.
There is no right or wrong answer, it's what works best for your environment. However, I would say that after working on several large Enterpise sized applications, I've hardly ever seen one that uses bound controls. And the ones that do, usually suffer a lot of performance problems, particularly when there is a large user base.
However, manually filling the lists, and handling the unbound loading of the screen and recordset takes a lot of extra code.
I understand keeping the interface in line with something the user's are comfortable with. Good choice. However, behind the scenes you don't have to actually have your textboxes bound to the recordsource. If you think about it, you only have to update the recordset when the user tries to navigate to a different record (or close the form). You will "navigate" to a different record if you do any of the MoveNext, MovePrevious, MoveFirst or MoveLast commands, or if you Find, Sort, Filter, Refresh or Requery the recordset. Normally, it's pretty easy to spot these items, and you can always just do a LoadRecordsetFromScreen and then Update before you move, find etc.
You don't have to do it after each field is updated.
There is no right or wrong answer, it's what works best for your environment. However, I would say that after working on several large Enterpise sized applications, I've hardly ever seen one that uses bound controls. And the ones that do, usually suffer a lot of performance problems, particularly when there is a large user base.
ASKER
hmmm, i need to set the DataSource of this DataSource class to a DataEnvironment.
but i don't want to use a DataEnvironment, i want to use a .dsn file so i can rewrite it depending on the directory that the user installs the data file.
anyone know how to set the DataSource class to a .dsn?
i see that the DataEnvironment is simply a graphical representation of a .dsn, (like a .udl) so it shouldn't be that hard.
ASKER
Thanks to all. I will continue to post to this question as I learn things (and hope EE gets the Order-By-Date thing fixed for the PAQ readers) so feel free to unsubscribe if you don't want to read my ramblings. :)
Arthur, I will post some points for your suggestion about the book. It's going to take me a while to put it all together (as usual, the book is one long programming solution and uses classes ect. that were developed in prior chapters, so you can't just jump into the middle and get a solution to a particular question).
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.Open "Provider=Microsoft.Jet.OL