Trigger

  • Thread starter Thread starter Everaldo Ricardo
  • Start date Start date
E

Everaldo Ricardo

I made a table to register update,delete and insert from some tables, in
this table i have a field named user that take the 'system_user' as default
value and is working well, but in a field named eqp that i set 'host_name()'
as default value, it's catching the my station's host name, where i made the
system (detail: it's off)
 
Everaldo Ricardo said:
I made a table to register update,delete and insert from some tables, in
this table i have a field named user that take the 'system_user' as default
value and is working well, but in a field named eqp that i set
'host_name()' as default value, it's catching the my station's host name,
where i made the system (detail: it's off) hey, im new
Click to expand...
 
How do you connect to SQL Server ? AFAIK this value can be defined using the
connection string. In some cases, it is forced into the connection string (I
believe Access does this for attached tables) "hiding" the real value.
Removing the hard coded value from the connection string should solve the
problem...

(A bit surprised ii does this for an ADP file but you can also check in case
it would have the same behavior).
 
The first step would be to use
? CurrentProject.Connection.ConnectionString
in the immediate window to print the connection string and see if you have a
hard coded workstation entry (if I remember it should be WORKSTATION_ID or
WORKSTATION_NAME).
 
that was the answer:

Provider=Microsoft.Access.OLEDB.10.0;Persist Security Info=False;Data
Source=192.168.0.150;User ID=ricardo;Initial Catalog=DbHMSM;Data
Provider=SQLOLEDB.1
 
So it doesn't seem to be hardcoded/

What if you do a quick test that would display the result of SELECT
host_name() and run this on two different workstations ? Do you always the
name of your workstation. The goal is to see if host_name() works fine under
normal circumstances or if you have this weird behavior only when inside the
trigger...

Also are you sure that an explicit value is not proivded when you inserted
those data ? For example if you use a default value such as 'host' and it
still shows up with your workstation name it could be that the default value
is not used at all and that you get the workstation name from somewher
else...

Patrice
 
This is normal (albeit undesirable) behaviour for an ADP. The best way to
work around it is to have code in your ADP that finds out the correct host
name (via one of the GetComputerName API calls, presumably), then close and
re-open CurrentProject.Connection. For example:

'-----This code pulled from a much larger routine, so not tested "as
is"-----

Private Declare GetComputerName Lib "kernel32" Alias "GetComputerNameA"
(ByVal lpszReturnBuffer As String, ByRef lpdwBufferSize As Long) As Long

Public Function apiGetComputerName() As String
Dim strBuffer As String
Dim lngBufSize As Long
Dim lngResult As Long

lngBufSize = 16 'Starting buffer size, changed by GetComputerName()
strBuffer = Space$(lngBufSize)
lngResult = GetComputerName(strBuffer, lngBufSize)
If lngResult <> 0 Then 'You'll probably want to put better error
checking in here.
apiGetComputerName = Left$(strBuffer, lngBufSize) 'Trim trailing
characters
End If
End Function

Dim strConnection As String

strConnection = CurrentProject.Connection.ConnectionString
CurrentProject.CloseConnection
CurrentProject.OpenConnection strConnection & ";Workstation ID=" &
apiGetComputerName()



Rob
 
Just ignore my previous note...I knew there was a reason I'd done it
differently in my own app. :)

Okay, closing and re-opening the connection, I was right about. What I'd
forgotten was that you can't actually SET the Workstation ID, you can only
RESET it (i.e., put it to the current value, not a value of your choosing).
I also goofed on the ConnectionString thing. That won't work; you need to
use BaseConnectionString.

So all you really need to do, then, is:

Dim strConnection As String

strConnection = CurrentProject.BaseConnectionString
CurrentProject.CloseConnection
CurrentProject.OpenConnection strConnection & ";Workstation
ID=DummyValue"

Sorry for the confusion; I really do need to get in the habit of looking at
ALL my code before posting it! (Cutting & pasting was out because the code
was on a different computer not connected to this network.)


Rob
 
Error:

msg: Error while closing connection (6008)

line: CurrentProject.CloseConnection
 
Make sure that this is the first thing you do in your project. I've only
ever tested it as the first thing I do in my Form_Open for the form I
specified in the Startup options, and it works fine there. Try that and see
how it goes.



Rob
 
Oh, and the form is unbound too, btw. Don't know if that would make a
difference or not.



Rob
 
Hmm...not sure what to tell you, then. It works on my end, and it's what MS
recommends to fix the host name. Try creating a new project and see if you
can do it in that context...at least then, we'd know for sure whether it was
a problem with your project or something else that's causing the problem.



Rob
 
using unbound form I could get one step forward: now the access stops at
next line:
 
Error:

msg: 30002 - can't logon. verify if your logon informations are right

line: CurrentProject.OpenConnection strconnection

the whole code is:
Private Sub Form_Open(Cancel As Integer)
Dim strconnection As String
strconnection = CurrentProject.BaseConnectionString
CurrentProject.CloseConnection
CurrentProject.OpenConnection strconnection
End Sub

the value of strconnection is :
 
the value of strconnection is:
PROVIDER=SQLOLEDB.1;PERSIST SECURITY INFO=FALSE;INITIAL CATALOG=DbHMSM;DATA
SOURCE=192.168.0.150;Use Procedure for Prepare=1;Auto
Translate=True;Workstation ID=HMSM-4
 
Back
Top