Sql etiquette or protocol?

[Smee]

I have a database and a selection of modules which all want to access the database.

What is the correct or best method of doing so?

Should I ;

a) Define the database and cursors and initialise in the Main Module

Dim SQL As SQL
Dim Cursor1 As Cursor

Sub Activity_Create(FirstTime As Boolean)

If FirstTime=True Then
       SQL.Initialize(File.DirInternal, "database.db", True)
end if

then in each activity refer to the database initialised in Main

Main.Cursor1 = Main.SQL.ExecQuery("SELECT Customer FROM Customers")

OR

b)
Initialise the database in each activity by the following process

close the database when calling another acttivity, re-open it in the next activity, manipulate data / user requests etc

close the database

exit the activity
go back to Main and initialise the database again for use there again

All comments suggestions etc are welcome

Joe

 

[Erel]

I think that the best option is to create a code module that handles all access to the database.

 

[devlei]

I think that the best option is to create a code module that handles all access to the database.

Would that be similar to the DBUtils module?
If so, would that not slow things down when one has a series of database calls to make?

 

[Erel]

DBUtils is also a code module. However DBUtils purpose is a bit different. It helps with common tasks.

Moving the methods to a code module doesn't have any effect on performance.

 

[Smee]

Thanks for the suggestion, Looks like a code re-write coming up

 

[devlei]

Joe, it looks like I am sitting with a similar situation to you.

I access my database from every Activity Module, and, unless I am missing something obvious, don't see what advantage it would be to have a seperate code module to handle accessing the database.

I started off by using your option b (initialising with every module), but found it confusing when one module is called from within another one. Last week I changed over to your option a (initialising in the Main Module), but haven't tested it to see the results.

Did you experience problems using option a, that you feel a necessary to change?

Dave

 

[JaunLukePicard]

The benifits of a Code Module

If I am not mistaken, the benifits to a Code Module are that you only write the routines once, then call them as needed from throughout the application. Think of it as a DAL (Data Access Layer).

:sign0163:

 

[Smee]

Did you experience problems using option a, that you feel a necessary to change?

hI dAVE,

No i have not had any problems. It was just that i am unsure re memory usage etc. I have actually used a combination of both methods to see if it causes a crash or something but everthing seems to work ok.

In reality it seems that there is no difference to using code like Main.cursor1 or putting it in a module called db and saying db.cursor1.

Although it is probably more memory efficient to dim a cursor in each sub routine which is what i will eventually end up doing i think.

One thing about coding and learning at the same time is it tends to create messy code at times.

At least in my case anyway

Cheers