ADO Connection
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 11 of 11

Thread: ADO Connection

  1. #1
    Join Date
    Dec 1999
    Location
    CA, USA
    Posts
    4

    ADO Connection

    O.k. guys/gals ADO experts. I'm trying to get into VB6 ADO databasing. I'm from the old school and I want all the database commands to work through all the formS included in the Project.

    I've been told to just create the ADO Data Control (in the startup form)and I have. BUT (there always is, isn't there !) then I can't use AddNew /Find /FindNext and a couple of others. AND it only works if I keep re-creating the control on each subsequent form, no universal method I can see.

    Then I've been told to use [Dim cnn as ADODB.Connection] and all the code that goes with this method (and there is a LOT). It then produces a connection to the database but has no AddNew/Edit and I haven't checked others. AND it only works in the immediate form, also.

    Lastly I read VB6Database for Dummies and found that they recommended I use Set rsCCF = OpenDatabase(mPath+"CCFile") and all the code they attached to that (this doesn't seem to be an ADO Connection). This worked (partially : I had AddNew and Edit but no Find or others, but nowhere in this code is ADO mentioned) in one application. But (there always is, again) when I tried to duplicate it in another I got error messages and no amount of work has gotten rid of the messages (i.e.- Dim CCFile as Database = User defined function, undefined).

    HHHHHHHHEEEEEEEEEEELLLLLPPPPP!!!!!!!

    I'm lost :
    Question is basically :

    Is there a way to make a connection to the database (ADO if possible) that is good for all the forms in my Project and that allows me to perform ALL the database operations (like : AddNew/Edit/Find/FindNext/Move/MoveLast,First,Etc.

    Are any of the above three the proper method or a combination of them ????? I'd REALLY APPRECIATE any HELP - particularly if you can keep it simple (for the simple minded!).


    Mike

  2. #2
    Join Date
    Apr 1999
    Location
    Netherlands
    Posts
    181

    Re: ADO Connection

    Sure there is, declare the connection object in a module and do that public eg.
    Public gConnection As ADODB.Connection
    gConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & s, "", "", -1

    And declare recordsets to access the tables, I usually have a recordset in each form, eg.
    Private rst As ADODB.Recordset
    And open it like
    rst.Open "SELECT * FROM MyTable", gConnection, adOpenDynamic, adLockOptimistic, -1

    With the recordset object, you can add, edit, delete, find (you can also use an SQL query to open a specific record), basically all stuff you want to do with a database.

    Crazy D @ Work :-)

  3. #3
    Join Date
    Dec 1999
    Location
    CA, USA
    Posts
    4

    Re: ADO Connection

    I just tried (ran) your method :
    where we have in Module1 : dbCCFile.open "Provider=Microsoft.Jet.oledb.4.0;datasource=" & s."CCFile.mdb."".-1

    The error was "Invalid Outside Proceedure" and the highlight code was "Provider=Microsoft.jet.oloedb.4.0;datasource", just that part.

    Am I short on References ? Or components ? Or maybe brains ?
    I'm from the old DOS based school and I found coding easy at the time and easy here BUT this is giving me fits.
    Thanks a lot for the try, anyway...

    Mike

  4. #4
    Join Date
    Dec 1999
    Location
    U.S./Canada
    Posts
    5

    Re: ADO Connection

    Put the declaration of the ADO variable in a global module.
    Put the open portion into the Form_Load event of the startup form or password/user authorization form.

    For example:
    Public adocn as ADODB.Connection ' In gobals module, shared across all forms in project


    ' In the Form load event or the connect event
    ' Connect
    Set adocn = New ADODB.Connection
    With adocn
    .ConnectionString = "DSN=MyDatabase"
    .Open
    End With

    ' Make sure that you delete and clean up when you unload all the forms.
    ' In final Form unload event (i.e. unload event of the form that created the connection)
    If Not adocn Is Nothing Then
    If (adocn.State = adStateOpen) Then
    adocn.Close ' close connection
    Set adocn = Nothing ' cleanup memory
    End If
    End If
    ' Note that this way is good because it lets you maintain one connection across a session and
    ' this significantly saves time and performance.
    ' Also note that the VB 6.0 documentation explains the ADO object model. The model
    ' Has Recordset objects off of a connection. One connection can have many recordset objects
    ' If you need it email me, I will put out the code for setting up the recordsets, but its also there in the
    ' MS documentation.

    Luck,
    Nikhil


  5. #5
    Join Date
    Dec 1999
    Location
    CA, USA
    Posts
    4

    Re: ADO Connection

    Hi : Please don't think I'm not trying alternatives between these e-mails. I tried what you suggested. BUT (Oy!), I can't seem to get past the error :

    "Cannot start your application. The workgroup information file is missing or opened exclusively by another user."

    I am on a stand alone pentium and I have thoroughly gone through all the forms in the project and studiously removed all the ADOdata controls and the other references to ADO anything (there were a LOT since I was trying several different methods - I forgot in my original question that I had tried a fourth method in creating a DataEnvironment and tried to use that to no avail.)

    What I plan , now, to try is a whole new project and see if I can JUST use your suggestions and see what I get.

    BUT ; I noticed that when I was retyping some mentions of the data source i.e.- rsMemories.MoveFirst etc. when I typed the . the drop down list had AddNew/Find/Etc. EXCEPT all it had was EditMode not just Edit. Is that because attaching the txtBox to the field in the properties (data bound control, I think it's called) doesn't need an Edit, and I just lock or unlock the txtBox ?

    Hey, anyway, I appreciate your taking the time to retrain an old man in the new stuff. I owe you a lunch. Mike

    Mike

  6. #6
    Join Date
    Apr 1999
    Location
    Netherlands
    Posts
    181

    Re: ADO Connection

    Trying it out in a new project is never a bad idea ;-)
    Nope ADO doesn't have an edit mode ( :-(( ) you just assign the new values to the recordset and call the update (or batchupdate if you update more then one record) method. Or use an SQL UPDATE query....
    Anyway, the error you're getting, I couldn't even imagine that such an error exists... but let us know how it goes in the new project :-)

    Crazy D @ Work :-)

  7. #7
    Join Date
    Jul 1999
    Location
    Athens, Hellas
    Posts
    769

    Would you like to try another way?

    Just use Data Environment that comes with vb6
    You set the connection graphically with no code, and it returns a recordset with all the methods and properties you need and you can use it anywhere on your forms by calling:

    MyDataEnvironment.rsMyrecordset

    and without any line of code for the connection!!!
    search on msdn to find example of how to use dataenvironment!!! It's easy!!!!!!!

    Michael Vlastos
    Automation Engineer
    Company SouthGate Hellas SA
    Development Department
    Athens, Greece

  8. #8
    Join Date
    Jul 1999
    Location
    Mumbai - India
    Posts
    12

    Re: ADO Connection

    Hi Mike,

    You can set up a global connection object and create recordsets as and when you need in your forms.
    Say for example,

    In some of your global modules

    Global gDBConn = new ADODB.Connection
    gDBConn.Open "Provider=MSDASQL;DSN=Memories"




    In say one of your forms you would use

    Dim rsMyInfo as ADODB.Recordset
    set rsMyInfo = new ADODB.Recordset
    With rsMYInfo
    .ActiveConnection = gDBConn
    .LockType = adLockOptimistic
    .Open "SELECT * From MyMemories"
    End With



    And you are ready to play with all the methods: Move, Find, Update, etc.
    Please don't forget to close the connection on exiting the program


  9. #9
    Join Date
    Dec 1999
    Location
    CA, USA
    Posts
    4

    Re: ADO Connection

    Yours was the best of the suggestions sent in. Worked great.

    Only further info I'd like answered is about the ADO Data Control. Use it in cnjunction with your set up or separately ?

    The only reason I'd like to know is that with your suggestion (working great), when I go into the properties of a txtBox there is no data source. I read where we can use the properties to link the data to the txtBox and not have to specify(I made a function to fill in the boxes for the one screen program I'm using to test with> txtBox = Recordset!<field> for each movement. True ?

    Mike

  10. #10
    Join Date
    Apr 1999
    Location
    Rotterdam, Netherlands
    Posts
    278

    Re: ADO Connection

    I've never used bound controls, I assume you can select datasource if you put an ADO Data Control on the form.
    I always use code, like
    txtName.Text = Iif(IsNull(rs!Name), "", rs!Name)

    Crazy D :-)
    "One ring rules them all"

  11. #11
    Guest

    Re: ADO Connection

    hiya mike
    well mike ur question on ADO COnnection depends on wot database u want.. are u using Sql-server or anyother database..???see i will give u a sample code to access ur database using ados
    here goes

    set the reference dll in vb references to Active X Data bjects 2.0 library
    then declare variables as say

    dim CONN as new adodb.connection
    dimb REC as new adodb.recordset

    after this create a dsn for ur database

    then the code goes as like this in any event

    set conn=createobject("Adodb.connection")
    conn.open "dsn=dsnname;Uid=uidname;Pwd=password;"

    say for example u want to query a database for all records then use this
    str="Select * from tablename"
    set rs=conn.execute(str)
    this will set the recordset

    now for global acceptance of using this connection object

    Add a class module to ur project write this code in a function there

    then after that in the module declare a variable as a instance of that class as

    say
    dim CNCLASS as new class1(here class1 is the class module name)

    then whenever and wherever u say in ur entire project

    cnclass DOT then the Function appears u can use ur connection object and the recordset object only one object throughtout ur project

    but make sure in every event wherever u use it set it to nothing (ie)

    set rs(recordset variable)=nothing
    set conn(connection object)=nothing
    if dont use this code ,u will have problems in using connection object and
    recordset operations in more than one event

    bye
    sudharshaan

    contact me at sudharshaan1@mailexcite.com




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center