UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Keeping A Connection Open To The Back End Database, Office 2007    
 
   
Csorric
post Oct 11 2011, 05:38 PM
Post#1



Posts: 59
Joined: 31-May 11



I am in the process of implementing a database for about 10 users. I plan to split the database and give each user their own local copy. Anyway, I have been testing it out before implementing it and it is a little slow. I found a post online that suggested Keeping a Connection Open to the Back End Database. The post provided the code and it worked, the backend stays open (or locked) until I close the front-end. It also did speed up the database.
My question is: Is it okay to use this with multiple users. If 10 people are using it at the same time wouldn’t that sort of be like using a single file database on a shared network? Has anyone used this or had a problem with it?
Thanks,
Go to the top of the page
 
Jeff B.
post Oct 11 2011, 05:45 PM
Post#2


UtterAccess VIP
Posts: 10,396
Joined: 30-April 10
From: Pacific NorthWet


JOPO (just one person's opinion) ...
I have been using a "hold-open" routine for years on a multi-user system, without issue.
Go to the top of the page
 
GroverParkGeorge
post Oct 11 2011, 07:31 PM
Post#3


UA Admin
Posts: 36,964
Joined: 20-June 02
From: Newcastle, WA


"If 10 people are using it at the same time wouldn’t that sort of be like using a single file database on a shared network?"
That are you driving at? If there is a single back end database, and all of the users are connected to that back end, then, yes, they are using a single backend database file.
The key point is that they are NOT using the same Front End because they are all using the copy of it on their own workstation. They each open a connection to the back end from their own copy, and that's where the "hold open" function comes in. It ensures that each of those connections remain open for the use of that user.
Perhaps I'm missing the import of your question, th
Go to the top of the page
 
Csorric
post Oct 11 2011, 07:47 PM
Post#4



Posts: 59
Joined: 31-May 11



Just want to know if it is a good method more or less. Sorry for the confusion
Go to the top of the page
 
GroverParkGeorge
post Oct 11 2011, 07:55 PM
Post#5


UA Admin
Posts: 36,964
Joined: 20-June 02
From: Newcastle, WA


Sorry, I just was not sure what the point of the question was regarding using a single file.
This method does seem to be effective in many situations.
Go to the top of the page
 
dmhzx
post Oct 12 2011, 02:30 AM
Post#6



Posts: 7,115
Joined: 22-December 10
From: England


If both frint and back end are in Access, you can use linked tables and DAO, then, unless I misunderstand, the question of having to keep a connection open becomesw irelevant.
It least I hope so, because that's the technique I use, and the strin 'connect' onlt appears in the entire project once, and the is to map a ntework connection to the backend if the user doesn't already have one.
Go to the top of the page
 
GroverParkGeorge
post Oct 12 2011, 09:41 AM
Post#7


UA Admin
Posts: 36,964
Joined: 20-June 02
From: Newcastle, WA


Here's an extended discussion from the highly reliable FMS
The reason for keeping the connection open, as I understand it, to prevent Access from closing down and reopening the locking file (.ldb or .accdb), which is an expensive action.
"Microsoft Access Lock Files
When a database is opened, Microsoft Access creates a lock file on disk. You may see these are *.LDB or *.LACCDB files. When the database is closed, the lock file is deleted.
This is not a big deal for a single MS Access database application which would create the lock file when the database is opened and maintain it until the database is closed. But in a linked database design, the lock file on the back-end database may be created and deleted every time a table is opened and closed. When no connections to any tables on the back end database remain open, the lock file is deleted. That takes time."
Read Luke's full article for more.
Go to the top of the page
 
Csorric
post Oct 12 2011, 10:23 AM
Post#8



Posts: 59
Joined: 31-May 11



Great thank you so much
One more quick question. The code below is what I am using for this method. Not sure who to give credit to for the code. But when I password protect the backend, it doesn't work anymore. Anyone know where I can add my password to this code. I am not great with VBA but I am learning.
CODE
Public Sub OpenAllDatabases(pfInit As Boolean)
  ' Open a handle to all databases and keep it open during the entire time the application runs.
  ' Params  : pfInit   TRUE to initialize (call when application starts)
  '                    FALSE to close (call when application ends)
  ' From    : Total Visual SourceBook
  Dim x As Integer
  Dim strName As String
  Dim strMsg As String

  ' Maximum number of back end databases to link
  Const cintMaxDatabases As Integer = 2
  ' List of databases kept in a static array so we can close them later
  Static dbsOpen() As DAO.Database

  If pfInit Then
    ReDim dbsOpen(1 To cintMaxDatabases)
    For x = 1 To cintMaxDatabases
      ' Specify your back end databases
      Select Case x
        Case 1:
          strName = "H:\folder\Backend1.mdb"
        Case 2:
          strName = "H:\folder\Backend2.mdb"
      End Select
      strMsg = ""
      On Error Resume Next
      Set dbsOpen(x) = OpenDatabase(strName)
      If Err.Number > 0 Then
        strMsg = "Trouble opening database: " & strName & vbCrLf & _
                 "Make sure the drive is available." & vbCrLf & _
                 "Error: " & Err.Description & " (" & Err.Number & ")"
      End If
      On Error GoTo 0
      If strMsg <> "" Then
        MsgBox strMsg
        Exit For
      End If
    Next x
  Else
    On Error Resume Next
    For x = 1 To cintMaxDatabases
      dbsOpen(x).Close
    Next x
  End If
End Sub
Go to the top of the page
 
dmhzx
post Oct 13 2011, 03:28 AM
Post#9



Posts: 7,115
Joined: 22-December 10
From: England


CODE
Sub Util2()
#39; trying to connect to an external database with DAO
Dim db1 As DAO.Database
Dim db2 As DAO.Database
Set db1 = CurrentDb
Set db2 = DAO.OpenDatabase("C:\dmhpersonal\Test.accdb", False, False, "MS Access;PWD=pass")
End Sub

This is how I open another Access database. with a password using DAO.
There is no error checking in this bit, but the active code is just one line
Hope it helps
Go to the top of the page
 
Csorric
post Oct 13 2011, 04:10 PM
Post#10



Posts: 59
Joined: 31-May 11



I appreciate your response. I am playing with things a little over my head because I don’t know much about DOA.
Could it be appropriate to put the code you wrote in the on load event of my switchboard form?
If so, how would explicitly close it in the un load event?
Thanks for all your help
Chris
Go to the top of the page
 
theDBguy
post Oct 13 2011, 05:34 PM
Post#11


UA Moderator
Posts: 77,708
Joined: 19-June 07
From: SunnySandyEggo


Pardon me for jumping in...
Looks like it's from Luke Chung at FMS.
The same link that George posted above.
Just my 2 cents... 2cents.gif
Go to the top of the page
 
dmhzx
post Oct 14 2011, 04:36 AM
Post#12



Posts: 7,115
Joined: 22-December 10
From: England


When working with several Access databases, you can either link the tables, or open the other databases and open recordsets from there.
My far the easiest is just linking tables.
Your code is already using DAO. so you can just use the code I sent to open the databases you want. If there was a possibility that they might be different form time to tim, I'd keep the possibilities in a table
So I would use linked tables, but if you want to open databases I'd go for
Dim db as dao.database
Dim db1 as dao.database
Dim db2 as dao.database
set db = currentdb
set db1 = --your backend1 (with the password if needed)

set db2 = --your backend1 (with the password if needed)
And if you don't need them any more you can specifically close them with db1.close
Go to the top of the page
 
Mickjav
post Jan 5 2020, 02:16 PM
Post#13



Posts: 88
Joined: 25-November 18



Just started using the code from FMS and have made a small change as I don't like hard coding links I added this

CODE
strName = Replace(CurrentDb.TableDefs("NAMEOFLINKEDTABLE").Connect, ";DATABASE=", "")


Using this will always point to a datafile as it uses the link info for the linked table, I've only just started using it but works for me.

if you have more than one use a linked table for each backend

--------------------
All open code examples and free projects are only available from: Database Dreams
Go to the top of the page
 
theDBguy
post Jan 5 2020, 02:48 PM
Post#14


UA Moderator
Posts: 77,708
Joined: 19-June 07
From: SunnySandyEggo


Hi Mickjay. Thanks for the update. I think that works only if the BE is not password protected. Is that correct?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Mickjav
post Jan 5 2020, 03:37 PM
Post#15



Posts: 88
Joined: 25-November 18



Have not been able to test it but but as the connection has already been made when the db starts up would it need to supply the password.

--------------------
All open code examples and free projects are only available from: Database Dreams
Go to the top of the page
 
theDBguy
post Jan 5 2020, 04:49 PM
Post#16


UA Moderator
Posts: 77,708
Joined: 19-June 07
From: SunnySandyEggo


QUOTE (Mickjay)
Have not been able to test it but but as the connection has already been made when the db starts up would it need to supply the password.

That would depend on how/where you use strName afterwards.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Mickjav
post Jan 5 2020, 07:58 PM
Post#17



Posts: 88
Joined: 25-November 18



In my case I'm just using it to keep the connection to the data file open which seems to be working as testing on my chart tracker elite 2020 update which has over 2.4mill entries in main table but have noticed it's working smoother.

Mickk

--------------------
All open code examples and free projects are only available from: Database Dreams
Go to the top of the page
 
theDBguy
post Jan 5 2020, 10:49 PM
Post#18


UA Moderator
Posts: 77,708
Joined: 19-June 07
From: SunnySandyEggo


Hi Mick. Thanks for the clarification. It sounds to me like your BE is not password-protected. If that's the case, I would be interested to hear any updates from you when you get a chance to test it out with a password-protected BE. If your BE is already password-protected, and this code work as-is, then I thank you for sharing it. Cheers!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Mickjav
post Jan 6 2020, 03:07 AM
Post#19



Posts: 88
Joined: 25-November 18



No Theirs no password in place as It's a domestic system with data available on the web.
This post has been edited by Mickjav: Jan 6 2020, 03:08 AM

--------------------
All open code examples and free projects are only available from: Database Dreams
Go to the top of the page
 
Mickjav
post Jan 6 2020, 03:48 AM
Post#20



Posts: 88
Joined: 25-November 18



As A thought I had a quick look and if you would need to add a password all you need do is update this line:

CODE
      Set dbsOpen(x) = OpenDatabase(strName)


I just copied and altered this from the post by dmhzx which is good for DAO

CODE
Set dbsOpen(x) = OpenDatabase(strName, False, False, "MS Access;PWD=pass")


NOTE: If you have more than one connection they would all need to have the same password for the above to work.

hope it helps

mick
This post has been edited by Mickjav: Jan 6 2020, 03:56 AM

--------------------
All open code examples and free projects are only available from: Database Dreams
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    29th March 2020 - 04:04 PM