Unanswered: Passing string variable into VB SQL statement??
First post, I hope you can help me.
I'm trying to write an Access Database talking to a Microsoft SQL server with VBA language, all of which I have no experience with. I'm bumbling through but I'm stuck on trying to pass a variable to a SQL statement to look-up a value in a table.
I'm trying to manage security better than the very un-user friendly Access security, and having grabbed the user's login name from NT I'm attempting to reference that name against a table and save the corresponding security number in a global variable. I'm getting an error "incompatable data types"
The strCurrentUserName is definately a string and holds the currently logged on user, with no \0 or returns on the end...just the username string.
Option Compare Database
Public intSecurityLevel As Integer 'used to store the current user's security level
Public Function getSecurityLevel() As Integer
Dim dbs As Database
Dim rst As Recordset
Set dbs = OpenDatabase("C:\Documents and Settings\jansem\Desktop\WorkloadPlanner.mdb")
Set rst = dbs.OpenRecordset("SELECT access_level FROM dbo_User WHERE User_Name = '" & strCurrentUserName & "'")