Doug can you reply regarding advapi32.dll

M

Me

Doug,

Your suggestion to use the sql
select user, ...

to get the sql login name doesn't work in Access, please look at the
advapi32.dll thread and reply.

Thank you,
-Me
 
N

Nick Coe \(UK\)

As Doug said, Terry's answer in that preceding thread is the
key.

I would guess that you haven't got an odbc connection string
set and that is why you're not getting anything returned.

If you go to the Access Help and search for 'pass through'
or just 'pass' you should find an example of how to use the
built in query designer to create a pass through query.
Once you've got that working then you can cut and paste the
resulting SQL. Doing it that way means one less variable
(the SQL syntax) to troubleshoot in your code.

--
Nick Coe (UK)
http://www.alphacos.co.uk/




In Me typed:
 
B

Brendan Reynolds

In addition to the good advice you have received from others, you can do it
by creating a stored procedure in SQL Server and calling the stored
procedure from Access using ADO. I'm not suggesting this method is
necesarily any better than using a pass-through query. I just happened to
have code to do this in a non-Access app and was able to quickly convert it
to work with Access ...

First, create a stored procedure in SQL Server ...

CREATE PROCEDURE [dbo].[SystemUser_Select] AS SELECT SYSTEM_USER

Now we need to call that stored procedure from Access ...

Public Function GetSqlName() As String

Const ConnectionString As String = "Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;Persist Security Info=False;" & _
"Initial Catalog=YourDatabaseHere;Data Source=(local)"

Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset

Set cn = New ADODB.Connection
cn.ConnectionString = ConnectionString
cn.Open
Set cmd = New ADODB.Command
With cmd
Set .ActiveConnection = cn
.CommandText = "SystemUser_Select"
.CommandType = adCmdStoredProc
End With
Set rst = cmd.Execute
GetSqlName = rst.Fields(0).Value
cn.Close

End Function
 
M

Me

Brenden,

Thank you very much for your reply!

I solved my problem.

-Me


Brendan Reynolds said:
In addition to the good advice you have received from others, you can do it
by creating a stored procedure in SQL Server and calling the stored
procedure from Access using ADO. I'm not suggesting this method is
necesarily any better than using a pass-through query. I just happened to
have code to do this in a non-Access app and was able to quickly convert it
to work with Access ...

First, create a stored procedure in SQL Server ...

CREATE PROCEDURE [dbo].[SystemUser_Select] AS SELECT SYSTEM_USER

Now we need to call that stored procedure from Access ...

Public Function GetSqlName() As String

Const ConnectionString As String = "Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;Persist Security Info=False;" & _
"Initial Catalog=YourDatabaseHere;Data Source=(local)"

Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset

Set cn = New ADODB.Connection
cn.ConnectionString = ConnectionString
cn.Open
Set cmd = New ADODB.Command
With cmd
Set .ActiveConnection = cn
.CommandText = "SystemUser_Select"
.CommandType = adCmdStoredProc
End With
Set rst = cmd.Execute
GetSqlName = rst.Fields(0).Value
cn.Close

End Function

--
Brendan Reynolds
Access MVP

Me said:
Doug,

Your suggestion to use the sql
select user, ...

to get the sql login name doesn't work in Access, please look at the
advapi32.dll thread and reply.

Thank you,
-Me
Click to expand...
Click to expand...
 
M

Me

Nick,

Your pointer on how to setup the odbc string helped me resolve the issue.
Thank you very much for your reply!

-Me
 
M

Me

Doug,

Where have you been? I have been struggling to get a reply to my question
for so long until the time your answered it 1st yesterday and then I had hope
to resolve it.
I think I will need your contact info whereever you go in future.

Thanks a millions for helping me out!

-Me
 
Top