Need Help w/ADO Connection String Open/Close 1

[Christineeve]

Hi,
I have to sub routines: One to open a connection and one to close a connection.

When I run this code, it keeps telling me that the connection is open and it won't close. But when it tests to see if the connection is closed, it just doesn't seem to evaluate that correctly. It seems like it's not closing the connection.

I'd like to call the connection from a routine, process some VBA and SQL Code then call the closing procedure after I'm finished with the sub routine. Everytime I open my connection, instead of the closing working, I just have to close my database. That's not the desired result. Any help is sincerely appreciated.

Here is my code:

Sub OpenADODatabase()
'Sub routine to open an active database connection.

Dim conn As ADODB.Connection
'Dim cat As ADOX.Catalog
Dim cmd As ADODB.Command
Dim myconnection As String

strPath = CurrentProject.Path & "\FM_Dcsh.mdb"
Set conn = New ADODB.Connection
myconnection = conn
conn.Mode = adModeReadWrite
If IsEmpty(myconnection) Then
conn.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source = " & strPath
conn.Open
Set rst = New ADODB.Recordset
End If

End Sub

Sub closeADODatabase()
'Sub Routine to close active database connection
If Not IsNull(conn) Then
If Not IsEmpty(conn) Then
conn.Close
conn = Nothing
Set cmd = Nothing
End If
End If
End Sub

 

[PHV]

Tip: use the Option Explicit instruction ...

 

[Christineeve]

Hi PHV,
Thank you for the reminder. I set the option to require variable declaration. This should default so that all modules have that set now! yay.

I inherited 50 to 60 databases that were developed by non developers. So, it's going to take awhile to get them fixed up. And, I'm no expert myself either so I appreciate the help in any case.

 

[Christineeve]

Ok, I ran into a problem. I need to research my connection string again. I see that I'm in a "secure" database. Which is not really secure but it does require you to join users and groups.

 

[PHV]

As yuo want to use the conn object in several procedures you have to make it global.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JoeAtWork]

See

http://www.connectionstrings.com

Joe Schwarz
Custom Software Developer

http://www.plandev.net

 

[Christineeve]

Joe,
I did review that link before I posted. The link is helpful for those who have a password. This database has no password but has a User Log on. I did not create the security for this database, I inherited it.

So, I believe if I use currentuser() and pass that as a variable to the connection string, I can get it to open. But, I'm still not doing something correctly.

I appreciate your post. Thank you.

 

[cmmrfrds]

The conn is a local object and will go out of scope when the sub routine is exited. In other words it will be suspended in memory (sometimes called a memory leak) and you will never be able to access to get rid of the space it occupies until you close the app. If you want to keep the connection available across Functions and Subroutines it must be a Public object.

Also, to test the existence on an objet like a connection you need to use the special object called "Nothing" like so.

Dim cn As New ADODB.Connection
If cn Is Nothing Then
Debug.Print "No connection "
Exit Function
Else
If cn.state = 0 Then
Debug.Print "Connection Closed"
Exit Function
End If
End If