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.Connectio n
Dim myRecordset As ADODB.Recordset
Dim pathstring As String
Dim str As String

Set myConnection = New ADODB.Connectio n
Set myRecordset = New ADODB.Recordset
pathstring = Application.Cur rentProject.Ful lName


str = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source= " & pathstring & ";Persist Security Info=False"
myConnection.Co nnectionString = str

'-Open the Connection --
myConnection.Op en

'Determine if we conected.
If myConnection.St ate = adStateOpen Then

myRecordset.Ope n "Select * From TEMP", myConnection, adOpenDynamic, adLockOptimisti c, adCmdText

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

'-just to be sure --
myRecordset.Mov eFirst

On Error GoTo transError

'-here is the top of the transaction--
myConnection.Be ginTrans

While Not myRecordset.EOF
mcounter = mcounter + 1

myRecordset!Sec tor = "5"
myRecordset.Upd ate
myRecordset.Mov eNext
Wend


myConnection.Cl ose
myRecordset.Clo se
myConnection.Cl ose

Exit Sub

transError:
myConnection.Ro llbackTrans
myRecordset.Clo se
myConnection.Cl ose
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.ConnectionStrin g = "driver={SQ L Server};" & _
"server=srv;uid =sa;pwd=pwd;dat abase=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.ConnectionStrin g = "driver={SQ L Server};" & _
"server=srv;uid =sa;pwd=pwd;dat abase=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.ConnectionStrin g = "driver={SQ L Server};" & _
"server=srv;uid =sa;pwd=pwd;dat abase=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.