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.
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 WhiteOP
@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.
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 WhiteOP
@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 WhiteOP
@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 WhiteOP
@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
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 WhiteOP
@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
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 WhiteOP
@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.:)
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 WhiteOP
@Reply 5 years ago
Great Thanks
Do a short video on that with a little more detail.
Sorry, only students may add comments.
Click here for more
information on how you can set up an account.
If you are a Visitor, go ahead and post your reply as a
new comment, and we'll move it here for you
once it's approved. Be sure to use the same name and email address.
This thread is now CLOSED. If you wish to comment, start a NEW discussion in
Access SQL Server Lessons.
