ADODB Connection Problem

[Dan]

Hello. I am using Access 2003 to connect to a backend SQL database. I can
make the connection using the following:

Global Conn as New ADODB.Connection
Conn.ConnectionString = strConn
Conn.Open

The problem I'm haivng now is that I do not know how to tell Access to use
this connection. I want all transactions to run using this conneciton. Any
help is greatly appreciated. Thanks!

 

[Douglas J. Steele]

Keeping a connection open to SQL Server like that is actually a bad idea.
Just open the connection when you actually need it.

 

[Dan]

How? I don't wnat to write a lot of extra code every time I need access to a
table.

 

[RD]

I doesn't have to be "a lot of extra code". Below is a function that I put in a
standard module. In this application I parse data out of Word forms and write
records to an Oracle database. It takes as arguments a SQL string, a connection
string (kept in a system parameter table) and the name of the document I'm
parsing out (for error logging). My commenting says it's only to write a record
but it could handle any action query. A similar function could be written for
returning a recordset. Then I just call it from whatever procedure is doing the
hard work, passing along the arguments.

As always, watch for line wrap.

Option Compare Database
Option Explicit

Function fWriteRecord(sSql As String, sConn As String, sDocName As String) As
String
' This function's only job is to take a SQL statement and a connection
' and write a record to the database

On Error GoTo ErrorHandler
Dim cnn As New ADODB.Connection
Dim vRet As Variant

' Open the connection
cnn.Open sConn
' Execute the action query
cnn.Execute sSql
' Return the function's string value
fWriteRecord = "True"

ExitPoint:
' Close the connection
Set cnn = Nothing
Exit Function

ErrorHandler:
Debug.Print Err.Number & ": " & Err.Description
vRet = fWriteLog(sDocName, Err.Number & ": " & Err.Description, "NA",
"fWriteRecord")
fWriteRecord = "Error: " & Err.Number & " occurred when attempting to write
record."
Resume ExitPoint
End Function

Hope this helps,
RD

 

[Dan]

Thanks. But doesn't this mean that I have to call this string everytime I
modify a field (i.e. in a form)?

 

[Douglas J Steele]

You could write a function that returns a Connection object, and assign that
object as the Active Connection.

Alternatively, you could store the connect string as a public variable (or
have it returned by a function), and use it to create your connection. Yes,
that's 1 extra line of code wherever you need the connection, but it's far
better to do it that way.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks. But doesn't this mean that I have to call this string everytime I
modify a field (i.e. in a form)?

I doesn't have to be "a lot of extra code". Below is a function that I put in a
standard module. In this application I parse data out of Word forms and write
records to an Oracle database. It takes as arguments a SQL string, a connection
string (kept in a system parameter table) and the name of the document I'm
parsing out (for error logging). My commenting says it's only to write a record
but it could handle any action query. A similar function could be written for
returning a recordset. Then I just call it from whatever procedure is doing the
hard work, passing along the arguments.

As always, watch for line wrap.

Option Compare Database
Option Explicit

Function fWriteRecord(sSql As String, sConn As String, sDocName As String) As
String
' This function's only job is to take a SQL statement and a connection
' and write a record to the database

On Error GoTo ErrorHandler
Dim cnn As New ADODB.Connection
Dim vRet As Variant

' Open the connection
cnn.Open sConn
' Execute the action query
cnn.Execute sSql
' Return the function's string value
fWriteRecord = "True"

ExitPoint:
' Close the connection
Set cnn = Nothing
Exit Function

ErrorHandler:
Debug.Print Err.Number & ": " & Err.Description
vRet = fWriteLog(sDocName, Err.Number & ": " & Err.Description, "NA",
"fWriteRecord")
fWriteRecord = "Error: " & Err.Number & " occurred when attempting to write
record."
Resume ExitPoint
End Function

Hope this helps,
RD

 

[Dan]

Thanks. I just went ahead and ported it over to an adp project. This
seemed to be the best way to go.

You could write a function that returns a Connection object, and assign that
object as the Active Connection.

Alternatively, you could store the connect string as a public variable (or
have it returned by a function), and use it to create your connection. Yes,
that's 1 extra line of code wherever you need the connection, but it's far
better to do it that way.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks. But doesn't this mean that I have to call this string everytime I
modify a field (i.e. in a form)?