Results 1 to 10 of 10

Thread: VBA SQL Query w/WHERE Clause Condition = GetUserName() From MS ACCESS DB
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Dec 2016
    Posts
    81
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA SQL Query w/WHERE Clause Condition = GetUserName() From MS ACCESS DB

    Hi Community,

    Hoping you can assist with helping me understand what i'm doing wrong with code I found from another post for querying an Microsoft Access Database to retrieve records WHERE a Column value = GetUserName().

    When i run the code I either get an error, OR i don't get an error and it pulls the column name instead of the actual rows / records... Not sure what i'm doing wrong..

    Using this specifc codie i get the following error message:
    "Undefined function 'GetUserName' in expression. Error at line :0, Error Number :-2147217900"

    Code:
    Public Sub ShiftSwap_DBOpen()          Dim cn As Object, rs As Object, rs1 As Object
              Dim intColIndex As Integer
              Dim DBFullName As String
              Dim TargetRange As Range
    
    
              
    DBFullName = "C:\Users\MyName\Documents\ShiftSwapDB.mdb"
    On Error GoTo Whoa
    
    
    Application.ScreenUpdating = False
    
    
    Set TargetRange = Sheets("Sheet2").Range("A5")
    
    
    Set cn = CreateObject("ADODB.Connection")
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DBFullName & ";"
    
    
    
    
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open "SELECT Req_Key, Submitted_Date, Swap_Req_Date, Swap_Req_Shift, Swap_Day_Work, Swap_Req_Time FROM ShiftSwap WHERE Req_Key = GetUserName()", cn, , , adCmdText
    
    
              ' Write the field names
    For intColIndex = 0 To rs.Fields.Count - 1
    TargetRange.Offset(1, intColIndex).Value = rs.Fields(intColIndex).Name
        Next
    
    
              ' Write recordset
    TargetRange.Offset(1, 0).CopyFromRecordset rs
    
    
    
    
    LetsContinue:
    Application.ScreenUpdating = True
    On Error Resume Next
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
    On Error GoTo 0
    Exit Sub
    Whoa:
    MsgBox "Error Description :" & Err.Description & vbCrLf & _
                 "Error at line     :" & Erl & vbCrLf & _
                 "Error Number      :" & Err.Number
    Resume LetsContinue
    End Sub

  2. #2
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    9,154
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VBA SQL Query w/WHERE Clause Condition = GetUserName() From MS ACCESS DB

    I guess the user name function is in Excel

    right now, the SQL has the function name hard-coded in it. instead the SQL (which is just text) needs to be created (concatenated) - so put the value returned from the user name into the SQL

    something like
    Code:
    'replace this line
    rs.Open "SELECT Req_Key, Submitted_Date, Swap_Req_Date, Swap_Req_Shift, Swap_Day_Work, Swap_Req_Time FROM ShiftSwap WHERE Req_Key = GetUserName()", cn, , , adCmdText
    
    
    'by this line
    rs.Open "SELECT Req_Key, Submitted_Date, Swap_Req_Date, Swap_Req_Shift, Swap_Day_Work, Swap_Req_Time FROM ShiftSwap WHERE Req_Key = '" & GetUserName() & "'", cn, , , adCmdText
    PS. I assume everything else is OK
    Last edited by Fazza; Aug 14th, 2019 at 07:59 PM.
    To receive a better answer, put more work into asking the question.


  3. #3
    Board Regular
    Join Date
    Dec 2016
    Posts
    81
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA SQL Query w/WHERE Clause Condition = GetUserName() From MS ACCESS DB

    That worked pefectly!

    Can i ask one other question on this post that is related and part of the same project. I am trying to insert rows into the database but the insert is inserting everyting on the sheet, and will not allow me to seect either a range or starting point rom where I want the row inserts to start, any guidance- the code is posted below:

    Code:
    Public Sub ShiftSwap()
    
    Set rg = Worksheets("ShiftSwap").Range("A3")
    
    
    Set cn = CreateObject("ADODB.Connection")
    dbPath = Application.ActiveWorkbook.Path & "\ShiftSwapDB.mdb"
    dbWb = Application.ActiveWorkbook.FullName
    dbWs = Application.ActiveSheet.Name
    scn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath
    dsh = "[" & Application.ActiveSheet.Name & "$]"
    cn.Open scn
    ssql = "INSERT INTO ShiftSwap ([Req_Key], [Submitted_Date], [Swap_Req_Date], [Swap_Req_Shift], [Swap_Day_Work], [Swap_Req_Time]) "
    ssql = ssql & "SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=" & dbWb & "]." & dsh
    
    
    
    
    
    
    cn.Execute ssql
    
    
    
    
    End Sub

  4. #4
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    9,154
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VBA SQL Query w/WHERE Clause Condition = GetUserName() From MS ACCESS DB

    it is inserting everything because that what the code says to do. near the end of the code, the line is "SELECT *"

    there are many, many options & I don't know what you want - so can't advise without knowing specifics

    simplest might be to have a dedicated extra worksheet just as a temporary store for data to be loaded, and load from there.
    so add an extra worksheet (can be hidden) and populate it with the specific data you want to load. this may be a subset of the full data on the worksheet starting from whatever row you want, or filtered how you want
    To receive a better answer, put more work into asking the question.


  5. #5
    Board Regular
    Join Date
    Dec 2016
    Posts
    81
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA SQL Query w/WHERE Clause Condition = GetUserName() From MS ACCESS DB

    Hi Fazza, this is actually a great suggestion- I had not thought to do this, but immediately know how to make this happen. But im just curious, if i wanted to use the current code or any variation thereof to start with inserting rows at for example "A3" how would i be able to start the row insert from a specific part of the sheet instead of just inserting the entire sheet as its currently doing?

  6. #6
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    9,154
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VBA SQL Query w/WHERE Clause Condition = GetUserName() From MS ACCESS DB

    again, REALLY specific info is needed (on what you want & the set up you have. based on the description given, what follows is really just rough, general advice. What might work best for you might be something different. there are many options, and maybe nuances depending on if you have headers or not, etc)

    you could try just hard coding the address within the SQL. something like "SELECT * FROM [" & YourWorksheet.Name & "$A3:Z99]"
    which with some simple VBA can create the actual address for the range you want (to replace the A3:Z99. The $ sign is required syntax with the worksheet name, it is nothing to do with the range address like in a worksheet formula where it fixes column A. That is, it is NOT $A3:Z99. the $ sign goes with the worksheet name. It is [worksheetname$], then address A3:Z99)

    normally, though, the data to be loaded would be filtered using SQL criteria. That is, not range address
    Last edited by Fazza; Aug 14th, 2019 at 09:12 PM.
    To receive a better answer, put more work into asking the question.


  7. #7
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    9,154
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VBA SQL Query w/WHERE Clause Condition = GetUserName() From MS ACCESS DB

    PS

    If you have a bit of data, IIRC a faster & more robust approach is to load the data into an array and load from there instead of from the worksheet.

    Of course, by using an array you again have many, many options of how to select what gets loaded.

    I'll have a look for an old post on the subject.

    Edit: I think this is it http://<a href="https://www.mrexcel....=10&page=1</a>
    Last edited by Fazza; Aug 14th, 2019 at 09:20 PM.
    To receive a better answer, put more work into asking the question.


  8. #8
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    9,154
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VBA SQL Query w/WHERE Clause Condition = GetUserName() From MS ACCESS DB

    actually, it was a different thread I was thinking of. it includes some timing of different approaches

    http://<a href="https://www.mrexcel....a-dao.html</a>
    To receive a better answer, put more work into asking the question.


  9. #9
    Board Regular
    Join Date
    Dec 2016
    Posts
    81
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA SQL Query w/WHERE Clause Condition = GetUserName() From MS ACCESS DB

    This is great, thank you so much- i'm going to give it a try and see how it works, i have no doubt it will not only solve my problem and perhaps more than what i need which is great!

  10. #10
    Board Regular
    Join Date
    Dec 2016
    Posts
    81
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA SQL Query w/WHERE Clause Condition = GetUserName() From MS ACCESS DB

    Hi Community - is there anyway i can use the following code to connect to a MS Access DB stored on sharepoint instead of my local machine or a shared drive? I ran into some issues with permissions, and found that this DB would need to be stored on sharepoint- any guidance?

    Code:
    Public Sub ShiftSwap_DBOpen() Dim cn As Object, rs As Object, rs1 As Object
    Dim intColIndex As Integer
    Dim DBFullName As String
    Dim TargetRange As Range
    
    
    
    DBFullName = "C:\Users\MyName\Documents\ShiftSwapDB.mdb"
    On Error GoTo Whoa
    
    
    Application.ScreenUpdating = False
    
    
    Set TargetRange = Sheets("Sheet2").Range("A5")
    
    
    Set cn = CreateObject("ADODB.Connection")
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DBFullName & ";"
    
    
    
    
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open "SELECT Req_Key, Submitted_Date, Swap_Req_Date, Swap_Req_Shift, Swap_Day_Work, Swap_Req_Time FROM ShiftSwap WHERE Req_Key = GetUserName()", cn, , , adCmdText
    
    
    ' Write the field names
    For intColIndex = 0 To rs.Fields.Count - 1
    TargetRange.Offset(1, intColIndex).Value = rs.Fields(intColIndex).Name
    Next
    
    
    ' Write recordset
    TargetRange.Offset(1, 0).CopyFromRecordset rs
    
    
    
    
    LetsContinue:
    Application.ScreenUpdating = True
    On Error Resume Next
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
    On Error GoTo 0
    Exit Sub
    Whoa:
    MsgBox "Error Description :" & Err.Description & vbCrLf & _
    "Error at line :" & Erl & vbCrLf & _
    "Error Number :" & Err.Number
    Resume LetsContinue
    End Sub
    Last edited by bemp87; Aug 15th, 2019 at 11:59 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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