none
Search Database using Excel Userform

    Question

  • Hi,

     

    As a bit of background i have an excel userform which communicates with a database, i know this is not ideal but most of the users of this application do not have access to microsoft access. I am trying to use my Excel userform to search an Access database and return the values of the data set. E.g

    Table Name : Employees

    Field 1: EmployeeID to textboxEmpID

    Field 2: EmployeeName to textboxEmpName

    etc, i currently have code which counts the records which i have posted below. My question is.. is this possible, i am a novice and cannot current get my head around this. Any help would be greatly appreicated

        'Set Searchstring for Breach
        Set rs = New ADODB.Recordset
        searchstring = "SELECT * FROM SwitchingFeedbackLog WHERE [UserFeedback] = " & Pers & " AND [Breach] = ""Yes"" AND [DateKeyed] >= " & DateFrom & " And [DateKeyed] <= " & DateTo
        rs.Open searchstring, cn, adOpenStatic
        totalBreach = rs.RecordCount
        tbNoOfBreaches.Value = totalBreach
        Set rs = Nothing

     

     

    • Moved by Mark Liu-lxfModerator Thursday, January 26, 2012 8:17 AM VBA issue (From:Visual Basic General)
    Monday, January 23, 2012 9:36 AM

Answers

  • Hi Cken21,

    This queue is about Visual Basic in Visual Studio. I’m afraid that your topic about VBA is unsuitable here. For better support, I will move this thread to Visual Basic for Applications (VBA) forum.

    I’m not familiar with VBA. I wondered why not use the cells in the Excel instead of using a textbox control to store the data. In this way, you can edit the data easily. Here is a sample about load a table in Access2007 to Excel (VBA code), hope it helps.

     

    Sub sampl2()
        Dim AlexCon As ADODB.Connection
        Set AlexCon = New ADODB.Connection
        Dim AlexRS As ADODB.Recordset
        Set AlexRS = New ADODB.Recordset
         AlexCon.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\work\Bobble1.accdb;Persist Security Info=False;"
        With AlexRS
            .ActiveConnection = AlexCon
            .Source = "SELECT * FROM dolls"
            .Open
        End With
        Range("A1").CopyFromRecordset AlexRS
        AlexRS.Close
        Set myRecSet = Nothing
        AlexCon.Close
        Set AlexCon = Nothing
    End Sub
    
    
    Sorry for any inconvenience and have a nice day.

     


    Mark Liu-lxf [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, January 26, 2012 8:16 AM

All replies

  • As a quick after thought, i meant to mention that i also need to edit the record once it has been returned
    Monday, January 23, 2012 9:51 AM
  • So what is your problem, 

    Getting the data from Excel

    Changing the Data

    Updating the data in the Access database.

    That are 3 different problems, give them in this forum in the sequence you handle this.

    Now you get no answer or you get an endless bunch of answers, but the chance that one of that is complete is probably zero.

     


    Success
    Cor
    Monday, January 23, 2012 10:52 AM
  • Thanks for your reply.

     

    Updating the data i can work out myself, the main problem i am having is pulling the data from the database and returning it to the textboxes on my excel userform

    Monday, January 23, 2012 12:34 PM
  • Hi Cken21,

    Welcome to the MSDN forum.

    First of all, I’d like to check some information about the code you showed in this thread. As far as I know, this code is a macro written with VB6. So please make sure witch one you choose to deal with this issue, VBA (macro) or VB.Net?

    As for your VB6 code, you need to make sure the SQL Query is correct firstly. You can use it in the Access database and check whether it can get the data you want, and then to deal with the VBA code.

    If you have any additional questions, please feel free to let me know.


    Mark Liu-lxf [MSFT]
    MSDN Community Support | Feedback to us
    Wednesday, January 25, 2012 3:24 AM
  • Hi

     

    Thanks for the reply. Yes the project runs off VB6 as it is within an excel document.

    I have found out the code for returning the values from the Database.

        Search = tbSearch.Value
    
    
        Set rs = New ADODB.Recordset
        searchstring = "SELECT ProductNum, Brand, FeedbackFrom FROM SwitchingFeedbackLog WHERE [ProductNum] = '" & Search & "'"
        rs.Open searchstring, cn, adOpenStatic
        tb1.Text = rs.Fields("ProductNum") 
        tb2.Text = rs.Fields("Brand") 
        tb3.Text = rs.Fields("FeedbackFrom") 
        Set rs = Nothing

    Could i ask you to help me with being able to edit this data once it has been returned?

    Wednesday, January 25, 2012 8:31 AM
  • Hi Cken21,

    This queue is about Visual Basic in Visual Studio. I’m afraid that your topic about VBA is unsuitable here. For better support, I will move this thread to Visual Basic for Applications (VBA) forum.

    I’m not familiar with VBA. I wondered why not use the cells in the Excel instead of using a textbox control to store the data. In this way, you can edit the data easily. Here is a sample about load a table in Access2007 to Excel (VBA code), hope it helps.

     

    Sub sampl2()
        Dim AlexCon As ADODB.Connection
        Set AlexCon = New ADODB.Connection
        Dim AlexRS As ADODB.Recordset
        Set AlexRS = New ADODB.Recordset
         AlexCon.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\work\Bobble1.accdb;Persist Security Info=False;"
        With AlexRS
            .ActiveConnection = AlexCon
            .Source = "SELECT * FROM dolls"
            .Open
        End With
        Range("A1").CopyFromRecordset AlexRS
        AlexRS.Close
        Set myRecSet = Nothing
        AlexCon.Close
        Set AlexCon = Nothing
    End Sub
    
    
    Sorry for any inconvenience and have a nice day.

     


    Mark Liu-lxf [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, January 26, 2012 8:16 AM
  • What do you mean being able to edit this data?

    When the data is returned you are able to edit it or not?

    Please explain. Thanks.


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Wednesday, February 15, 2012 1:20 AM