Advice on Connection - where should it be? (1 Viewer)

[knn9413]

Ok.. I am still a newbie working on access. So far, what I have done is I have included code to open a connection in every module i.e. add, update and delete. Obviously there is duplication of some code.

The one I am currently working on will probably be used by 5-6 people. My question is in the projects, is it good to open a single connection and if yes, where should it typically be opened?

Or should every single sub have its own connection? I have seen examples wherein they mention currentproject.connection which makes me think they have a single connection.

Any advice would be much appreciated.

Thanks

 

[spikepl]

You need to tell us what this is all about, because I smell a huge ado-rat roaming around here unnecessarily. Access/windows is not like web - things do not get forgotten between postbacks.

 

[Frothingslosh]

You need to tell us what this is all about, because I smell a huge ado-rat roaming around here unnecessarily. Access/windows is not like web - things do not get forgotten between postbacks.

And now I have this mental image of rats dancing a conga line with the front one holding a little "ADO" sign.

 

[knn9413]

Sure.. I have a form which has different buttons wherein a user can update the current record, add part of the record being displayed into a different table or enter new data on the form and add a record to the table. Each of those sub routines has the following code

Set cnn1 = New ADODB.Connection
mydb = "H:\Documents\accesstest\Tasks.accdb"
strCnn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & mydb
cnn1.Open strCnn

So my question was, do I need this code in each of those sub's or can this be placed in a single place to be used by any of those modules? Or do I need to specify this at all.

Hopefully I made that a little clear.

 

[spikepl]

Not really. What is the setup? Normal Access front end for each, backend on the network? Then why do you bring ADO into this at all? DAO is the native Access system, and you do not need to do anything (other than open one conneciton to the backend implicitly, by opening a table in the backend, and keeping it open throuhgout the session alone for efficiency - and that is the end of the open/close connection story).

 

[knn9413]

Not really. What is the setup? Normal Access front end for each, backend on the network? Then why do you bring ADO into this at all? DAO is the native Access system, and you do not need to do anything (other than open one conneciton to the backend implicitly, by opening a table in the backend, and keeping it open throuhgout the session alone for efficiency - and that is the end of the open/close connection story).

Thanks. I think you kind of cleared the doubts that I had. It is a simple time tracking application that the manager wants for his team. I am just helping them out. It is the normal access front end. The application is just going to reside on a shared drive which the team has and can open it up. Like I said, I am a newbie and talking to a few other folks they mentioned ADO is the one to be used. For me personally, I had no preference as such. So other than the implicit connection that already exists, it seems I don't really need to open any new connections.

 

[spikepl]

It is the normal access front end. The application is just going to reside on a shared drive which the team has and can open it up.

To be clear: each user has on own machine his own front end. All front ends link to a backend residing on a shared drive. This is a "normal" Access setup. Yours?

 

[knn9413]

To be clear: each user has on own machine his own front end. All front ends link to a backend residing on a shared drive. This is a "normal" Access setup. Yours?

Everything resides on the same place i.e. the shared drive

I still haven't figured out, how to separate out the front end from the backend.. Can you point me in the right direction? Split the database?

Thanks

 

[spikepl]

Sharing one unsplit db is calling for trouble: it will work right until it stops working.

How to split the db: google it, it's not hard. Or wait until someone less lazy than me explains.

As a lesser evil, you could make one separate front end for each on the shared drive. But, running front end from share puts additional load on the network and slows your application.

 

[jdraw]

Further to the conversation so far, see these for multiuser / performance:
http://www.fmsinc.com/microsoftaccess/databasesplitter/
http://www.fmsinc.com/microsoftaccess/performance/linkeddatabase.html

 

[knn9413]

Sharing one unsplit db is calling for trouble: it will work right until it stops working.

How to split the db: google it, it's not hard. Or wait until someone less lazy than me explains.

As a lesser evil, you could make one separate front end for each on the shared drive. But, running front end from share puts additional load on the network and slows your application.

Thanks. I kind of updated my post right before you posted this. I will figure that out. This is not going to be a heavy duty database. A person will access it once during the whole day and worst case twice. Based on what you told me so far, I believe it should be ok with a single front end. If that doesn't work, I will consider the option of having a separate front end for each...