Opening a form with code

[MikeG0930]

I am trying to open a form with the following code, and getting an
error message "Run-time error '-2147467259(80004005 the database has
been placed in a state by user "Admin" on machine "" that prevents it
from being opened or locked."

Here is the code:
Option Compare Database

Private strSQL As String
Private cnnHIREPay As New ADODB.Connection
Private rstHIREPay As New ADODB.Recordset

Private Sub Form_Load()
With cnnHIREPay
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open CurrentProject.Path & "\HIRE Consumer Payroll.mdb",
"Admin"
End With

strSQL = "SELECT ConsumerName, CalDay, EEP1, DateWorked FROM
qryEEP_Cal"
With rstHIREPay
Set .ActiveConnection = cnnHIREPay
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open strSQL
End With

Set Me.Recordset = rstHIREPay
End Sub
Can someone help me please?
Thanks
Mike

 

[Graham Mandeno]

Hi Mike

This would normally indicate that somebody (possibly YOU) has the database
file open for exclusive access.

This form is not in "HIRE Consumer Payroll.mdb", is it? If so, then you
don't need to create a new connection to it. Just use
CurrentProject.Connection.

Otherwise, try to find what piece of code has that file open.

 

[MikeG0930]

You are correct Graham. The form is in HIRE Consumer Payroll.mdb. I'm
learning this stuff as I go. I used an example from a text book and
adapted it to work with my form. When I use CurrentProject.Connection,
do I need to remove or change the rest of the code?

Mike

 

[Graham Mandeno]

Hi Mike

I suspect that ALL of the code is unnecessary.

Simply set the RecordSource property of the form to:
SELECT ConsumerName, CalDay, EEP1, DateWorked FROM qryEEP_Cal

Or, even more simply, set it to:
qryEEP_Cal

Unfortunately ADO and the .NET dynasty that relies on it have often duped
people into thinking that things are much more complex than they need to be.
Just rely on the beauty and simplicity of Access

 

[MikeG0930]

Hi Graham,
The reason I am trying to do this through code is because I need to
fill a flexgrid with many records. I have the code wriiten to fill the
FlexGrid, I just needed a way for the code to know where to get the
data from.

Mike

 

[Graham Mandeno]

Hi Mike

Well, you can do this using your original code. Just change:

Set .ActiveConnection = cnnHIREPay to
Set .ActiveConnection = CurrentProject.Connection

The code you originally posted was assigning the opened recordset to the
form's recordset (Me.Recordset). I was just pointing out that the same can
be achieved by specifying the SQL statement as the RecordSource property of
the form.

Do you really need to use a FlexGrid? I would think that in 99% of cases a
continuous form is more robust and more powerful and requires a fraction of
the amount of code.

 

[MikeG0930]

Thanks Graham! That worked great!
I selected a flexgrid because I'm trying to duplicate a form we use at
work to show attendance for each worker each month. It consist of 32
col and 14 rows. Row(0) is for the header where r0, c0 is for names,
and the rest of the col are for 31 days. There are 13 workers. The
flexgrid seems to work well for what I'm doing, but I'm always open to
new ways of doing things. I don't know if a continuous form will give
me the same effect since I'm not familiar with it.

Mike

 

[Graham Mandeno]

Hi Mike

It sounds like what you want is an "updateable crosstab query". This is not
easy to achieve without a lot of code and probably the MSFlexGrid control is
a good way to go.

 

[MikeG0930]

Hi Graham,
Funny I should hear from you today. Changing the "Set .ActiveConnection
= CurrentProject.Connection" worked great on my home computer, but when
I made the change on the computer at work, I got a run-time error about
a missing object. The code stopped at " .Open strSQL ". This is really
weird. I checked the code and they are identical. I even copied the
database from home and pasted it in a temp folder at work and the copy
worked. I am totally confused! I would use the copy at work except the
data in it is not up to date. Any idea what might be wrong? Thanks!
Mike

 

[MikeG0930]

This is the error message:
Run-time error '-2147217904(80040e10)':
No value given for one or more required parameters.

Mike

 

[MikeG0930]

Hello again!
I found the problem! The query in the database at work had a diferent
name for one of the fields. My booboo! It's working great now!!!!

Thanks for all your help Graham!!