Single user, designing module, err: database has been placed in a state by user 'Admi

[sharon.chapman7]

Hi,
I have an MS Access 2010 database with a module called bas This module reads the fields on a Microsoft Word document and then the data is loaded to a linked SQL Server 2008 R2 table. I am currently desigining this module so I am the only user using this database. I open the MS Access database, open the code for the module and click on Debug>Run. I get the following error:
-2147467259: The Database has been placed in a state by user ‘Admin’ on machine ‘JITC-PC’ that prevents it from being opened or locked.
How do I get rid of this error? Does any one have a solution? I have searched the Internet all week but haven't found a solution that works.

Here is my code:


Sub GetWordData()
Dim appWord As Word.Application
Dim doc As Word.Document
Dim cnn As New ADODB.Connection
Dim cnn2 As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim rst2 As New ADODB.Recordset
Dim strDocName As String
Dim blnQuitWord As Boolean

On Error GoTo ErrorHandling

strDocName = "\\JITC-PC\Users\Sharon\My Documents\GEOINT Repository\Requests\GEOINT_rep_req_form20111109 class.doc"
Set appWord = GetObject(, "Word.Application")
Set doc = appWord.Documents.Open(strDocName)

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=\\JITC-PC\Users\Sharon\Desktop\DEVELOPMENT.mdb"


' Open Requester table to load
rst.Open "dbo_Requester", cnn, adOpenKeyset, adLockOptimistic

With rst
.AddNew
!Requester_Organization = doc.FormFields("Req_Org").result
.Update
.Close
End With

doc.Close

If blnQuitWord Then appWord.Quit
cnn.Close
MsgBox "Requestor Data Imported!"

Cleanup:
Set rst = Nothing
Set cnn = Nothing
Set doc = Nothing
Set appWord = Nothing

Exit Sub

ErrorHandling:
Select Case Err
Case -2147022986, 429
Set appWord = CreateObject("Word.Application")
blnQuitWord = True
Resume Next
Case 5121, 5174
MsgBox "You must select a valid Word document. " _
& "No Data Imported.", vbOKOnly, _
"Word Document Not Found"
Case 5941
MsgBox "This Field is not found in the Word Document." _
& "No Data Imported.", vbOKOnly, _
"Fields not found in the Word Document"
Case Else
MsgBox Err & ": " & Err.Description
End Select
GoTo Cleanup

End Sub

Thank you for any help you can give me.

Sharon Chapman

 

[boblarson]

If the database this code is in is the same one you are referring to in this code:

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=\\JITC-PC\Users\Sharon\Desktop\DEVELOPMENT.mdb"

Then you need to change your code from this:

Dim cnn As New ADODB.Connection

To this

Dim cnn As ADODB.Connection

And then change this:

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=\\JITC-PC\Users\Sharon\Desktop\DEVELOPMENT.mdb"

To this

Set cnn = CurrentProject.Connection

 

[sharon.chapman7]

thanks for the quick reply. I tried these suggestions and I still get the error. I have tried shutting off my pc and restarting it. I have restarted the app and I still get this error.

Sharon

 

[sharon.chapman7]

Sorry,
I get a different error: 3709: the connection cannot be used to perform this operation. It is either closed or invalid in this context.

It is getting this error when it tries to execute rst.Open "dbo.Requester, cnn, adOpenKeyset, adLockOptimistic

Sharon

 

[boblarson]

Do you have any other locations in code where you have tried to set the connection to:

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=\\JITC-PC\Users\Sharon\Desktop\DEVELOPMENT.mdb"

What if you change it up to use DAO instead? I assume that this is a linked SQL Server table so you could use:

Sub GetWordData()
    Dim appWord As Word.Application
    Dim doc As Word.Document

[B][COLOR=red]    Dim db As DAO.Database
    Dim rst As DAO.Recordset[/COLOR][/B]
 
    Dim strDocName As String
    Dim blnQuitWord As Boolean
 
    On Error GoTo ErrorHandling
 
    strDocName = "[URL="file://\\JITC-PC\Users\Sharon\My"]\\JITC-PC\Users\Sharon\My[/URL] Documents\GEOINT Repository\Requests\GEOINT_rep_req_form20111109 class.doc"

    Set appWord = GetObject(, "Word.Application")

    Set doc = appWord.Documents.Open(strDocName)
 
    [B][COLOR=red]Set db = CurrentDb[/COLOR][/B]

    ' Open Requester table to load
    [B][COLOR=red]Set rst = db.OpenRecordset("dbo_Requester", dbOpenDynaset, dbSeeChanges)[/COLOR][/B]
[B][COLOR=#ff0000][/COLOR][/B] 
    With rst
        .AddNew
        !Requester_Organization = doc.FormFields("Req_Org").result
        .Update
        .Close
    End With
 
    doc.Close
 
    If blnQuitWord Then appWord.Quit

    [B][COLOR=red]rst.Close[/COLOR][/B]
    MsgBox "Requestor Data Imported!"
 
Cleanup:
    Set rst = Nothing
    
    Set doc = Nothing
    Set appWord = Nothing
    Exit Sub
 
ErrorHandling:
    Select Case Err
    Case -2147022986, 429
        Set appWord = CreateObject("Word.Application")
        blnQuitWord = True
        Resume Next
    Case 5121, 5174
        MsgBox "You must select a valid Word document. " _
             & "No Data Imported.", vbOKOnly, _
               "Word Document Not Found"
    Case 5941
        MsgBox "This Field is not found in the Word Document." _
             & "No Data Imported.", vbOKOnly, _
               "Fields not found in the Word Document"
    Case Else
        MsgBox Err & ": " & Err.Description
    End Select
    GoTo Cleanup
End Sub

 

[sharon.chapman7]

Bob,
You have put me on the right track. I put the following into my code and it works:

set cnn = CurrentProject.Connection
rst.Open "dbo_Requester", cnn, adOpenKeyset, adLockOptimistic

Thanks for your help..Sharon