Open MS Access Exclusive

[=?Utf-8?B?RyBIdXN0aXM=?=]

I am creating a new MS Access DB in code, which works fine. I then try to
open it & set a password. I keep getting an error that the DB is already open
by my machine's Admin account. In reality it is not actually open.
Here is a look at my code for creating & then setting the Password.

Dim ADOXcat As New ADOX.Catalog
sCreateString = "Provider=" & Provider & ";Data Source=" & DatabaseFullPath

Try
ADOXcat.Create(sCreateString)
bAns = True
Catch Excep As System.Runtime.InteropServices.COMException
bAns = False
MessageBox.Show("Could not create Database. " & Excep.ToString,
"Create DataBase", MessageBoxButtons.OK, MessageBoxIcon.Error)
Return bAns
Exit Function
Finally
ADOXcat = Nothing
End Try
Up to this point things work fine, the DB is created in the folder specified.
Here is where I have the problem

Dim NewTabConn As New System.Data.OleDb.OleDbConnection
sCreateString = "Provider=" & Provider & ";Data Source=" & DatabaseFullPath
'& "; Jet OLEDB:Mode=Share Exclusive"
(I redefine my connection string to open the db exclusively)

NewTabConn = New OleDbConnection(sCreateString)
Dim NewDBPassCmd As OleDbCommand = New OleDbCommand

NewDBPassCmd.Connection = NewTabConn
NewDBPassCmd.CommandText = "ALTER DATABASE PASSWORD CHURCH NULL"
NewTabConn.Close()
Try
NewTabConn.Open() <<<<--This is where the problem occurs
NewDBPassCmd.ExecuteNonQuery()
NewTabConn.Close()
Catch ex As OleDbException
MessageBox.Show("Error: " & ex.ErrorCode & ex.Message, "Error",
MessageBoxButtons.OK)
NewTabConn.Close()
Finally
NewDBPassCmd.Dispose()
End Try

The error I get is this:
You attempted to open a database that is already opened exclusively by user
'Admin' on machine 'GHUSTIS'. Try again when the database is available.

So somewhere between the creation of the db & altering it i need to close it
completely, but is isn't really opened. How can i do this?

As a note: the sCreateString variable is created using other varialbe to set
the db location to use jet4.0

Thanks
Gary

[Paul Clement]

On Tue, 31 Jul 2007 14:50:01 -0700, G Hustis <GH*****@discussions.microsoft.comwrote:

¤ I am creating a new MS Access DB in code, which works fine. I then try to
¤ open it & set a password. I keep getting an error that the DB is already open
¤ by my machine's Admin account. In reality it is not actually open.
¤ Here is a look at my code for creating & then setting the Password.
¤
¤ Dim ADOXcat As New ADOX.Catalog
¤ sCreateString = "Provider=" & Provider & ";Data Source=" & DatabaseFullPath
¤
¤ Try
¤ ADOXcat.Create(sCreateString)
¤ bAns = True
¤ Catch Excep As System.Runtime.InteropServices.COMException
¤ bAns = False
¤ MessageBox.Show("Could not create Database. " & Excep.ToString,
¤ "Create DataBase", MessageBoxButtons.OK, MessageBoxIcon.Error)
¤ Return bAns
¤ Exit Function
¤ Finally
¤ ADOXcat = Nothing
¤ End Try
¤ Up to this point things work fine, the DB is created in the folder specified.
¤ Here is where I have the problem
¤
¤ Dim NewTabConn As New System.Data.OleDb.OleDbConnection
¤ sCreateString = "Provider=" & Provider & ";Data Source=" & DatabaseFullPath
¤ '& "; Jet OLEDB:Mode=Share Exclusive"
¤ (I redefine my connection string to open the db exclusively)
¤
¤ NewTabConn = New OleDbConnection(sCreateString)
¤ Dim NewDBPassCmd As OleDbCommand = New OleDbCommand
¤
¤ NewDBPassCmd.Connection = NewTabConn
¤ NewDBPassCmd.CommandText = "ALTER DATABASE PASSWORD CHURCH NULL"
¤ NewTabConn.Close()
¤ Try
¤ NewTabConn.Open() <<<<--This is where the problem occurs
¤ NewDBPassCmd.ExecuteNonQuery()
¤ NewTabConn.Close()
¤ Catch ex As OleDbException
¤ MessageBox.Show("Error: " & ex.ErrorCode & ex.Message, "Error",
¤ MessageBoxButtons.OK)
¤ NewTabConn.Close()
¤ Finally
¤ NewDBPassCmd.Dispose()
¤ End Try
¤
¤ The error I get is this:
¤ You attempted to open a database that is already opened exclusively by user
¤ 'Admin' on machine 'GHUSTIS'. Try again when the database is available.
¤
¤ So somewhere between the creation of the db & altering it i need to close it
¤ completely, but is isn't really opened. How can i do this?
¤
¤ As a note: the sCreateString variable is created using other varialbe to set
¤ the db location to use jet4.0
¤
¤ Thanks
¤ Gary
¤

Have you tried closing the database connection through ADOX? It would appear that ADOX is keeping
its connection open.
Paul
~~~~
Microsoft MVP (Visual Basic)

[=?Utf-8?B?RyBIdXN0aXM=?=]

"Paul Clement" wrote:

On Tue, 31 Jul 2007 14:50:01 -0700, G Hustis <GH*****@discussions.microsoft.comwrote:

¤ I am creating a new MS Access DB in code, which works fine. I then try to
¤ open it & set a password. I keep getting an error that the DB is already open
¤ by my machine's Admin account. In reality it is not actually open.
¤ Here is a look at my code for creating & then setting the Password.
¤
¤ Dim ADOXcat As New ADOX.Catalog
¤ sCreateString = "Provider=" & Provider & ";Data Source=" & DatabaseFullPath
¤
¤ Try
¤ ADOXcat.Create(sCreateString)
¤ bAns = True
¤ Catch Excep As System.Runtime.InteropServices.COMException
¤ bAns = False
¤ MessageBox.Show("Could not create Database. " & Excep.ToString,
¤ "Create DataBase", MessageBoxButtons.OK, MessageBoxIcon.Error)
¤ Return bAns
¤ Exit Function
¤ Finally
¤ ADOXcat = Nothing
¤ End Try
¤ Up to this point things work fine, the DB is created in the folder specified.
¤ Here is where I have the problem
¤
¤ Dim NewTabConn As New System.Data.OleDb.OleDbConnection
¤ sCreateString = "Provider=" & Provider & ";Data Source=" & DatabaseFullPath
¤ '& "; Jet OLEDB:Mode=Share Exclusive"
¤ (I redefine my connection string to open the db exclusively)
¤
¤ NewTabConn = New OleDbConnection(sCreateString)
¤ Dim NewDBPassCmd As OleDbCommand = New OleDbCommand
¤
¤ NewDBPassCmd.Connection = NewTabConn
¤ NewDBPassCmd.CommandText = "ALTER DATABASE PASSWORD CHURCH NULL"
¤ NewTabConn.Close()
¤ Try
¤ NewTabConn.Open() <<<<--This is where the problem occurs
¤ NewDBPassCmd.ExecuteNonQuery()
¤ NewTabConn.Close()
¤ Catch ex As OleDbException
¤ MessageBox.Show("Error: " & ex.ErrorCode & ex.Message, "Error",
¤ MessageBoxButtons.OK)
¤ NewTabConn.Close()
¤ Finally
¤ NewDBPassCmd.Dispose()
¤ End Try
¤
¤ The error I get is this:
¤ You attempted to open a database that is already opened exclusively by user
¤ 'Admin' on machine 'GHUSTIS'. Try again when the database is available.
¤
¤ So somewhere between the creation of the db & altering it i need to close it
¤ completely, but is isn't really opened. How can i do this?
¤
¤ As a note: the sCreateString variable is created using other varialbe to set
¤ the db location to use jet4.0
¤
¤ Thanks
¤ Gary
¤

Have you tried closing the database connection through ADOX? It would appear that ADOX is keeping
its connection open.
Paul
~~~~
Microsoft MVP (Visual Basic)

Paul,
I would agree that ADOX is keeping the connection open, However ADOX does
not seem to have a connection open() or close() arguement. When the code
calls ADOXcat.Create(sCreateString), ADOX opens its own connection using the
connection string provided. I figured that by setting the ADOXcat = nothing
that i would kill the connection, but no luck.
If you know of a way to close the connection please let me know.
Thanks
Gary

>

[Paul Clement]

On Wed, 1 Aug 2007 13:30:03 -0700, G Hustis <GH*****@discussions.microsoft.comwrote:
¤ Paul,
¤ I would agree that ADOX is keeping the connection open, However ADOX does
¤ not seem to have a connection open() or close() arguement. When the code
¤ calls ADOXcat.Create(sCreateString), ADOX opens its own connection using the
¤ connection string provided. I figured that by setting the ADOXcat = nothing
¤ that i would kill the connection, but no luck.
¤ If you know of a way to close the connection please let me know.
¤ Thanks
¤ Gary
¤ >

Have you tried ADOXcat.ActiveConnection.Close?
Paul
~~~~
Microsoft MVP (Visual Basic)

[=?Utf-8?B?RyBIdXN0aXM=?=]

"Paul Clement" wrote:

On Wed, 1 Aug 2007 13:30:03 -0700, G Hustis <GH*****@discussions.microsoft.comwrote:
¤ Paul,
¤ I would agree that ADOX is keeping the connection open, However ADOX does
¤ not seem to have a connection open() or close() arguement. When the code
¤ calls ADOXcat.Create(sCreateString), ADOX opens its own connection using the
¤ connection string provided. I figured that by setting the ADOXcat = nothing
¤ that i would kill the connection, but no luck.
¤ If you know of a way to close the connection please let me know.
¤ Thanks
¤ Gary
¤ >

Have you tried ADOXcat.ActiveConnection.Close?
Paul
~~~~
Microsoft MVP (Visual Basic)

Paul,
Yes I have, however VS.Net2005 throws an error. Activeconnection.Close is
not a valid method. The actual error i get is "Option Strict On Disallows
late binding". Also the intelisense does not expose 'close' as a method for
Activeconnection, or any other ADOX method.
It's hard for me to believe that I am the only one who as encountered this
problem. Someone else must have experienced this & solved it.
I am also willing to create the database in another way (other than ADOX) if
it will give me the ability to create it and set a password. But I haven't
found that solution either.
Thanks
Gary

>

[Paul Clement]

On Thu, 2 Aug 2007 09:26:00 -0700, G Hustis <GH*****@discussions.microsoft.comwrote:

¤
¤
¤ "Paul Clement" wrote:
¤
¤ On Wed, 1 Aug 2007 13:30:03 -0700, G Hustis <GH*****@discussions.microsoft.comwrote:
¤ >
¤ >
¤ ¤ Paul,
¤ ¤ I would agree that ADOX is keeping the connection open, However ADOX does
¤ ¤ not seem to have a connection open() or close() arguement. When the code
¤ ¤ calls ADOXcat.Create(sCreateString), ADOX opens its own connection using the
¤ ¤ connection string provided. I figured that by setting the ADOXcat = nothing
¤ ¤ that i would kill the connection, but no luck.
¤ ¤ If you know of a way to close the connection please let me know.
¤ ¤ Thanks
¤ ¤ Gary
¤ ¤ >
¤ >
¤ Have you tried ADOXcat.ActiveConnection.Close?
¤ >
¤ >
¤ Paul
¤ ~~~~
¤ Microsoft MVP (Visual Basic)
¤
¤ Paul,
¤ Yes I have, however VS.Net2005 throws an error. Activeconnection.Close is
¤ not a valid method. The actual error i get is "Option Strict On Disallows
¤ late binding". Also the intelisense does not expose 'close' as a method for
¤ Activeconnection, or any other ADOX method.
¤ It's hard for me to believe that I am the only one who as encountered this
¤ problem. Someone else must have experienced this & solved it.
¤ I am also willing to create the database in another way (other than ADOX) if
¤ it will give me the ability to create it and set a password. But I haven't
¤ found that solution either.
¤ Thanks
¤ Gary
¤ >

Try the following:

CType(ADOXcat.ActiveConnection, ADODB.Connection).Close()
Paul
~~~~
Microsoft MVP (Visual Basic)

[=?Utf-8?B?RyBIdXN0aXM=?=]

"Paul Clement" wrote:

On Thu, 2 Aug 2007 09:26:00 -0700, G Hustis <GH*****@discussions.microsoft.comwrote:

¤
¤
¤ "Paul Clement" wrote:
¤
¤ On Wed, 1 Aug 2007 13:30:03 -0700, G Hustis <GH*****@discussions.microsoft.comwrote:
¤ >
¤ >
¤ ¤ Paul,
¤ ¤ I would agree that ADOX is keeping the connection open, However ADOX does
¤ ¤ not seem to have a connection open() or close() arguement. When the code
¤ ¤ calls ADOXcat.Create(sCreateString), ADOX opens its own connection using the
¤ ¤ connection string provided. I figured that by setting the ADOXcat = nothing
¤ ¤ that i would kill the connection, but no luck.
¤ ¤ If you know of a way to close the connection please let me know.
¤ ¤ Thanks
¤ ¤ Gary
¤ ¤ >
¤ >
¤ Have you tried ADOXcat.ActiveConnection.Close?
¤ >
¤ >
¤ Paul
¤ ~~~~
¤ Microsoft MVP (Visual Basic)
¤
¤ Paul,
¤ Yes I have, however VS.Net2005 throws an error. Activeconnection.Close is
¤ not a valid method. The actual error i get is "Option Strict On Disallows
¤ late binding". Also the intelisense does not expose 'close' as a method for
¤ Activeconnection, or any other ADOX method.
¤ It's hard for me to believe that I am the only one who as encountered this
¤ problem. Someone else must have experienced this & solved it.
¤ I am also willing to create the database in another way (other than ADOX) if
¤ it will give me the ability to create it and set a password. But I haven't
¤ found that solution either.
¤ Thanks
¤ Gary
¤ >

Try the following:

CType(ADOXcat.ActiveConnection, ADODB.Connection).Close()
Paul
~~~~
Microsoft MVP (Visual Basic)

Paul,
Thanks for the suggestion, however it does not work. VB gives me an error on
the ADO.Connection portion & says 'Connection is ambiguous in the namespace
ADODB. I also tried a few other renditions of this and could not find a
solution.
I tried to follow what the MSDN atricle says: 'Setting the ActiveConnection
property to Nothing should close the connection to the catalog', which i have
done and it still does not work.
http://msdn2.microsoft.com/en-us/library/ms681562.aspx
Gary

>