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

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

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

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

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

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

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

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

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

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"

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 [email protected]