calling ado-connection

[Jason]

Hi,

Is the following code correct to call the ado-connection
from every form i create. I'm getting errors if i execute it.
For testing purposes i used the code in the form's module and then it
worked, but not how i like it. The purpose of it, is to use connection
pooling.

Here is the code in the module:
option explicit
Dim cnn As ADODB.Connection

public sub ado_conn()

Set cnn = New ADODB.Connection

With cnn
.Provider = "sqloledb"
.Properties("Data Source") = "local"
.Properties("Initial Catalog") = "NEW"
.Properties("User ID") = "sa"
.Properties("Password") = "password"
End With
end sub

From the form onOpen event:

dim cnn as adodb.connection
Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset

rst.CursorLocation = adUseClient
cnn.Open cnn
cnn.usp_search 7, Nz(Null, ""), rst 'this line calls a stored procedure
from a sql server database.

Set Forms("Form1").Recordset = rst

rst.Close

Set rst = Nothing

cnn.Close

Set cnn = Nothing

 

[Brendan Reynolds]

You have two variables both named cnn, one with module-level scope, one
local to the Form_Open event procedure. The local variable will over-ride
the module-level variable within the Form_Open event procedure, effectively
making the module-level variable invisible to the code in the Form_Open
event procedure. Try renaming one of these variables.

 

[Jason]

Hi Brendan,

I've tried to rename the variable, but currentproject.connection referes to
the jet connection.

Do you know how this code should be if i want to call it in every form i
create?

Greetings,

J

 

[Brendan Reynolds]

When you have two variables, one with module-level scope and one with
procedure-level scope, the variable with procedure-level scope takes
precedence within that procedure. In your Form_Open event you have the
following line ...

cnn.Open cnn

Both references to the cnn variable here are references to the variable
declared *within* the Form_Open event, *not* to the variable declared at
module level. But none of the properties of the procedure-level variable
have been set - it knows nothing about the properties you assigned to the
variable with the same name at module-level, it is a completely separate
variable.

 

[Jason]

Hi Brendan,

I understand that, but how should it be set up? I did change the names but
still got errors.

I hope you can give the solution.

Greetings,

J

 

[Brendan Reynolds]

This is not something I've ever needed to do in a 'real-world' application,
but if I did, I guess I would probably do something like the following ...

Option Compare Database
Option Explicit

Public mconn As ADODB.Connection

Public Function GetConnection(ByVal boolOpenIfClosed As Boolean) As
ADODB.Connection

If mconn Is Nothing Then
Set mconn = New ADODB.Connection
With mconn
'Persist Security Info = True only for testing/checking -
'will normally be set to False in production scenarios.
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Password=TestPassword;" & _
"User ID=TestUser;" & _
"Data Source=C:\DSDATA\Northwind.mdb;" & _
"Persist Security Info=True;" & _
"Jet OLEDB:System database=c:\dsdata\test.mdw"
End With
End If
If mconn.State = adStateClosed Then
If boolOpenIfClosed Then
mconn.Open
End If
End If

Set GetConnection = mconn

End Function

Public Sub TestGetConnection()

Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset
With rst
.ActiveConnection = GetConnection(True)
.Source = "SELECT FirstName, LastName FROM Employees"
.Open
Debug.Print .Fields("FirstName") & " " & .Fields("LastName")
.Close
.ActiveConnection.Close
End With

End Sub

 

[Jason]

Hi Brendan,

I succesfully implemented your code in my front-end. However is your code
enough to handle the closing of connections.

For example i use on the unload/close-event of a form to close the
connection and set the connection to nothing.
Should i leave it or can i remove those lines?

Thnx

 

[Brendan Reynolds]

I would certainly recommend closing the connection when you're done with it,
but beyond that I can't advise you. As I said, I've never needed to do this,
and I'm not sure what it is you're trying to achieve here. Using
CurrentProject.Connection and letting Access take care of it works for me -
are there particular reasons why you're not doing that?

 

[Jason]

Hi Brendan,

I have several reasons to do this.

I use this approach because i'm not using an Access.adp file. It cannot
handle multiple sessions like an .mdb. Otherwise i have to install a copy of
the adp-file to every pc, instead of dumping it to a network location where
everybody can open it.
Second, is that i don't want to link the tables into access so that nowone
can import the linked tables into another database if no access-security is
set (mdw-file).
Third, i don't have to use the jet engine to get data, an ado connection
method is faster.
Fouth, i don't have to setup a dsn on every pc.

Are these the right reasons to do what i'm trying to do?

 

[Brendan Reynolds]

ODBC-linked tables and a copy of the 'front-end' application MDB on each PC
works for me. It seems to work for most others as well. But that's really
all I can say. You know your own situation best. If you feel your particular
circumstances require a different approach, it's your call.

 

[Jason]

Hi Brendan,

So if you need to change your front-end, are you going to replace all the
front-ends on all pc's?

 

[Brendan Reynolds]

Sure. I usually use a simple batch file, with a shortcut icon on the user's
desktop they can double-click to copy the app from the server. But if you
need a more automated approach, check out Tony Toews 'Auto FE Updater' ...
http://www.granite.ab.ca/access/autofe.htm
.... or Paul Litwin's 'AccessCheck' ...
http://www.mvps.org/access/resources/downloads.htm

 

[Jason]

Hi Brendan,

You mentioned that you use a batchfile to distribute your app. I have a
question about a batchfiles, so i hope you can help me.

I have a batchfile which copies a access.mdb to the location of the
batchfile. After that it renames the .mdb.
It would be nice if it renames the file with the current date. E.g.
<name>_<current date>.mdb.

Do you know the syntax to do that?

 

[Brendan Reynolds]

Sorry, no, I don't remember how to get the system date in a batch file.

The file I use is very simple, and I just copy the existing file and change
the paths and names. I never was any kind of expert on batch files, and what
little I did know on that subject I've mostly forgotten now.