Access Crash on Form Open

[PJS]

Hi,
We're in our first week of running an SQL Server database
that was made from an upsized Access (XP) back end. We
are not, however, using an Access ADP front end. There
are other changes needed before we convert to an ADP. I'm
having a lot of problems with subforms on many of our
existing forms and am not sure why. It seems that the new
system is not happy when I issue a Requery command for a
subform, as in: subSomeSubForm.Requery . It either takes
a long time or hangs permanently. Is SQL Server not happy
with this kind of operation?

Here's a specific one. We have a switchboard for our
shipping department that has an MS Calendar control on
it. Users click a date to see what deliveries are due for
the selected date. On open, the date defaults to the
current date. The OnOpen event of the form populates the
subform recordset. This code, which works great w/ an
Access back end, hangs at the point indicated since
switching to SQL Server back end. (Note: There are other
functions on the form that provide users with different
options for filling this subform, therefore, I didn't bind
the subform to a specific query, but used code intstead.)

Private Sub Form_Open(Cancel As Integer)
'Set Calendar control to today's date and populate subform.

On Error GoTo Err_FormOpen

Dim strSQL As String
Dim datDate As Date

datDate = DateValue(Now())
With Me
!ocxCalendar.SetFocus
!ocxCalendar.Value = datDate
!txtSelDate = datDate
strSQL = "SELECT * FROM qryPOIDueByVend WHERE
due_date = #" & datDate & "#"
!subDueToday.SourceObject = "sfrmPOIDueByProj"
!subDueToday.Form.RecordSource = strSQL
'*** CRASH OCCURS ON LINE ABOVE ***
End With

Exit_FormOpen:
Exit Sub
Err_FormOpen:
MsgBox Err.description & " -- " & Err.Number
Resume Exit_FormOpen
End Sub

There's no error message, it just hangs and Access has to
be closed thru Task Manager. The query in the SQL string
is a standard Access query, not a SQL pass-thru, view, or
stored procedure. I tried wrapping the date value in
single quotes instead of #, but NG. Also, I've tried to
include any reference to a SQL Server or RDO library in
the References settings in VBA.

Thanks for reading this saga. Any help would be great. I
have 2 companies on opposite coasts that are waiting for
me to fix things. The horror.

Regards,
PJS

 

[SFAxess]

It sounds like it is something with the SQL Server, which
is why your aren't getting an error message. I built a
test situation which mirrored yours, and I didn't have
any problems. (I assume you are using ODBC to link to
your SQL Server tables).

You might want to check to be sure that your referenced
queries such as, "qryPOIDueByVend", are working OK.
If you haven't tried making it a pass-through query, you
may wanna try that.
Another thing is the permissions on the tables you are
accessing in SQL Server. Make sure that you have proper
permissions.
I am not sure about the Requery issue, it sounds like it
is a bi-product of the situation which causes your Open
proc. to crash.
Sorry I can't be of more help.

 

[PJS]

SF:

Thanks for replying! Strangely, some of the code crash
problems we're having do not occur, or certainly less
frequently, on my developer machine. (Currently, that's my
laptop running a SQL Evaluation version. I had MSDE
running on it but it got corrupted somehow so I
uninstalled it an put in the Eval version just to get thru
the 2 weeks I'll be on the road getting a new company
online with our database.)

Are there any other "required" or "recommended" libraries
that must/should be referenced for connecting an Access
MDB front end to SQL Server? (We're not using ADO in this
system yet. It's all DAO. We haven't had time to convert
the system.)

Thanks again!
PJS

 

[SFAxess]

As long as your ODBC connection is set up properly you
should be fine. You might try reconnecting the linked
tables in case the ODBC drivers were corrupted(if you
aren't using ODBC and are just referencing the server
through code or in a query, you might try that). You
might also try posting your problem on the SQL Server
newsgroups to find some more insight.
Best of luck.