VBA on Access Click Event Returns DB Error

[luvbug]

I have a form with a button that when clicked should perform the following action on the current DB;

Open table TEMP and allow reading of the TEMP's "Sector" field to be stored in a variable for later comparison and update. Before I could even start coding the logic, I am getting the following error from access:

"The database has been placed in a state by user that prevents it from being opend or locked."

Here is my code (FYI: logic portion not complete yet):

Private Sub Command0_Click()
Dim myConnection As ADODB.Connection
Dim myRecordset As ADODB.Recordset
Dim pathstring As String
Dim str As String

Set myConnection = New ADODB.Connection
Set myRecordset = New ADODB.Recordset
pathstring = Application.CurrentProject.FullName


str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & pathstring & ";Persist Security Info=False"
myConnection.ConnectionString = str

'-Open the Connection --
myConnection.Open

'Determine if we conected.
If myConnection.State = adStateOpen Then

myRecordset.Open "Select * From TEMP", myConnection, adOpenDynamic, adLockOptimistic, adCmdText

Else
MsgBox "The connection could not be made."
myConnection.Close
Exit Sub
End If

'-just to be sure --
myRecordset.MoveFirst

On Error GoTo transError

'-here is the top of the transaction--
myConnection.BeginTrans

While Not myRecordset.EOF
mcounter = mcounter + 1

myRecordset!Sector = "5"
myRecordset.Update
myRecordset.MoveNext
Wend


myConnection.Close
myRecordset.Close
myConnection.Close

Exit Sub

transError:
myConnection.RollbackTrans
myRecordset.Close
myConnection.Close
MsgBox Err.Description

End Sub

[sierra7]

Hi
I'm not clear that the Table [TEMP] is in a remote database. If not you do not need all the connection string code.
You can lookup one value with

myVariable = DLookup("[Sector]", "TEMP", strCriteria)

The comma and strCriteria can be ommitted if you only have one record otherwise the strCriteria must state which record you want to look at e.g. "[ID]=1" or something. Your code would return all the values in [TEMP] unless you only have one record.

I have seen various syntax for connection strings. Check out this MS Article For ADODB it should look somethig like.

cnn1.ConnectionString = "driver={SQL Server};" & _
"server=srv;uid=sa;pwd=pwd;database=Pubs"

I believe that 'Persist Secuitity Info = False' is something to do with ADO.NET, which brings us full circle, What are you using ?
S7

[luvbug]

Sorry for the late reply and thanks. The database is not remote so I will remove the unessary connection string code and use the "DLookup".

Hi
I'm not clear that the Table [TEMP] is in a remote database. If not you do not need all the connection string code.
You can lookup one value with

myVariable = DLookup("[Sector]", "TEMP", strCriteria)

The comma and strCriteria can be ommitted if you only have one record otherwise the strCriteria must state which record you want to look at e.g. "[ID]=1" or something. Your code would return all the values in [TEMP] unless you only have one record.

I have seen various syntax for connection strings. Check out this MS Article For ADODB it should look somethig like.

cnn1.ConnectionString = "driver={SQL Server};" & _
"server=srv;uid=sa;pwd=pwd;database=Pubs"

I believe that 'Persist Secuitity Info = False' is something to do with ADO.NET, which brings us full circle, What are you using ?
S7

[luvbug]

BTW: Not using a database with password security.

Hi
I'm not clear that the Table [TEMP] is in a remote database. If not you do not need all the connection string code.
You can lookup one value with

myVariable = DLookup("[Sector]", "TEMP", strCriteria)

The comma and strCriteria can be ommitted if you only have one record otherwise the strCriteria must state which record you want to look at e.g. "[ID]=1" or something. Your code would return all the values in [TEMP] unless you only have one record.

I have seen various syntax for connection strings. Check out this MS Article For ADODB it should look somethig like.

cnn1.ConnectionString = "driver={SQL Server};" & _
"server=srv;uid=sa;pwd=pwd;database=Pubs"

I believe that 'Persist Secuitity Info = False' is something to do with ADO.NET, which brings us full circle, What are you using ?
S7

[FishVal]

Hi, luvbug.

You are opening a new connection to the same database the code is running in.
From the one hand it is just unnecessary wasting of resources as long as database already has an opened and always available ADO connection which is returned by CurrentProject.Connection property.
From the other hand after code modification and before the module is saved your database is in locked state. No wonder connection fails.

Kind regards,
Fish.