Access Freezing Up - How To Connect Multiple Users to Online SQL Server Databases Using Microsoft Access and Web ASP


	Back to Access SQL Server Lessons Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Access Freezing Up

Ray White

5 years ago

Hi Richard
Thank you for the "Access SQL Server Online Seminar"
been waiting a long time, Great training.
I do have Winhost service and made new SQL database and connected
to my Access app. Everything went great.
This was a small database I already had built with 6 tables
and one table has 41,000.00 records.
My problem is that when I open any of my forms that i already had built
they will Access will Freeze up, Even a form that only has 300 records.
BUT if i rebuild that same form from scratch identical as it was and then run the form
it works great no freezing even the form with 41K records.
This makes no sense why my original form will freeze but the new identical will work great. What is your idea on this?
I would hate to have to rebuild every form over.

Thanks for your Great Training.

Ray

	Richard Rost @Reply 5 years ago
That's odd. I've never encountered that myself. Make sure you have the Record Source set to either a table or a pass-thru query. Otherwise if you have an SQL statement (especially one with a sort) then remember Access has to pull down ALL of those records to run the query locally. Also make sure you don't have an Order By or Filter specified in the form. Again... same problem.

	Ray White OP @Reply 5 years ago
Ya I see that if you have a complexed application with forms and sub forms on it you will need to re-write a lot of it to run it on a SQL server.

	Richard Rost @Reply 5 years ago
Sometimes you have to remove some of the complexity to get the proper speed. Always keep in mind what data has to go "over the wire," even when you're dealing with LAN-based databases. It's a lot different from designing a database for one user on a desktop PC. Then, sure, make stuff as complex as you want. :)

Ray White OP @Reply

5 years ago

Help :)
I am making a Pass Through query and it runs fine just like this.

SELECT  Count(OrderID)AS OrderCount, Sum(ProductPrice)AS ProductPrice, Sum(TotalVendorFee)AS TotalVendorFee, Sum(TotalDiscount) AS TotalDiscounts,Sum(TotalSale) AS TotalSales, Format(OrderDate,'yyyy') AS OdersDate
FROM tblOrders
WHERE  Format([OrderDate],'yyyy')='2021'
GROUP BY Format([OrderDate],'yyyy')

BUT in the>  WHERE  Format([OrderDate],'yyyy')='2021'
I want to get that Year from a Field that I have on a form.
I tried this but no luck.
WHERE (((Format([OrderDate],"yyyy"))=[Forms]![frmCustomers]![ComboDate]))

	Ray White OP @Reply 5 years ago
Also do you have any Videos just on building SQL pass-through queries.? I see now that SQL pass-through queries are not the same as regular queries. :(

Ray White OP @Reply

5 years ago

I cleaned the code up a little.
This works:

SELECT  Count(OrderID)AS OrderCount,
               Sum(ProductPrice)AS ProductPrice,
               Sum(TotalVendorFee)AS TotalVendorFee,
               Sum(TotalDiscount) AS TotalDiscounts,
               Sum(TotalSale) AS TotalSales,
               Year(OrderDate) AS OderDate
         FROM tblOrders
         WHERE  Year(OrderDate)='2020'
         GROUP BY Year(OrderDate)

BUT in the WHERE  Year(OrderDate)='2020'
I want to get that Year from a Field that I have on a form.
I tried this but no luck.
WHERE  Year(OrderDate)= Forms.frmCustomers.ComboDate

Richard Rost @Reply

5 years ago

A pass-thru query gets sent to the server for evaluation. That SQL Server has no idea what Forms!CustomerF!ComboDate is. You have to send it the value. That's why I took the time to show you how to build a query on the fly using the QueryDefs collection. Watch lesson 4 again. You have to construct the pass-thru query using VBA, then you can use that query on the server.

And yes, I'll be doing more lessons with pass-thru queries once I get to the SQL Server course.

Ray White OP @Reply

5 years ago

Yes I got it working.

Private Sub ComboDate_AfterUpdate()

Dim strSQL As String

  strSQL = "SELECT  Count(OrderID)AS OrderCount, Sum(ProductPrice)AS ProductPrice, " & _
           "Sum(TotalVendorFee)AS TotalVendorFee, Sum(TotalDiscount) AS TotalDiscounts, " & _
           "Sum(TotalSale) AS TotalSales, YEAR(OrderDate) AS OdersDate " & _
           "FROM tblOrders " & _
           "WHERE  YEAR(OrderDate)=" & Me.ComboDate & " " & _
           "GROUP BY YEAR(OrderDate)"
  With CurrentDb.QueryDefs("PTqryCustOrders")
    .SQL = strSQL
    .ReturnsRecords = True
  End With
  Me.Requery

Me.ListSalesByMonth.Requery
Me.ListProductSold.Requery
Me.Refresh

End Sub

	Ray White OP @Reply 5 years ago
Is there something special you have to do in the code to Delete a record in a form that is connected to sql server.? I have a form I can Add new records But I can not delete any records?

	Ray White OP @Reply 5 years ago
I got it. If you have sub forms on a form and you try to delete a record on that sub form and it will not delete. You have to go into the sql server and build the Relations to the form and sub form first then you can delete a record on the sub form. Ya something like that.:)

	Richard Rost @Reply 5 years ago
Good catch. There's always SOMETHING I forget to put in the course. It's a little more complicated than that. It has to do with the BIT value (Y/N) not converting properly. If it's a NULL value in the server table, it locks.

	Ray White OP @Reply 5 years ago
Great Thanks Do a short video on that with a little more detail.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access SQL Server Lessons.

Next Unseen

New Feature: Comment Live View