Access has too many connections on the backend database

  • Thread starter Thread starter Simon Guertin
  • Start date Start date
S

Simon Guertin

Hi, I am trying to import data from the back-end to the
front-end in a splitted database that has the back-end on
a network file server. The problem is during the transfer,
there is at some point more than 10 000 open connections
on my back-end. The server then resets the connection and
Access has no longer access to the back-end.
Is there a way to limit the number of open connetions? Can
I force the garbage collector to close the open
connections that should be closed?
Is it because I forgot to explicitly close some database
object in some of my procedures?
here is my code that I use to transfer the data. Is there
a better way to do this?


Set fTable = fdatabase.OpenRecordset("LocalContract")
Set fRecordSet = fdatabase.OpenRecordset("Contract")
fRecordSet.MoveFirst

Do Until fTable.EOF
fTable.Delete
fTable.MoveNext
Loop
Do Until fRecordSet.EOF
fTable.AddNew
fTable!contractNo = fRecordSet!contractNo
fTable!contractSystem = fRecordSet!contractSystem
fTable!contractComplex = fRecordSet!contractComplex
fTable.Update
fRecordSet.MoveNext
Loop

fTable.Close
fRecordSet.Close


any help is appreciated.

thanks

Simon
 
Simon Guertin said:
Hi, I am trying to import data from the back-end to the
front-end in a splitted database that has the back-end on
a network file server. The problem is during the transfer,
there is at some point more than 10 000 open connections
on my back-end. The server then resets the connection and
Access has no longer access to the back-end.
Is there a way to limit the number of open connetions? Can
I force the garbage collector to close the open
connections that should be closed?
Is it because I forgot to explicitly close some database
object in some of my procedures?
here is my code that I use to transfer the data. Is there
a better way to do this?


Set fTable = fdatabase.OpenRecordset("LocalContract")
Set fRecordSet = fdatabase.OpenRecordset("Contract")
fRecordSet.MoveFirst

Do Until fTable.EOF
fTable.Delete
fTable.MoveNext
Loop
Do Until fRecordSet.EOF
fTable.AddNew
fTable!contractNo = fRecordSet!contractNo
fTable!contractSystem = fRecordSet!contractSystem
fTable!contractComplex = fRecordSet!contractComplex
fTable.Update
fRecordSet.MoveNext
Loop

fTable.Close
fRecordSet.Close


any help is appreciated.
Click to expand...

I'm not sure whether this will directly affect the number of connections,
but there is *certainly* a much better and more efficient way to do it. You
need only execute a delete query and an append query:

fdatabase.Execute _
"DELETE FROM LocalContract;",
dbFailOnError

fdatabase.Execute _
"INSERT INTO LocalContract " & _
"(contactNo, contractSystem, contractComplex) " & _
"SELECT contactNo, contractSystem, contractComplex " & _
"FROM Contract;",
dbFailOnError
 
Back
Top