Multiple ADO Connections with VB6

[Panthaur]

Greetings all,

I am working on a MDI application where I can have multiple screens open to many different parts of the application. Maintenance, orders, reports, etc...

Basically, I typically create an application that allows me to define my connection and recordset objects, as in the example below:

[ ---- code snippet start --- ]
' functions are scaled down drastically so as to not make a
' super long posting
Public ConSQL As ADODB.Connection
Public RstSQL As ADODB.Recordset
Public SQL As String

Public Function SQLInitConn() As Boolean
Dim mstrDBConnect As String

mstrDBConnect = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=dbasename"

Set ConSQL = New ADODB.Connection
Set RstSQL = New ADODB.Recordset

ConSQL.Open mstrDBConnect
Exit Function
End Function

Public Sub SQLCloseConn()
On Error Resume Next
RstSQL.Close
ConSQL.Close
Set RstSQL = Nothing
Set ConSQL = Nothing
End Sub

Public Sub SubmitQuery()
RstSQL.CursorLocation = adUseClient
RstSQL.Open SQL, ConSQL, adOpenKeyset, adLockOptimistic
End Sub
[ ---- code snippet end --- ]


Now that I have posted by snippet, I can explain my problem. If I have one form open, let's say customer maintenance, and I am loading a list of all available customers, but I want to run a report at the same time, the report will crash and not function because I only have one database connection available, and it's currently busy loading the customer list.

I had the bright idea (sarcastically speaking) to do something along these lines (see below):

Public ConSQL(1 to 10) As ADODB.Connection
Public RstSQL(1 to 10) As ADODB.Recordset
Public SQL As String

which I thought would allow me to essentially have an array of connections where I could use the ActiveConnection property to see if consql(1) was busy, and go on to consql(2), or consql(3) if consql(2) was busy, etc... Well, VB did not like that at all. It gave me an error about collections as soon as I tried to do something like rstsql(1)!fieldname.

Anyway, now that i've explained my attempts, and gotten frusterated trying to implement them, my question is this:

Can someone give me a small code snippet example of how I essentially can create dynamic database connections in the event that one form or another needs to access the database at the same time (without hosing the memory in the computer)? I was going to forget about using my SqlInitConn routine, and just immediately call the SubmitQuery function which would see if there was an open connection, then check if the recordset for the connection was busy and use that connection, or open another connection if needed. But, again, that didn't work.

Any help would be appreciated.

Thanks,
Pan

 

[BobRodes]

I'd look at disconnected recordsets here. You can grab whatever data you want, put it in a recordset, and close the connection while keeping the recordset locally. Unless you need VERY up-to-the-minute reports, this should work fine.

So, use the connection to open the recordset(s) on which your report is based, and then set the recordset's ActiveConnection property to Nothing. (You may or may not close the connection, depending on whether you need it again right away.) You can then run your reports or whatever, doing whatever you want with your recordset, and also do whatever you want with your connection.

Ok. Disconnected recordsets require the following:

CursorLocation = adUseClient
LockType = adLockBatchOptimistic

(You don't need to set the CursorType property to adOpenStatic, since this is the only value available on client side recordsets.)

Now, create a recordset, open it with a connection, set the ActiveConnection property to Nothing. You'll have a recordset with no connection. (There's also a facility to update the recordset locally and batch the changes to the server, and you can save the recordset locally as an xml file as well.)

There's a wealth of material on disconnected recordsets, so you can read up on them all you want. But that's how I would first address your problem.

HTH

Bob

 

[ChewDoggie]

try disconnecting from the database after you've opened a recordset.

RstSQL.open sql, ConSQL
RstSQL.ActiveConnection = nothing

THen, when you've modified that recordset and you want to update the table/database, then re-establish the connection with:

RstSQL.ActiveConnection = ConSQL

and BAM! the info is written to the table.

HTH



ciao for niao!

AMACycle

American Motorcyclist Association

http://www.ama-cycle.org

 

[Panthaur]

What about if I have a recordset defined locally for each form, and use that forms recordset instead of a global recordset?

Right now I have the recordset variable and the connection variable stored in a module and they are global. I can use multiple recordsets for once connection, right?

Pan

 

[Panthaur]

Also, the error I mentioned in my first post is as follows:

Compile Error:

Qualifier must be collection


That's when i'm doing the following: rstsql(1)!fieldname

Thanks,
Pan

 

[Sheco]

Instead of a Sub for your shared recordset:
Public Sub SubmitQuery()
RstSQL.CursorLocation = adUseClient
RstSQL.Open SQL, ConSQL, adOpenKeyset, adLockOptimistic
End Sub

You could write a function to give back disconnected ones:
Public Function FetchRS(SQL As String) As ADODB.Recordset
Set FetchRS = New ADODB.Recordset
FetchRS.CursorLocation = adUseClient
FetchRS.LockType = adLockBatchOptimistic
FetchRS.Open SQL, ConSQL

Set FetchRS.ActiveConnection = Nothing
End Function

Then you could call it from your MDI child forms like this:
'... somewhere on child form...
Dim myRS As ADODB.Recordset
Set myRS = FetchRS("Select * from Clients")

Do While Not myRS.EOF
Debug.Print myRS(0)
myRS.MoveNext
Loop

myRS.Close
Set myRS = Nothing

'... other code continues here ...

 

[BobRodes]

What about if I have a recordset defined locally for each form, and use that forms recordset instead of a global recordset?

Right now I have the recordset variable and the connection variable stored in a module and they are global. I can use multiple recordsets for once connection, right?

You can certainly have a recordset defined locally in each form, and if the form is the only entity using it, it's probably the way to go.

You can certainly use a global connection, and use that single connection with the different recordsets in your project. In fact, that is definitely the way to go. Multiple connections generally imply multiple data providers, not multiple data consumers.

HTH

Bob

 

[ChewDoggie]

I agree with Bob. We use a globally declared connection in mdlMain:

Public Sub Connect()
constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\EMS.MDB"
If Not IsNull(cn) Then
Set cn = New ADODB.Connection
End If
If Not cn.State Then
cn.Open constr
End If
End Sub

and when the Main Form in the project is loaded, we call this method:

Private Sub MDIForm_Load()
On Error GoTo ErrorHandler
Me.Width = Screen.Width
Me.Height = Screen.Height
Me.Top = (Screen.Height - Me.Height) / 2
Me.Left = (Screen.Width - Me.Width) / 2

If App.PrevInstance <> 0 Then
MsgBox "One instance of application already running.", vbCritical, "System message"
End
End If
Connect
Exit Sub

ErrorHandler:
ReferringForm = "frmMain"
Record_Error Err.Number, Err.Description, "MDIForm_Load"
Resume Next
End Sub

All recordsets are declared either as form-level recordsets (for when multiple modules are sharing the recordset) or at the Procedure-level for isolated uses of a recordset:

private sub Form_Load()
Dim rsClients as ADODB.Recordset
Dim sql as string

On Error goto ErrorHandler
Set rsClients = New ADODB.Recordset
sql = "select * from Clients Where district = " & dist
rsClients.Open sql, cn, adOpenKeyset, adLockOptimistic
If not rsClients.EOF Then
...
End If
rsClients.Close
Set rsClients = nothing
Exit Sub

ErrorHandler:
referringform = "frmMyForm"
record_error err.Number, err.Description, "Form_Load"
Resume Next
End Sub

...for example.

HTH




ciao for niao!

AMACycle

American Motorcyclist Association

http://www.ama-cycle.org

 

[ChewDoggie]

Sorry, replace "(for when multiple modules are sharing the recordset)" with "(for when multiple PROCEDURES are sharing the recordset")



ciao for niao!

AMACycle

American Motorcyclist Association

http://www.ama-cycle.org

 

[Panthaur]

Thank you all for your help. I've converted my application to having a recordset defined for each form in the MDI environment, and it is working great now. Also, I didn't have to over-modify the heck out of my application.

I had to add a couple of lines to each form (sql as string and rstsql as new recordset), do a global search and replace and replace submitquery to "submitquery sql,rstsql" and my app is working with no more recordsets crashing together problems that I had before.

Cheers,
Pan