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...
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.
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.
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.
well there are people Disagreeing on Mr.NolongerSet and he himself admit prefers Bound form (his last comment).
of course it is up to your choice. but looks like you are getting an obstacle already.you can limit the number of records being edited/created by returning 1 record (for editing) and no record for adding:
for editing:
select * from yourTable where (CriteriaHere);
for new record:
select * from yourTable where (1=0);
- There are lots of posts about this, and folks tend to get pretty defensive about their preferred approach to forms,
https://nolongerset.com/use-unbound-forms-to-add-records/
https://www.access-programmers.co.uk/forums/threads/unbound-vs-bound-forms.322411/
Much of what you can do with an unbound form you can do with one that's bound. But I'm sure I can build much more user-friendly interfaces with better error handling with unbound forms. I feel I have far greater control, and if the GUI is designed well for your users, there's less training involved.
That said, there's definitely more work to reap the advantages. However, I've been doing it long enough so that it's not really a big deal to me at this point. - out of curiosity, why are you using .Addnew method of a Recordset.
why not use a Bound Form? RichNewman That is an excellent result and more in line with what one would expect and what most of the Access users worldwide are seeing.
I suspect it is something on the machine. Maybe a bad software configuration, maybe bad hardware (NIC?). If it was my problem, I would decide ahead of time to spend X hours on trying to figure it out. After that, I would rebuild the machine from scratch (at least that takes a known quantity of time, while continuing with the first step does not, hence the timebox). If that did not help either, I would scrap it and buy a new one.
- Thanks again. Yes, I saw the posting you refer to last week and attempted that. That was actually the source for me to get the syntax of how to open a password protected database.
I did try today to set up a simple split database. On the back end the database contained one table with an index autonumber field, and a simple field that holds a date. On my laptop, I created another database that links to that table across the same network, I created a form that has only 2 objects: a textbox for input, formatted as a date, and a button that triggers the code to take the user input and use the ADDNEW method to add it to the table. I did not get any unusual delays for the first or any of the subsequent records I added. Now the trick is to figure out why. I can tell you that although I have a few dozen editing checks to make sure the user is following the rules, in my testing I commented out all of that and just did the ADDNEW based on user input. Got the same issue -- an extremely poor response time for the first record, and a very fast response for subsequent record additions. I will do some tinkering with the code, but off the top of my head it's hard to understand why the identical code runs over and over and only misbehaves the first time it is run in a session.
Thanks for hearing me out -- I very much appreciate the additional sets of eyes. - there is a better explanation here but i think you got it.
https://www.fmsinc.com/microsoftaccess/performance/linkeddatabase.html - Hi arnel_gp,
Sorry for the delayed response. I appreciate your help.
I'm having trouble getting this code to work. In the Replace$ statement, all I did was to swap the name of one linked table in my back end file, for the string "oneLinkedTableHere". I left the rest of that statement intact. So it now reads:
Replace$(myBe.TableDefs("tblActivity").Connect, ";DATABASE=", "")
When I run this, I get run-time error 3055 Not a valid file name. It's pointing to sBackEnd as faulty.
I changed your code just to test to the following:
Public Function OpenBE()
Dim sBackEnd As String
Static myBE As Dao.Database
Set myBE = CurrentDb
'retrieve the backend path and name
sBackEnd = "M:\Proposals Databases\Activity Tracker Data.accdb"
'open the BackEnd
Set myBE = DBEngine.OpenDatabase(sBackEnd, False, False, "MS Access;PWD=Password123")
End Function
In the OpenDatabase statement, I think I have to put in the password as you see above because that BE file is password protected.
Did I capture what you intended? My rewrite runs without error, but it didn't solve the problem. Maybe because of something I misinterpreted about your code?
Your help getting this right will be very much appreciated.
TIA
