March 2, 2005 at 6:42 am
I recently read an article, thought it was here but haven't been able to find it, that described how to define an ADO connection in a background form that was always open and then use that connection for all your ADO operations across your various code behind your forms and controls. Anyone know either where the article is or can explain how to do it? I tried just defining the connection using standard ADO in the Form_Load event for a background form that remains open and then referencing it in another form's (same db) controls. The child form didn't know what I was talking about (connection? What connection?). Not only that, but I thought the article said to close and null the connection in the Form_Unload for the background form but that didn't work either.
Any clues?
Also, all my users are logging into a network. Is there any way to get their domain and/or userid in a module and share it across the app?
TIA
March 2, 2005 at 7:32 am
For the global connection :
Open a module and declare the connection like so
Public MyCn as ADODB.Connection
'run this on the program startup
public sub AutoExec ()
set mycn = new adodb.connection
mycn.open "..."
end sub
'call when the program shuts down
public sub DestroyConnection()
mycn.close
set mycn=nothing
end sub
public function ErrHandler() as ErrResponse
select case Err.number
case 999
ResetConnection
ErrHandler = ErrResponse.Resume
case else
ErrHandler = ErrResponse.ExitSub
end select
(check for connection error here and reset it if needed.... I had to implement something like this in access cause the cn disconnected once in a while because the modules were opened for days at the time)
'log error....
end function
March 2, 2005 at 8:02 am
Perfect! Thanks!
March 3, 2005 at 7:19 am
To retrieve a user's domain name you can use the WNetGetUser API call
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/wnet/wnet/wnetgetuser.asp
March 3, 2005 at 7:23 am
Ummm... OK, any thoughts on how to do that in Access VBA?
March 3, 2005 at 8:00 am
Back to the specification of Shared Connections. Here's my Module:
1 Attribute VB_Name = "Module1"
2 Option Compare Database
3
4 Public db As ADODB.Connection
5
6 'run this on the program startup
7 Public Sub BuildConnection()
8 Set db = New ADODB.Connection
9 db.Open "Provider=SQLOLEDB.1;Persist Security Info=True;Initial Catalog=Agency
ControlSQL;Data Source=ad1hfdsqlthtsc\htsc", "AgencyAppt", "AgencyAppt"
10 End Sub
11
12 'call when the program shuts down
13 Public Sub DestroyConnection()
14 db.Close
15 Set db = Nothing
16 End Sub
In my background form I have in the Form_Open event:
BuildConnection
And in the Form_Unload event:
DestroyConnection
I've checked and there is no "db.close" or Set db=Nothing" anywhere else in the code. When I exit the app I get an Object not found error. If I go into Debug I'm in the middle of Public Sub DestroyConnection() with the current statement pointing at the "db.close" statement. So it's saying the global "db" object no longer exists. If I look at the variable in the debugger it IS set to "nothing".
I tried moving it from Form_Unload to Form_Close with no change in the results.
Any thoughts on where the connection is being destroyed?
ps. Just to clarify - the background form is the Startup form so the Form_Load is the first thing executed. All I'm doing it opening the app and then clicking the Exit button which only does a "DoCmd.Quit" and I get this error...
March 6, 2005 at 9:07 am
To get the user's domain in Access, you use the api calls -- here's what I use, call UserNTDomain() to return the domain. To try it, just paste this into a blank Access module and then in the immediate window type ?UserNTDomain()<enter>:
'---------begin code-----------
Private Declare Function apiWkStationUser Lib "Netapi32" _
Alias "NetWkstaUserGetInfo" _
(ByVal reserved As Long, _
ByVal Level As Long, _
bufptr As Long) _
As Long
Private Declare Sub apiCopyMemory Lib "kernel32" _
Alias "RtlMoveMemory" _
(hpvDest As Any, _
hpvSource As Any, _
ByVal cbCopy As Long)
Private Declare Function apiStrLenFromPtr Lib "kernel32" _
Alias "lstrlenW" _
(ByVal lpString As Long) _
As Long
Private Type mtypUserInfo
UserInfo_username As Long 'name of the user _
currently logged on _
to the workstation.
UserInfo_logon_domain As Long 'the domain name of _
the user account of the _
user currently logged on
UserInfo_oth_domains As Long 'list of other LAN _
Manager domains browsed _
by the workstation.
UserInfo_logon_server As Long 'name of the computer _
that authenticated the _
server
End Type
Public Function UserNTDomain() As String
' Comments :
' Parameters : -
' Returns : String -
' Created :
' Modified :
' --------------------------------------------------------
On Error GoTo ErrHandler
Dim lngRet As Long
Dim lngPtr As Long
Dim tNTInfo As mtypUserInfo
lngRet = apiWkStationUser(0&, 1&, lngPtr)
If lngRet = 0 Then
Call apiCopyMemory(tNTInfo, ByVal lngPtr, LenB(tNTInfo))
If Not lngPtr = 0 Then
With tNTInfo
UserNTDomain = StringFromPtr(.UserInfo_logon_domain)
End With
End If
End If
ExitHere:
Exit Function
ErrHandler:
UserNTDomain = "Local"
Resume ExitHere
End Function
Private Function StringFromPtr(lngPtr As Long) As String
' Comments :
' Parameters : lngPtr -
' Returns : String -
' Created :
' Modified :
' --------------------------------------------------------
On Error GoTo PROC_ERR
Dim lngLen As Long
Dim bytAbytStr() As Byte
lngLen = apiStrLenFromPtr(lngPtr) * 2
If lngLen > 0 Then
ReDim bytAbytStr(0 To lngLen - 1)
Call apiCopyMemory(bytAbytStr(0), ByVal lngPtr, lngLen)
StringFromPtr = bytAbytStr()
End If
Exit Function
PROC_ERR:
MsgBox "The following error occurred: " & Error$
Resume Next
End Function
March 7, 2005 at 1:24 pm
Perfect! Thanks!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply