Runtime Error 2147467259

[sabloomer]

I have an Excel Book that several users have copied to their local computer. The Excel Book opens a connection to an Access database and transfers data the to Book using the CopyFromRecordset command.

All users have the same file, but only one gets the "Runtime Error 2147467259 The Database has been placed in a state by 'User' on machine 'PC' that prevents it from being open or locked" when the connection is opened.

The research I have done suggests that the issue is the way I am opening the connection, but I am not sure what I need to be changeing. Below is the code I am running at start-up.

Thank You,

sabloomer


Private Sub ADOImportFromAccessTable(TableName As String, TargetRange As Range)
' Example: ADOImportFromAccessTable "C:\FolderName\DataBaseName.mdb", _
"TableName", Range("C1")

Dim cn As ADODB.Connection, rs As New ADODB.Recordset, intColIndex As Integer
Dim strConnect As String

Set TargetRange = TargetRange.Cells(1, 1)
' open the database
Set cn = New ADODB.Connection

strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Password=pwrd;User ID=TheUser;" & _
"Data Source=" & gblPath & "DataSource.mdb;" & _
"Jet OLEDB:System database=M:\Security\LOCK.MDW"

cn.Open strConnect
Set rs = New ADODB.Recordset
With rs
' open the recordset
.Open TableName, cn, adOpenStatic, adLockOptimistic
' all records
'.Open "SELECT * FROM " & TableName & _
" WHERE [FieldName] = 'MyCriteria'", cn, , , adCmdText

If .EOF = True And .BOF = True Then
'There is no data to return
blnYesData = True
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
Exit Sub
End If

For intColIndex = 0 To rs.Fields.Count - 1 ' the field names
TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
Next
TargetRange.Offset(1, 0).CopyFromRecordset rs ' the recordset data

End With
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

 

[JerryKlmns]

I like the With .. End With block since you refer to the connection object only once and it is more readable

Set cn = New ADODB.Connection
With cn
   .Provider = "Microsoft.Jet.OLEDB.4.0"
   .Properties("Data Source") =  gblPath & "DataSource.mdb"
   .Properties("Jet OLEDB:System database") = "M:\Security\LOCK.MDW"
   .Properties("User ID") = "TheUser"
   .Properties("Password") = "pwrd"
'This determines if you allow others to read/write or whatever
   .Properties("Mode") = adModeShareDenyNone
   .Properties("Jet OLEDB:Engine Type") = 5
   .Properties("Persist Security Info") = False
   .Open
End With

 

[ClulessChris]

Does the user have file permissions set to see the database, and modifiy the folder the database is in (they must be able to create the locking file)?

Never knock on Death's door: ring the bell and run away! Death really hates that!