Link to home
Start Free TrialLog in
Avatar of MacRena
MacRenaFlag for United States of America

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


Avatar of ronenr
ronenr
Flag of Mexico image

You can try using ADO:

Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & "Data Source=path_Of_The_Access_File.mdb" & ";" & "Jet OLEDB:Database Password=mypassword"



Avatar of MacRena

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 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.
Avatar of MacRena

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
Avatar of mdougan
mdougan
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of MacRena

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?)

 
Avatar of MacRena

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?
Avatar of MacRena

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)
Avatar of vinnyd79
vinnyd79

#listening
Avatar of MacRena

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...>

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.
Avatar of MacRena

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.


Avatar of MacRena

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).