Forum Discussion
First addnew record slow
I am running an Access split database application using Office 365. I have a form that is not bound to a table -- the user fills in multiple fields on the form and then clicks an "Add Record" button...
For your next test you move the BE to your workstation. Now it is fast, right?
Yes, the problem goes away with local tables.
Tomorrow I'm going to try to just use the Form wizard and create a table bound to the underlying table and see if that makes the problem go away (I'm not fond of bound tables, though -- there are quite a few things I do that work better with an unbound form).
I suppose I could set up a local table and post the records the user is adding to it, and then execute an APPEND query when the user closes the form, or clicks a button called "POST RECORDS," But that's pretty clunky. I can't shake the feeling that there's a simple answer here. To be continued.....
Thanks so much again for your help. I'll keep you posted.
Tomorrow I'm going to try to just use the Form wizard and create a table bound to the underlying table and see if that makes the problem go away (I'm not fond of bound tables, though -- there are quite a few things I do that work better with an unbound form).
I suppose I could set up a local table and post the records the user is adding to it, and then execute an APPEND query when the user closes the form, or clicks a button called "POST RECORDS," But that's pretty clunky. I can't shake the feeling that there's a simple answer here. To be continued.....
Thanks so much again for your help. I'll keep you posted.
- If I had to speculate it is environmental, not a programming issue.
I bet if you created a very small repro, we would not see this problem in our environments. That would be a logical next step. If I am right, then you really have to suspect your infrastructure.
Would you consider upsizing the BE to SQL Server?- It's doable, but this is a complicated application and I'm on a pretty short budget string. Using SQL Server would take time to migrate, and it also opens up some licensing costs.
A small version just to test is a good idea -- I'll try that. Also, I keep getting the nagging feeling that perhaps some Access setting might be involved, though nothing jumps out at me.- "persistent" connection is not about Opening a Table from the BE and helding it open until you quit your app. It is about Opening the Whole BE (not just a single table) and helding it Open until you quit the app.
public function OpenBE()
dim sBackEnd As String
Static myBE As Dao.database
Set myBE = currentdb
'retrieve the backend path and name
sBackEnd = Replace$(myBe.TableDefs("oneLinkedTableHere").Connect, ";DATABASE=", "")
'open the BackEnd
Set myBE = DbEngine.OpenDatabase(sBackEnd, False, False)
end function
call the above from autoexec macro.
don't worry when there is no code to close this database.
it will get closed automatically when you close your app.
