CODEWITHSUNDEEP
ms-accessvbscripthta
Jack
Jack

Reputation: 2771

SQL_query WHERE User = 'Windows Username'

I am working on an HTA that is using vbscript to access, read, and update a MS Access database. I would like to be able to select all records where the current Windows username matches the 'User' column in the database.

I have used the below example before to collect the Windows username, but I wasn't having any luck when trying to insert strSender into my query.

Set wshShell = CreateObject( "WScript.Shell" )
strSender = wshShell.ExpandEnvironmentStrings( "%USERNAME%" )

Any suggestions are appreciated.

Thanks.

Added on 02-01-13:

This is a snippet to give you an idea of what I have going.

Dim conn

sub dotheconnection

Set conn = CreateObject("ADODB.Connection")

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=./Database/data.mdb; User Id=; Password="

If conn.errors.count <> 0 Then 

    alert("problem connecting to the database")
else
    getdata

end if
end sub

sub getdata

SQL_query = "SELECT * FROM MyDatabase ORDER BY Team, Status, Employee"
Set rsData = conn.Execute(SQL_query)

strHTML = strHTML & "<table id='data_table' cellspacing='0' cellpadding='4' border='1'><tr><td bgcolor='#000000'><font color='blue'><b>Employee</b></font></td><td bgcolor='#000000'><font color='blue'><b>Team</b></font></td><td bgcolor='#000000'><font color='blue'><b>Status</b></font></td></tr>"
Do Until rsData.EOF = True
strHTML = strHTML & "<tr class='in_out'><td onclick='deleteUser("& rsData("ID") &")' language='vbscript' style='cursor:hand; color:red'>del</td><td>" & rsData("Employee") & "</td><td>" & rsData("Team") & "</td><td><b>" & rsData("Status") & " </b></td> <td onclick='editUser("& rsData("ID") &")'language='vbscript' style='cursor:hand; color:red'>Edit</td></tr>"
rsData.moveNext ' go to next record
Loop

strHTML = strHTML & "</table>"
thediv.innerHTML = strHTML


SQL_query = "SELECT Count(*) AS intTotal FROM MyDatabase"
Set rsData = conn.Execute(SQL_query)
strHTML1 = strHTML1 & "<b>Number of Users: </b>"
strHTML1 = strHTML1 & "" & rsData("intTotal") & ""
Count.innerHTML = strHTML1
end sub

Upvotes: 1

Views: 332

Answers (2)

HansUp
HansUp

Reputation: 97101

I have this parameter query saved as qryFetchUserRecords:

PARAMETERS which_user Text ( 255 );
SELECT yt.*
FROM YourTable AS yt
WHERE yt.User=[which_user];

This VBScript loads an ADO recordset with the result set from that query. It uses the named query as a "method" of the connection object and the parameter value is supplied as an argument to that method.

Dim cn ' As ADODB.Connection
Dim rs ' As ADODB.Recordset
Dim strSender ' As String

Set cn = CreateObject("ADODB.Connection")
cn.Open "your connection string"
Set rs = CreateObject("ADODB.Recordset")
strSender = CreateObject("WScript.Network").UserName
cn.qryFetchUserRecords strSender, rs
' do stuff with recordset here
rs.Close
Set rs = Nothing
Set cn = Nothing

If you don't want to use a saved query, you can use an ADO Command object and add your parameter to it before executing it. But that seems like unneeded extra effort to me.

Beware User is a reserved word, so avoid trouble by enclosing it in square brackets in your SELECT statement, or qualifying it with the table name or alias as I did in that example.

Upvotes: 1

groksrc
groksrc

Reputation: 3025

Looks like you may need the WScript.NetWork object instead. See the example here: http://www.codeproject.com/Articles/1422/Getting-User-Information-Using-WSH-and-VBScript

Upvotes: 0

Related Questions