Passing Connection Info

[Nik]

Hey Guys n Gals,
I'm attempting to develop my first Access VBA application. I have managed to
connect to the database via coding when the form opens. My question is when
I'm ready to submit info via forms, do I for each form have to put in the
connection info. If I have to can someone guide me on the simplest way to
achieve this. - I'm using Access 2003
thanks
Nik

 

[Scott McDaniel]

Hey Guys n Gals,
I'm attempting to develop my first Access VBA application. I have managed to
connect to the database via coding when the form opens. My question is when
I'm ready to submit info via forms, do I for each form have to put in the
connection info. If I have to can someone guide me on the simplest way to
achieve this. - I'm using Access 2003

There are two schools of thought on this - open a global connection when the app is launched, or open/close connections
as needed. If your network can stand the bandwidth, then the global connection is easiest, but many purists prefer to
open/close connections only as needed. If you're hitting a server that is extremely busy, the second method is preferred
since doing so would reduce the load on the server.

In most cases, I think the global connection object is the easiest way to deal with this; when your app is launched,
call a sub or function that opens your connection, then make sure to close it when the app exits:

[General Declarations]

Public MyConn As ADODB.Connection

Function OpenDatabaseConnection() As Boolean

Set MyConn = New ADODB.Connection
MyConn.Open "YourConnectString"

OpenDatabaseConnection = True

End Sub

Now in the Open or Load event of your startup form:

Sub MyForm_Open()

If Not OpenDatabaseConnection Then
MsgBox "Unable to connect to database. This application will close", vbOkOnly
Application.Quit
End If

End Sub

thanks
Nik

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

 

[bob]

You need to tell us more: is your "database" an Access (backend) or are you
using something such as SQL server?