470,591 Members | 1,772 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,591 developers. It's quick & easy.

Open MS Access Exclusive

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
Jul 31 '07 #1
6 4546
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)
Aug 1 '07 #2


"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
>
Aug 1 '07 #3
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)
Aug 2 '07 #4


"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
>
Aug 2 '07 #5
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)
Aug 3 '07 #6


"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

>
Aug 3 '07 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Wayne | last post: by
1 post views Thread by buclas | last post: by
2 posts views Thread by existential.philosophy | last post: by
14 posts views Thread by SimeonD | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.