SQL Server - Microsoft Access Forum - Computer Learning Zone


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

SQL Server

Jon Nelson

14 months ago

I am experimenting with my database on SQL Server. I have a GroupF that uses nested continuous forms as Rick taught in his Association Tech Help Videos. With SQL Server, it takes about 3 minutes to load the various queries in the form. I need certain users to be able to add people to groups within the form so I assume a pass through query wouldn't work for my situation since it is read only. Is there anything I can do to speed up the processing of the form or should I put my tables on a Sharepoint backend instead.

	Sami Shamma @Reply 14 months ago
Yes, I had the same problem and Richard advised me to load the tables from SQL-Server to a temp Tables on either Form Load or application load depending on your situation. And if the Tables are modified, you just reload the Temp tables at that point. It works great. Now it is as fast as local tables.

	Jon Nelson OP @Reply 14 months ago
Thank you Sami. Do you have an example of the vba from your Form Load event? I am not familiar with loading to a temp Tables and then how to reload that information back to SQL-Server. Or can you point me to Richard's response? Your help is greatly appreciated.

	Sami Shamma @Reply 14 months ago
Hi Jon I will get you the code once I am back in the office. Unfortunately, Richard did not make that response public. Perhaps, he will add it to the Quick Queries for tomorrow.

Sami Shamma @Reply

14 months ago

Hi Jon

Place this code in a global Mod. Call it from where it makes more sense to your db. This code Create a Temp Table for States and populate it from the SQL-Server StateT

Details
Private Sub DeleteLocalTempTables()



    On Error Resume Next

    

    ' delete local temp tables

    CurrentDb.Execute "DROP TABLE TempStateT"

    

End Sub



Public Sub CreateLocalTempTables()



    On Error Resume Next



    DeleteLocalTempTables

    

    ' create local temp tables

    CurrentDb.Execute "CREATE TABLE TempStateT(StateID Integer, StateName Text, State Text)"

       

    ' state

    CurrentDb.Execute "INSERT INTO TempStateT (StateID, StateName, State) " & _

        "SELECT StateID, StateName, State FROM StateT;"

            

    Application.RefreshDatabaseWindow





End Sub

	Richard Rost @Reply 14 months ago
Sami Shamma this is good stuff. I have something like this I've had in my notes forever, however it will work with ANY table, and you don't have to specify the field list. I'm going to record it as a lesson for Dev 49.

	Richard Rost @Reply 14 months ago
Done. Lesson 3 of Developer 49.

	Jon Nelson OP @Reply 14 months ago
Thank you both. This is very helpful. Maybe someday, I'll reach Developer 49 but for now this will be help me to fill in the gaps of my knowledge.

	Richard Rost @Reply 14 months ago
It's basically what Sami gave you, just more advanced, and in a format where you can use it with any table without having to specify a list of fields. The concepts are the same, so if you only have to get this working with one table, just do what Sami told you to do.

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

Next Unseen

New Feature: Comment Live View