Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003

    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.

    Code is;

    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 & "'")


    getSecurityLevel = rst("access_level")


    Set dbs = Nothing

    End Function

    Hope you can help


  2. #2
    Join Date
    Dec 2002
    Préverenges, Switzerland
    WHERE User_Name = '" & strCurrentUserName & "'")
    looks ok unless you want to add the semicolon so ..erName & "';")
    getSecurityLevel = rst("access_level")
    should be = rst!access_level


  3. #3
    Join Date
    Sep 2003
    cheers izzy I'll give that a shot

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts