Problem with ADO Microsoft Jet - Programmers Heaven

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

Problem with ADO Microsoft Jet

darthmoobeydarthmoobey Posts: 140Member
I wrote this program with ADO connection string to a microsoft access database (.mdb) file. The program is working great when used as a single computer, but I need it to be a shared multiuser database system. I have the DB shared on the network, and mapp the directory as a drive on the client station. When the program runs, it opens the database by the mapped drive directory. Now when I open the program and database to view data on one client, when I open the other client I get an error saying

Run-Time Error
Cannot open file "; File already in use.

I am including the code at the bottome because I don't know if its a setting I need to fix in my ado connection or recordset connection setting.

[code]
Public Function Open_CentexDB()
If CentexDB.State = adStateOpen Then
CentexDB.Close
End If

CentexDriveLetter.MoveFirst
DBDriveLetter = CentexDriveLetter.Fields("Drive").Value

CentexDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;persist security info =false;data source=" & DBDriveLetter & ":databasesCentex.mdb"

End Function

Public Function Open_CentexTable()

If CentexTable.State = adStateOpen Then
CentexTable.Close
End If

With CentexTable
.ActiveConnection = CentexDB
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.Source = "SELECT * FROM CentexInfo"
.Open
End With

End Function

[/code]

Those are both functions I open up on form load to view the data. Someone please help me, the company I am building this program for does not wish to buy microsoft SQL server to handle database solutions, they wish to use microsoft access.

Thank you,
The Darthmoob

Comments

  • RickManRickMan Posts: 7Member
    [b][red]This message was edited by RickMan at 2005-12-9 14:45:48[/red][/b][hr]
    [b][red]This message was edited by RickMan at 2005-12-9 14:45:6[/red][/b][hr]
    : I wrote this program with ADO connection string to a microsoft access database (.mdb) file. The program is working great when used as a single computer, but I need it to be a shared multiuser database system. I have the DB shared on the network, and mapp the directory as a drive on the client station. When the program runs, it opens the database by the mapped drive directory. Now when I open the program and database to view data on one client, when I open the other client I get an error saying
    :
    : Run-Time Error
    : Cannot open file "; File already in use.
    :
    : I am including the code at the bottome because I don't know if its a setting I need to fix in my ado connection or recordset connection setting.
    :
    : [code]
    : Public Function Open_CentexDB()
    : If CentexDB.State = adStateOpen Then
    : CentexDB.Close
    : End If
    :
    : CentexDriveLetter.MoveFirst
    : DBDriveLetter = CentexDriveLetter.Fields("Drive").Value
    :
    : CentexDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;persist security info =false;data source=" & DBDriveLetter & ":databasesCentex.mdb"
    :
    : End Function
    :
    : Public Function Open_CentexTable()
    :
    : If CentexTable.State = adStateOpen Then
    : CentexTable.Close
    : End If
    :
    : With CentexTable
    : .ActiveConnection = CentexDB
    : .CursorLocation = adUseClient
    : .CursorType = adOpenStatic
    : .LockType = adLockOptimistic
    : .Source = "SELECT * FROM CentexInfo"
    : .Open
    : End With
    :
    : End Function
    :
    : [/code]
    :
    : Those are both functions I open up on form load to view the data. Someone please help me, the company I am building this program for does not wish to buy microsoft SQL server to handle database solutions, they wish to use microsoft access.
    :
    : Thank you,
    : The Darthmoob
    :


    Two quick questions:

    [b][Leftbr]1.[rightbr][/b] Is this a split database, or an all in one
    application. From the description I am thinking that it is all in
    one. You may want to consider,splitting the application and database
    portion, having the users use their own local copy of the front end in
    MDE format and having a seperate back end.

    [b][Leftbr]2.[rightbr][/b] What is the open mode for this database.
    You can check it by accessing the Tools/Options menu and going to the
    advanced tab, in Access 2003, I do not remember the tab name in Access
    2000. If the system is not in shared mode, that would eplain the
    problem.

    This can be checked in code:
    [code]
    [Blue][b]
    If CurrentProject.Connection.Mode = adModeShareExclusive Then
    [green]'The value is 12 and the Database is Exclusive.
    [blue]
    If CurretProject.Connection.Mode = adModeShareDenyNone Then
    [green] The value is 16 and it is Shared.
    [/blue][/b]
    [/code]

    [black]
    HTH
    RickMan





  • darthmoobeydarthmoobey Posts: 140Member
    Hey thanks Rickman,

    The database at the moment is not exclusive, but shared. Also the way the program works is it is a standalone visual basic application user interface. The database is a simple .mdb on a shared directory on the network. The visual basic program uses microsoft jet to open the database and update using the simple.

    ADODB.Recordset.Fields("Name").Value = TxtName.Text
    ADODB.Recordset.Update

    This is my first attempt at making a shared Database over the network without useing SQL server and only using an Access Database. I do not have the program saved as an MDE format, it is still in its .mdb format. Do I need to convert it to MDE? I was reading that Access has what it calls Record Locking, is that why when a second client tries to open the database with the visual basic application it says it is already open?

    Thank you Rick for all your help.

    The Darthmoob

    : [b][red]This message was edited by RickMan at 2005-12-9 14:45:48[/red][/b][hr]
    : [b][red]This message was edited by RickMan at 2005-12-9 14:45:6[/red][/b][hr]
    : : I wrote this program with ADO connection string to a microsoft access database (.mdb) file. The program is working great when used as a single computer, but I need it to be a shared multiuser database system. I have the DB shared on the network, and mapp the directory as a drive on the client station. When the program runs, it opens the database by the mapped drive directory. Now when I open the program and database to view data on one client, when I open the other client I get an error saying
    : :
    : : Run-Time Error
    : : Cannot open file "; File already in use.
    : :
    : : I am including the code at the bottome because I don't know if its a setting I need to fix in my ado connection or recordset connection setting.
    : :
    : : [code]
    : : Public Function Open_CentexDB()
    : : If CentexDB.State = adStateOpen Then
    : : CentexDB.Close
    : : End If
    : :
    : : CentexDriveLetter.MoveFirst
    : : DBDriveLetter = CentexDriveLetter.Fields("Drive").Value
    : :
    : : CentexDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;persist security info =false;data source=" & DBDriveLetter & ":databasesCentex.mdb"
    : :
    : : End Function
    : :
    : : Public Function Open_CentexTable()
    : :
    : : If CentexTable.State = adStateOpen Then
    : : CentexTable.Close
    : : End If
    : :
    : : With CentexTable
    : : .ActiveConnection = CentexDB
    : : .CursorLocation = adUseClient
    : : .CursorType = adOpenStatic
    : : .LockType = adLockOptimistic
    : : .Source = "SELECT * FROM CentexInfo"
    : : .Open
    : : End With
    : :
    : : End Function
    : :
    : : [/code]
    : :
    : : Those are both functions I open up on form load to view the data. Someone please help me, the company I am building this program for does not wish to buy microsoft SQL server to handle database solutions, they wish to use microsoft access.
    : :
    : : Thank you,
    : : The Darthmoob
    : :
    :
    :
    : Two quick questions:
    :
    : [b][Leftbr]1.[rightbr][/b] Is this a split database, or an all in one
    : application. From the description I am thinking that it is all in
    : one. You may want to consider,splitting the application and database
    : portion, having the users use their own local copy of the front end in
    : MDE format and having a seperate back end.
    :
    : [b][Leftbr]2.[rightbr][/b] What is the open mode for this database.
    : You can check it by accessing the Tools/Options menu and going to the
    : advanced tab, in Access 2003, I do not remember the tab name in Access
    : 2000. If the system is not in shared mode, that would eplain the
    : problem.
    :
    : This can be checked in code:
    : [code]
    : [Blue][b]
    : If CurrentProject.Connection.Mode = adModeShareExclusive Then
    : [green]'The value is 12 and the Database is Exclusive.
    : [blue]
    : If CurretProject.Connection.Mode = adModeShareDenyNone Then
    : [green] The value is 16 and it is Shared.
    : [/blue][/b]
    : [/code]
    :
    : [black]
    : HTH
    : RickMan
    :
    :
    :
    :
    :
    :

  • RickManRickMan Posts: 7Member
    : Hey thanks Rickman,
    :
    : The database at the moment is not exclusive, but shared. Also the way the program works is it is a standalone visual basic application user interface. The database is a simple .mdb on a shared directory on the network. The visual basic program uses microsoft jet to open the database and update using the simple.
    :
    : ADODB.Recordset.Fields("Name").Value = TxtName.Text
    : ADODB.Recordset.Update
    :
    : This is my first attempt at making a shared Database over the network without useing SQL server and only using an Access Database. I do not have the program saved as an MDE format, it is still in its .mdb format. Do I need to convert it to MDE? I was reading that Access has what it calls Record Locking, is that why when a second client tries to open the database with the visual basic application it says it is already open?
    :
    : Thank you Rick for all your help.
    :
    : The Darthmoob
    :
    : : [b][red]This message was edited by RickMan at 2005-12-9 14:45:48[/red][/b][hr]
    : : [b][red]This message was edited by RickMan at 2005-12-9 14:45:6[/red][/b][hr]
    : : : I wrote this program with ADO connection string to a microsoft access database (.mdb) file. The program is working great when used as a single computer, but I need it to be a shared multiuser database system. I have the DB shared on the network, and mapp the directory as a drive on the client station. When the program runs, it opens the database by the mapped drive directory. Now when I open the program and database to view data on one client, when I open the other client I get an error saying
    : : :
    : : : Run-Time Error
    : : : Cannot open file "; File already in use.
    : : :
    : : : I am including the code at the bottome because I don't know if its a setting I need to fix in my ado connection or recordset connection setting.
    : : :
    : : : [code]
    : : : Public Function Open_CentexDB()
    : : : If CentexDB.State = adStateOpen Then
    : : : CentexDB.Close
    : : : End If
    : : :
    : : : CentexDriveLetter.MoveFirst
    : : : DBDriveLetter = CentexDriveLetter.Fields("Drive").Value
    : : :
    : : : CentexDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;persist security info =false;data source=" & DBDriveLetter & ":databasesCentex.mdb"
    : : :
    : : : End Function
    : : :
    : : : Public Function Open_CentexTable()
    : : :
    : : : If CentexTable.State = adStateOpen Then
    : : : CentexTable.Close
    : : : End If
    : : :
    : : : With CentexTable
    : : : .ActiveConnection = CentexDB
    : : : .CursorLocation = adUseClient
    : : : .CursorType = adOpenStatic
    : : : .LockType = adLockOptimistic
    : : : .Source = "SELECT * FROM CentexInfo"
    : : : .Open
    : : : End With
    : : :
    : : : End Function
    : : :
    : : : [/code]
    : : :
    : : : Those are both functions I open up on form load to view the data. Someone please help me, the company I am building this program for does not wish to buy microsoft SQL server to handle database solutions, they wish to use microsoft access.
    : : :
    : : : Thank you,
    : : : The Darthmoob
    : : :
    : :
    : :
    : : Two quick questions:
    : :
    : : [b][Leftbr]1.[rightbr][/b] Is this a split database, or an all in one
    : : application. From the description I am thinking that it is all in
    : : one. You may want to consider,splitting the application and database
    : : portion, having the users use their own local copy of the front end in
    : : MDE format and having a seperate back end.
    : :
    : : [b][Leftbr]2.[rightbr][/b] What is the open mode for this database.
    : : You can check it by accessing the Tools/Options menu and going to the
    : : advanced tab, in Access 2003, I do not remember the tab name in Access
    : : 2000. If the system is not in shared mode, that would eplain the
    : : problem.
    : :
    : : This can be checked in code:
    : : [code]
    : : [Blue][b]
    : : If CurrentProject.Connection.Mode = adModeShareExclusive Then
    : : [green]'The value is 12 and the Database is Exclusive.
    : : [blue]
    : : If CurretProject.Connection.Mode = adModeShareDenyNone Then
    : : [green] The value is 16 and it is Shared.
    : : [/blue][/b]
    : : [/code]
    : :
    : : [black]
    : : HTH
    : : RickMan
    : :
    : :
    : :
    : :
    : :
    : :
    :
    :
    DarthMoob,

    So the front end application and the simple.mdb are two seperate files. Are you sure that the database is opened in shared mode. The code I passed above will let you check this when you are in the mdb you are checking programatically.

    I am away form my regular system, so I cannot check, did you say what version of Access you are using. The response that you get from the database seems to point to the simple db being set as Exclusive. This would cause the message for the second connection to the simple.mdb to receive an locked db error message. You should open the Simple.mdb in access go to the tools/options, on the advanced tab you will find the [b]Default Open Mode:[/b] [italic]Shared or Exclusive[/italic], and the [b]Default Record Locking:[/b] [italic] No Locks, All Records, Edited Record[/italic]. I would set those to Shared and No Locks and set the mode in your connection string and set the locking when opening the record set.

    RickMan
  • darthmoobeydarthmoobey Posts: 140Member
    : : Hey thanks Rickman,
    : :
    : : The database at the moment is not exclusive, but shared. Also the way the program works is it is a standalone visual basic application user interface. The database is a simple .mdb on a shared directory on the network. The visual basic program uses microsoft jet to open the database and update using the simple.
    : :
    : : ADODB.Recordset.Fields("Name").Value = TxtName.Text
    : : ADODB.Recordset.Update
    : :
    : : This is my first attempt at making a shared Database over the network without useing SQL server and only using an Access Database. I do not have the program saved as an MDE format, it is still in its .mdb format. Do I need to convert it to MDE? I was reading that Access has what it calls Record Locking, is that why when a second client tries to open the database with the visual basic application it says it is already open?
    : :
    : : Thank you Rick for all your help.
    : :
    : : The Darthmoob
    : :
    : : : [b][red]This message was edited by RickMan at 2005-12-9 14:45:48[/red][/b][hr]
    : : : [b][red]This message was edited by RickMan at 2005-12-9 14:45:6[/red][/b][hr]
    : : : : I wrote this program with ADO connection string to a microsoft access database (.mdb) file. The program is working great when used as a single computer, but I need it to be a shared multiuser database system. I have the DB shared on the network, and mapp the directory as a drive on the client station. When the program runs, it opens the database by the mapped drive directory. Now when I open the program and database to view data on one client, when I open the other client I get an error saying
    : : : :
    : : : : Run-Time Error
    : : : : Cannot open file "; File already in use.
    : : : :
    : : : : I am including the code at the bottome because I don't know if its a setting I need to fix in my ado connection or recordset connection setting.
    : : : :
    : : : : [code]
    : : : : Public Function Open_CentexDB()
    : : : : If CentexDB.State = adStateOpen Then
    : : : : CentexDB.Close
    : : : : End If
    : : : :
    : : : : CentexDriveLetter.MoveFirst
    : : : : DBDriveLetter = CentexDriveLetter.Fields("Drive").Value
    : : : :
    : : : : CentexDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;persist security info =false;data source=" & DBDriveLetter & ":databasesCentex.mdb"
    : : : :
    : : : : End Function
    : : : :
    : : : : Public Function Open_CentexTable()
    : : : :
    : : : : If CentexTable.State = adStateOpen Then
    : : : : CentexTable.Close
    : : : : End If
    : : : :
    : : : : With CentexTable
    : : : : .ActiveConnection = CentexDB
    : : : : .CursorLocation = adUseClient
    : : : : .CursorType = adOpenStatic
    : : : : .LockType = adLockOptimistic
    : : : : .Source = "SELECT * FROM CentexInfo"
    : : : : .Open
    : : : : End With
    : : : :
    : : : : End Function
    : : : :
    : : : : [/code]
    : : : :
    : : : : Those are both functions I open up on form load to view the data. Someone please help me, the company I am building this program for does not wish to buy microsoft SQL server to handle database solutions, they wish to use microsoft access.
    : : : :
    : : : : Thank you,
    : : : : The Darthmoob
    : : : :
    : : :
    : : :
    : : : Two quick questions:
    : : :
    : : : [b][Leftbr]1.[rightbr][/b] Is this a split database, or an all in one
    : : : application. From the description I am thinking that it is all in
    : : : one. You may want to consider,splitting the application and database
    : : : portion, having the users use their own local copy of the front end in
    : : : MDE format and having a seperate back end.
    : : :
    : : : [b][Leftbr]2.[rightbr][/b] What is the open mode for this database.
    : : : You can check it by accessing the Tools/Options menu and going to the
    : : : advanced tab, in Access 2003, I do not remember the tab name in Access
    : : : 2000. If the system is not in shared mode, that would eplain the
    : : : problem.
    : : :
    : : : This can be checked in code:
    : : : [code]
    : : : [Blue][b]
    : : : If CurrentProject.Connection.Mode = adModeShareExclusive Then
    : : : [green]'The value is 12 and the Database is Exclusive.
    : : : [blue]
    : : : If CurretProject.Connection.Mode = adModeShareDenyNone Then
    : : : [green] The value is 16 and it is Shared.
    : : : [/blue][/b]
    : : : [/code]
    : : :
    : : : [black]
    : : : HTH
    : : : RickMan
    : : :
    : : :
    : : :
    : : :
    : : :
    : : :
    : :
    : :
    : DarthMoob,
    :
    : So the front end application and the simple.mdb are two seperate files. Are you sure that the database is opened in shared mode. The code I passed above will let you check this when you are in the mdb you are checking programatically.
    :
    : I am away form my regular system, so I cannot check, did you say what version of Access you are using. The response that you get from the database seems to point to the simple db being set as Exclusive. This would cause the message for the second connection to the simple.mdb to receive an locked db error message. You should open the Simple.mdb in access go to the tools/options, on the advanced tab you will find the [b]Default Open Mode:[/b] [italic]Shared or Exclusive[/italic], and the [b]Default Record Locking:[/b] [italic] No Locks, All Records, Edited Record[/italic]. I would set those to Shared and No Locks and set the mode in your connection string and set the locking when opening the record set.
    :
    : RickMan
    :


    Ok,

    The database is shared, and no locks in the options window. I checked. I am curious though, the network technitian that set up the shared directory and mapped the drive, could not answer me when I asked if he allowed more than one user to "Access" the database over the network. I am refering to the section when on a domain network, and you set user access permisions to the file, there is a small section that specifies maximum number of users to use the file at one time. Could the error be that the network technitian did not share the database to more than one user, leaving it at one user per access and that is what is giving my program the error? Or would I get a significantly different error if that was the case?

    The Darthmoob
  • RickManRickMan Posts: 7Member
    : : : Hey thanks Rickman,
    : : :
    : : : The database at the moment is not exclusive, but shared. Also the way the program works is it is a standalone visual basic application user interface. The database is a simple .mdb on a shared directory on the network. The visual basic program uses microsoft jet to open the database and update using the simple.
    : : :
    : : : ADODB.Recordset.Fields("Name").Value = TxtName.Text
    : : : ADODB.Recordset.Update
    : : :
    : : : This is my first attempt at making a shared Database over the network without useing SQL server and only using an Access Database. I do not have the program saved as an MDE format, it is still in its .mdb format. Do I need to convert it to MDE? I was reading that Access has what it calls Record Locking, is that why when a second client tries to open the database with the visual basic application it says it is already open?
    : : :
    : : : Thank you Rick for all your help.
    : : :
    : : : The Darthmoob
    : : :
    : : : : [b][red]This message was edited by RickMan at 2005-12-9 14:45:48[/red][/b][hr]
    : : : : [b][red]This message was edited by RickMan at 2005-12-9 14:45:6[/red][/b][hr]
    : : : : : I wrote this program with ADO connection string to a microsoft access database (.mdb) file. The program is working great when used as a single computer, but I need it to be a shared multiuser database system. I have the DB shared on the network, and mapp the directory as a drive on the client station. When the program runs, it opens the database by the mapped drive directory. Now when I open the program and database to view data on one client, when I open the other client I get an error saying
    : : : : :
    : : : : : Run-Time Error
    : : : : : Cannot open file "; File already in use.
    : : : : :
    : : : : : I am including the code at the bottome because I don't know if its a setting I need to fix in my ado connection or recordset connection setting.
    : : : : :
    : : : : : [code]
    : : : : : Public Function Open_CentexDB()
    : : : : : If CentexDB.State = adStateOpen Then
    : : : : : CentexDB.Close
    : : : : : End If
    : : : : :
    : : : : : CentexDriveLetter.MoveFirst
    : : : : : DBDriveLetter = CentexDriveLetter.Fields("Drive").Value
    : : : : :
    : : : : : CentexDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;persist security info =false;data source=" & DBDriveLetter & ":databasesCentex.mdb"
    : : : : :
    : : : : : End Function
    : : : : :
    : : : : : Public Function Open_CentexTable()
    : : : : :
    : : : : : If CentexTable.State = adStateOpen Then
    : : : : : CentexTable.Close
    : : : : : End If
    : : : : :
    : : : : : With CentexTable
    : : : : : .ActiveConnection = CentexDB
    : : : : : .CursorLocation = adUseClient
    : : : : : .CursorType = adOpenStatic
    : : : : : .LockType = adLockOptimistic
    : : : : : .Source = "SELECT * FROM CentexInfo"
    : : : : : .Open
    : : : : : End With
    : : : : :
    : : : : : End Function
    : : : : :
    : : : : : [/code]
    : : : : :
    : : : : : Those are both functions I open up on form load to view the data. Someone please help me, the company I am building this program for does not wish to buy microsoft SQL server to handle database solutions, they wish to use microsoft access.
    : : : : :
    : : : : : Thank you,
    : : : : : The Darthmoob
    : : : : :
    : : : :
    : : : :
    : : : : Two quick questions:
    : : : :
    : : : : [b][Leftbr]1.[rightbr][/b] Is this a split database, or an all in one
    : : : : application. From the description I am thinking that it is all in
    : : : : one. You may want to consider,splitting the application and database
    : : : : portion, having the users use their own local copy of the front end in
    : : : : MDE format and having a seperate back end.
    : : : :
    : : : : [b][Leftbr]2.[rightbr][/b] What is the open mode for this database.
    : : : : You can check it by accessing the Tools/Options menu and going to the
    : : : : advanced tab, in Access 2003, I do not remember the tab name in Access
    : : : : 2000. If the system is not in shared mode, that would eplain the
    : : : : problem.
    : : : :
    : : : : This can be checked in code:
    : : : : [code]
    : : : : [Blue][b]
    : : : : If CurrentProject.Connection.Mode = adModeShareExclusive Then
    : : : : [green]'The value is 12 and the Database is Exclusive.
    : : : : [blue]
    : : : : If CurretProject.Connection.Mode = adModeShareDenyNone Then
    : : : : [green] The value is 16 and it is Shared.
    : : : : [/blue][/b]
    : : : : [/code]
    : : : :
    : : : : [black]
    : : : : HTH
    : : : : RickMan
    : : : :
    : : : :
    : : : :
    : : : :
    : : : :
    : : : :
    : : :
    : : :
    : : DarthMoob,
    : :
    : : So the front end application and the simple.mdb are two seperate files. Are you sure that the database is opened in shared mode. The code I passed above will let you check this when you are in the mdb you are checking programatically.
    : :
    : : I am away form my regular system, so I cannot check, did you say what version of Access you are using. The response that you get from the database seems to point to the simple db being set as Exclusive. This would cause the message for the second connection to the simple.mdb to receive an locked db error message. You should open the Simple.mdb in access go to the tools/options, on the advanced tab you will find the [b]Default Open Mode:[/b] [italic]Shared or Exclusive[/italic], and the [b]Default Record Locking:[/b] [italic] No Locks, All Records, Edited Record[/italic]. I would set those to Shared and No Locks and set the mode in your connection string and set the locking when opening the record set.
    : :
    : : RickMan
    : :
    :
    :
    : Ok,
    :
    : The database is shared, and no locks in the options window. I checked. I am curious though, the network technitian that set up the shared directory and mapped the drive, could not answer me when I asked if he allowed more than one user to "Access" the database over the network. I am refering to the section when on a domain network, and you set user access permisions to the file, there is a small section that specifies maximum number of users to use the file at one time. Could the error be that the network technitian did not share the database to more than one user, leaving it at one user per access and that is what is giving my program the error? Or would I get a significantly different error if that was the case?
    :
    : The Darthmoob
    :

    What is the version of Windows is the server, that you are running? The permissions are usually set on the network share and how many users are allowed to access the share, not the file. I believe that the error you would get would be a network error at that point. But the real question on permissions, is who has Update permissions on the directory. Are you using a specific user name to connect in your connection string or are you using the current user? User Permissions can cause this type of problem you are getting, the user does not have update permission then the changes can't be saved.

    RickMan
  • darthmoobeydarthmoobey Posts: 140Member
    : : : : Hey thanks Rickman,
    : : : :
    : : : : The database at the moment is not exclusive, but shared. Also the way the program works is it is a standalone visual basic application user interface. The database is a simple .mdb on a shared directory on the network. The visual basic program uses microsoft jet to open the database and update using the simple.
    : : : :
    : : : : ADODB.Recordset.Fields("Name").Value = TxtName.Text
    : : : : ADODB.Recordset.Update
    : : : :
    : : : : This is my first attempt at making a shared Database over the network without useing SQL server and only using an Access Database. I do not have the program saved as an MDE format, it is still in its .mdb format. Do I need to convert it to MDE? I was reading that Access has what it calls Record Locking, is that why when a second client tries to open the database with the visual basic application it says it is already open?
    : : : :
    : : : : Thank you Rick for all your help.
    : : : :
    : : : : The Darthmoob
    : : : :
    : : : : : [b][red]This message was edited by RickMan at 2005-12-9 14:45:48[/red][/b][hr]
    : : : : : [b][red]This message was edited by RickMan at 2005-12-9 14:45:6[/red][/b][hr]
    : : : : : : I wrote this program with ADO connection string to a microsoft access database (.mdb) file. The program is working great when used as a single computer, but I need it to be a shared multiuser database system. I have the DB shared on the network, and mapp the directory as a drive on the client station. When the program runs, it opens the database by the mapped drive directory. Now when I open the program and database to view data on one client, when I open the other client I get an error saying
    : : : : : :
    : : : : : : Run-Time Error
    : : : : : : Cannot open file "; File already in use.
    : : : : : :
    : : : : : : I am including the code at the bottome because I don't know if its a setting I need to fix in my ado connection or recordset connection setting.
    : : : : : :
    : : : : : : [code]
    : : : : : : Public Function Open_CentexDB()
    : : : : : : If CentexDB.State = adStateOpen Then
    : : : : : : CentexDB.Close
    : : : : : : End If
    : : : : : :
    : : : : : : CentexDriveLetter.MoveFirst
    : : : : : : DBDriveLetter = CentexDriveLetter.Fields("Drive").Value
    : : : : : :
    : : : : : : CentexDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;persist security info =false;data source=" & DBDriveLetter & ":databasesCentex.mdb"
    : : : : : :
    : : : : : : End Function
    : : : : : :
    : : : : : : Public Function Open_CentexTable()
    : : : : : :
    : : : : : : If CentexTable.State = adStateOpen Then
    : : : : : : CentexTable.Close
    : : : : : : End If
    : : : : : :
    : : : : : : With CentexTable
    : : : : : : .ActiveConnection = CentexDB
    : : : : : : .CursorLocation = adUseClient
    : : : : : : .CursorType = adOpenStatic
    : : : : : : .LockType = adLockOptimistic
    : : : : : : .Source = "SELECT * FROM CentexInfo"
    : : : : : : .Open
    : : : : : : End With
    : : : : : :
    : : : : : : End Function
    : : : : : :
    : : : : : : [/code]
    : : : : : :
    : : : : : : Those are both functions I open up on form load to view the data. Someone please help me, the company I am building this program for does not wish to buy microsoft SQL server to handle database solutions, they wish to use microsoft access.
    : : : : : :
    : : : : : : Thank you,
    : : : : : : The Darthmoob
    : : : : : :
    : : : : :
    : : : : :
    : : : : : Two quick questions:
    : : : : :
    : : : : : [b][Leftbr]1.[rightbr][/b] Is this a split database, or an all in one
    : : : : : application. From the description I am thinking that it is all in
    : : : : : one. You may want to consider,splitting the application and database
    : : : : : portion, having the users use their own local copy of the front end in
    : : : : : MDE format and having a seperate back end.
    : : : : :
    : : : : : [b][Leftbr]2.[rightbr][/b] What is the open mode for this database.
    : : : : : You can check it by accessing the Tools/Options menu and going to the
    : : : : : advanced tab, in Access 2003, I do not remember the tab name in Access
    : : : : : 2000. If the system is not in shared mode, that would eplain the
    : : : : : problem.
    : : : : :
    : : : : : This can be checked in code:
    : : : : : [code]
    : : : : : [Blue][b]
    : : : : : If CurrentProject.Connection.Mode = adModeShareExclusive Then
    : : : : : [green]'The value is 12 and the Database is Exclusive.
    : : : : : [blue]
    : : : : : If CurretProject.Connection.Mode = adModeShareDenyNone Then
    : : : : : [green] The value is 16 and it is Shared.
    : : : : : [/blue][/b]
    : : : : : [/code]
    : : : : :
    : : : : : [black]
    : : : : : HTH
    : : : : : RickMan
    : : : : :
    : : : : :
    : : : : :
    : : : : :
    : : : : :
    : : : : :
    : : : :
    : : : :
    : : : DarthMoob,
    : : :
    : : : So the front end application and the simple.mdb are two seperate files. Are you sure that the database is opened in shared mode. The code I passed above will let you check this when you are in the mdb you are checking programatically.
    : : :
    : : : I am away form my regular system, so I cannot check, did you say what version of Access you are using. The response that you get from the database seems to point to the simple db being set as Exclusive. This would cause the message for the second connection to the simple.mdb to receive an locked db error message. You should open the Simple.mdb in access go to the tools/options, on the advanced tab you will find the [b]Default Open Mode:[/b] [italic]Shared or Exclusive[/italic], and the [b]Default Record Locking:[/b] [italic] No Locks, All Records, Edited Record[/italic]. I would set those to Shared and No Locks and set the mode in your connection string and set the locking when opening the record set.
    : : :
    : : : RickMan
    : : :
    : :
    : :
    : : Ok,
    : :
    : : The database is shared, and no locks in the options window. I checked. I am curious though, the network technitian that set up the shared directory and mapped the drive, could not answer me when I asked if he allowed more than one user to "Access" the database over the network. I am refering to the section when on a domain network, and you set user access permisions to the file, there is a small section that specifies maximum number of users to use the file at one time. Could the error be that the network technitian did not share the database to more than one user, leaving it at one user per access and that is what is giving my program the error? Or would I get a significantly different error if that was the case?
    : :
    : : The Darthmoob
    : :
    :
    : What is the version of Windows is the server, that you are running? The permissions are usually set on the network share and how many users are allowed to access the share, not the file. I believe that the error you would get would be a network error at that point. But the real question on permissions, is who has Update permissions on the directory. Are you using a specific user name to connect in your connection string or are you using the current user? User Permissions can cause this type of problem you are getting, the user does not have update permission then the changes can't be saved.
    :
    : RickMan
    :

    Far as I know the operating system acting as the "Server" where the .mdb is located is a windowsXP second edition system. I am not entirely certain what settings the network tech has set on the directory and file. I admit much that I am not a network technitian, I know only what I remember from two semesters in college. From the information given to me by the Network Technitian, the windows XP "server" or computer hosting the .mdb is on a domain controlled network with a windows server 2k3 platform. The directory is shared, based by username within the domain tree, of all people that should have access to the file. They are all given full control to read and write via network share and accessability.

    The connection string I am using for the program is exactly as posted before, I am not passing any usernames or passwords. I figured the network is secured by the logon and mapped drive anyway, there would be no "Need" to add that. The network technitian did not specify, because he was not sure or could not remember what setting was placed for the, "amount of users" to access the file. I researched and found windows XP will only allow 10 concurrent connections to a shared directory, which is ok because the only ones that use this program are 5 users. I do not understand what the error is or where its coming from as other programs I have made, with the same connection string work great.

    Darthmoob
  • RickManRickMan Posts: 7Member
    : : : : : Hey thanks Rickman,
    : : : : :
    : : : : : The database at the moment is not exclusive, but shared. Also the way the program works is it is a standalone visual basic application user interface. The database is a simple .mdb on a shared directory on the network. The visual basic program uses microsoft jet to open the database and update using the simple.
    : : : : :
    : : : : : ADODB.Recordset.Fields("Name").Value = TxtName.Text
    : : : : : ADODB.Recordset.Update
    : : : : :
    : : : : : This is my first attempt at making a shared Database over the network without useing SQL server and only using an Access Database. I do not have the program saved as an MDE format, it is still in its .mdb format. Do I need to convert it to MDE? I was reading that Access has what it calls Record Locking, is that why when a second client tries to open the database with the visual basic application it says it is already open?
    : : : : :
    : : : : : Thank you Rick for all your help.
    : : : : :
    : : : : : The Darthmoob
    : : : : :
    : : : : : : [b][red]This message was edited by RickMan at 2005-12-9 14:45:48[/red][/b][hr]
    : : : : : : [b][red]This message was edited by RickMan at 2005-12-9 14:45:6[/red][/b][hr]
    : : : : : : : I wrote this program with ADO connection string to a microsoft access database (.mdb) file. The program is working great when used as a single computer, but I need it to be a shared multiuser database system. I have the DB shared on the network, and mapp the directory as a drive on the client station. When the program runs, it opens the database by the mapped drive directory. Now when I open the program and database to view data on one client, when I open the other client I get an error saying
    : : : : : : :
    : : : : : : : Run-Time Error
    : : : : : : : Cannot open file "; File already in use.
    : : : : : : :
    : : : : : : : I am including the code at the bottome because I don't know if its a setting I need to fix in my ado connection or recordset connection setting.
    : : : : : : :
    : : : : : : : [code]
    : : : : : : : Public Function Open_CentexDB()
    : : : : : : : If CentexDB.State = adStateOpen Then
    : : : : : : : CentexDB.Close
    : : : : : : : End If
    : : : : : : :
    : : : : : : : CentexDriveLetter.MoveFirst
    : : : : : : : DBDriveLetter = CentexDriveLetter.Fields("Drive").Value
    : : : : : : :
    : : : : : : : CentexDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;persist security info =false;data source=" & DBDriveLetter & ":databasesCentex.mdb"
    : : : : : : :
    : : : : : : : End Function
    : : : : : : :
    : : : : : : : Public Function Open_CentexTable()
    : : : : : : :
    : : : : : : : If CentexTable.State = adStateOpen Then
    : : : : : : : CentexTable.Close
    : : : : : : : End If
    : : : : : : :
    : : : : : : : With CentexTable
    : : : : : : : .ActiveConnection = CentexDB
    : : : : : : : .CursorLocation = adUseClient
    : : : : : : : .CursorType = adOpenStatic
    : : : : : : : .LockType = adLockOptimistic
    : : : : : : : .Source = "SELECT * FROM CentexInfo"
    : : : : : : : .Open
    : : : : : : : End With
    : : : : : : :
    : : : : : : : End Function
    : : : : : : :
    : : : : : : : [/code]
    : : : : : : :
    : : : : : : : Those are both functions I open up on form load to view the data. Someone please help me, the company I am building this program for does not wish to buy microsoft SQL server to handle database solutions, they wish to use microsoft access.
    : : : : : : :
    : : : : : : : Thank you,
    : : : : : : : The Darthmoob
    : : : : : : :
    : : : : : :
    : : : : : :
    : : : : : : Two quick questions:
    : : : : : :
    : : : : : : [b][Leftbr]1.[rightbr][/b] Is this a split database, or an all in one
    : : : : : : application. From the description I am thinking that it is all in
    : : : : : : one. You may want to consider,splitting the application and database
    : : : : : : portion, having the users use their own local copy of the front end in
    : : : : : : MDE format and having a seperate back end.
    : : : : : :
    : : : : : : [b][Leftbr]2.[rightbr][/b] What is the open mode for this database.
    : : : : : : You can check it by accessing the Tools/Options menu and going to the
    : : : : : : advanced tab, in Access 2003, I do not remember the tab name in Access
    : : : : : : 2000. If the system is not in shared mode, that would eplain the
    : : : : : : problem.
    : : : : : :
    : : : : : : This can be checked in code:
    : : : : : : [code]
    : : : : : : [Blue][b]
    : : : : : : If CurrentProject.Connection.Mode = adModeShareExclusive Then
    : : : : : : [green]'The value is 12 and the Database is Exclusive.
    : : : : : : [blue]
    : : : : : : If CurretProject.Connection.Mode = adModeShareDenyNone Then
    : : : : : : [green] The value is 16 and it is Shared.
    : : : : : : [/blue][/b]
    : : : : : : [/code]
    : : : : : :
    : : : : : : [black]
    : : : : : : HTH
    : : : : : : RickMan
    : : : : : :
    : : : : : :
    : : : : : :
    : : : : : :
    : : : : : :
    : : : : : :
    : : : : :
    : : : : :
    : : : : DarthMoob,
    : : : :
    : : : : So the front end application and the simple.mdb are two seperate files. Are you sure that the database is opened in shared mode. The code I passed above will let you check this when you are in the mdb you are checking programatically.
    : : : :
    : : : : I am away form my regular system, so I cannot check, did you say what version of Access you are using. The response that you get from the database seems to point to the simple db being set as Exclusive. This would cause the message for the second connection to the simple.mdb to receive an locked db error message. You should open the Simple.mdb in access go to the tools/options, on the advanced tab you will find the [b]Default Open Mode:[/b] [italic]Shared or Exclusive[/italic], and the [b]Default Record Locking:[/b] [italic] No Locks, All Records, Edited Record[/italic]. I would set those to Shared and No Locks and set the mode in your connection string and set the locking when opening the record set.
    : : : :
    : : : : RickMan
    : : : :
    : : :
    : : :
    : : : Ok,
    : : :
    : : : The database is shared, and no locks in the options window. I checked. I am curious though, the network technitian that set up the shared directory and mapped the drive, could not answer me when I asked if he allowed more than one user to "Access" the database over the network. I am refering to the section when on a domain network, and you set user access permisions to the file, there is a small section that specifies maximum number of users to use the file at one time. Could the error be that the network technitian did not share the database to more than one user, leaving it at one user per access and that is what is giving my program the error? Or would I get a significantly different error if that was the case?
    : : :
    : : : The Darthmoob
    : : :
    : :
    : : What is the version of Windows is the server, that you are running? The permissions are usually set on the network share and how many users are allowed to access the share, not the file. I believe that the error you would get would be a network error at that point. But the real question on permissions, is who has Update permissions on the directory. Are you using a specific user name to connect in your connection string or are you using the current user? User Permissions can cause this type of problem you are getting, the user does not have update permission then the changes can't be saved.
    : :
    : : RickMan
    : :
    :
    : Far as I know the operating system acting as the "Server" where the .mdb is located is a windowsXP second edition system. I am not entirely certain what settings the network tech has set on the directory and file. I admit much that I am not a network technitian, I know only what I remember from two semesters in college. From the information given to me by the Network Technitian, the windows XP "server" or computer hosting the .mdb is on a domain controlled network with a windows server 2k3 platform. The directory is shared, based by username within the domain tree, of all people that should have access to the file. They are all given full control to read and write via network share and accessability.
    :
    : The connection string I am using for the program is exactly as posted before, I am not passing any usernames or passwords. I figured the network is secured by the logon and mapped drive anyway, there would be no "Need" to add that. The network technitian did not specify, because he was not sure or could not remember what setting was placed for the, "amount of users" to access the file. I researched and found windows XP will only allow 10 concurrent connections to a shared directory, which is ok because the only ones that use this program are 5 users. I do not understand what the error is or where its coming from as other programs I have made, with the same connection string work great.
    :
    : Darthmoob
    :

    The only thing that we do not know is what the permissions on the directory are. Because as I said before, if the users do not have update, which is at a high level Read, Write, Modify. You need to be able to modify to change the MDB file, write to allow for the creation of ldb file, and delete to allow the ldb file to be deleted when it is no longer needed. If you do not have these rights it will not be able to perform updates on the file.

    Let me know what the rights are. And we can go from there.

    RickMan
  • darthmoobeydarthmoobey Posts: 140Member
    Thanks Rickman, but yeah the problem was not with the program at all. It was the network technitian did not share the resource properly over the network. So when the program would try to access the DB, it would lock it and not allow it to open. It was quite frustrating.. hehe

    The Darthmoob
Sign In or Register to comment.