Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Connecting a front end to a back end (VBA Excel 2003)

    Hi,

    Im not sure if this can be done? Ive got a text box on a form in which a postcode is entered, with a button next to it that states 'Enter'. I need to be able to take whatever data is entered into the text box, at the press of the button, run a macro that inserts the data into a T-SQL script line, which in turn then interrogates an Access database to return data to a worksheet that is relevant to the postcode.

    Im ok with the connecting to and the interrogation of Access, but just dont know how to get the entered data and button to insert the postcode into the T-SQL script line and run the macro interrogating Access?

    Any pointers will be of great help.

    Cheers.
    Lee

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Connecting a front end to a back end (VBA Excel 2003)

    I thought T-SQL was for SQL Server. As far as I know, Access doesn't "understand" T-SQL.

  3. #3
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Connecting a front end to a back end (VBA Excel 2003)

    Sorry, my mistake.

    I mean a script line in vba that uses the sql commands to pull data from Access, ie ("SELECT [..column name..], FROM qry...... WHERE ..............etc ").

    Regards.
    Lee

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Connecting a front end to a back end (VBA Excel 2003)

    Something like
    <code>
    Dim strSQL As String
    strSQL = "SELECT [Field1], [Field2] FROM [qrySomething] WHERE [Field3] = " & Chr(39) & Me.txtPostcode & Chr(39)
    </code>
    Chr(39) is the single quote character <code>'</code>, so if the text box txtPostcode on the form contains <code>SW1A 1AA</code>, the variable strSQL will contain
    <code>
    SELECT [Field1], [Field2] FROM [qrySomething] WHERE [Field3] = 'SW1A 1AA'
    </code>
    Single or double quotes are needed around text values in an SQL statement.

  5. #5
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Connecting a front end to a back end (VBA Excel 2003)

    Many thanks Hans.

    I just need to clarify Me.txtPostcode as when I Debug I get the error "Invalid Use of Me keyword", I suspect that Im meant to replace the 'Me' with something else?? Ive tried a few things but nothing works...Sorry if it is obvious but this is a first time for me connecting a form to a script in this way.

    Cheers.
    Lee

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Connecting a front end to a back end (VBA Excel 2003)

    You wrote "Ive got a text box on a form (...) with a button next to it". I assumed that you meant a userform created in the Visual Basic Editor, and that your code would be in the module belonging to the userform. In this module, "Me" refers to the userform.

    If your code is elsewhere, replace Me.txtPostCode with FormName.TextboxName where FormName is the name of the userform and TextboxName is the name of the text box.

    If you don't have a userform, please explain what you meant by "Ive got a text box on a form".

  7. #7
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Connecting a front end to a back end (VBA Excel 2003)

    Thanks Hans, I hadnt realised that the code, ideally, needed to be in the module belonging to the userform, that now debugs without error.

    Just one more thing, have I included StrSql correctly in the following line?

    Set rst = dbs.OpenRecordset(strSQL = "SELECT [Court Name],[Court Postcode] FROM tblCourtsAgents WHERE [Court Postcode] = " & Chr(39) & frmCPC.TxtPostCode & Chr(39))

    Many Thanks.
    Lee

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Connecting a front end to a back end (VBA Excel 2003)

    No, either use

    strSQL = "SELECT [Court Name],[Court Postcode] FROM tblCourtsAgents WHERE [Court Postcode] = " & Chr(39) & frmCPC.TxtPostCode & Chr(39)
    Set rst = dbs.OpenRecordset(strSQL)

    or omit the variable entirely:

    Set rst = dbs.OpenRecordset("SELECT [Court Name],[Court Postcode] FROM tblCourtsAgents WHERE [Court Postcode] = " & Chr(39) & frmCPC.TxtPostCode & Chr(39))

    Note: it isn't necessary to put the code in the module belonging to the userform, it was simply an (unjustified) assumption on my side.

  9. #9
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Connecting a front end to a back end (VBA Excel 2003)

    Thanks for the guidance Hans.

    The code I used, as kindly amended by you, detailed below, produces the following error when running; "Syntax error in FROM clause"

    Set rst = dbs.OpenRecordset("SELECT [Court Name],[Court Postcode] FROM tblCourtsAgents WHERE [Court Postcode] = " & Chr(39) & frmCPC.TxtPostCode & Chr(39))

    Is there anything I need to adjust further?

    Regards,
    Lee

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Connecting a front end to a back end (VBA Excel 2003)

    The only explanation I can think of is that tblCourtsAgents is not the correct name of the table. Make sure that the spelling is exactly right.

  11. #11
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts

    Re: Connecting a front end to a back end (VBA Exce

    Are you sure the WHERE clause doesn't need to be enclosed in (brackets). Build the query in Access using design view and then switch to SQL view to ensure you get the string correct before moving to Excel.
    You might also need to check the field type for TxtPostCode - are you positive that it is a string value?
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  12. #12
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Connecting a front end to a back end (VBA Excel 2003)

    Ok, many thanks for your time and help Hans.

    Regards.
    Lee

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Connecting a front end to a back end (VBA Exce

    WHERE clauses in SQL do not need to be enclosed in parentheses or otherwise.

    Post codes in the UK contain both letters and digits (for example "NE71 6JW", so I assumed it had to be a text field.

  14. #14
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Connecting a front end to a back end (VBA Exce

    Thanks for the tip Andrew, but Im quite new to this and dont really understand at what point in the script line that I may have to put bracket around the WHERE clause. Is it at the beginning of the word Where, are just after ? And where would the closing braket go??

    Also Im not sure how to check the field type on TxtPostCode?

    Any guidance will be appreciated.

    Regards.
    Lee

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Connecting a front end to a back end (VBA Exce

    As I mentioned in my reply to Andrew, there is no need to put brackets around the WHERE part, so you don't have to worry about that.

    You could look at the design of the table in Access to check that the postcode field is a text field As it should, since UK postcodes contain both letters and digits).

Page 1 of 2 12 LastLast

Posting Permissions

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