Results 1 to 15 of 15
  1. #1
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1

    Question Unanswered: Can You Pass SQL to Your SQL DB Like In Excel VBA?

    I am wondering if it is possible to bypass Access's limited sql engine and pass straight to sql server and get results, in the same way that you can in Excel using VBA. I'm using Access 2010, but it must be backwards compatible with 2007.

    For example, here is some code from excel VBA:

    Code:
    Dim cmdText As String
    cmdText = "SELECT * from myTable"
    
    With ActiveSheet.QueryTables.Add( _
    Connection:="OLEDB;DSN=datawarehouse;Trusted_Connection=Yes;APP=2007 Microsoft Office system;WSID=BD-MKTG-C24;" & _
    "DATABASE=PRODDW", Destination:=Range("A4"))
    
    
    .CommandText = cmdText
    .Name = "List"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = True
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .Refresh BackgroundQuery:=True
    
    End With
    So this will run the query against our sql server, and stick the results in the worksheet. Is something like this possible in Access and pop up a dataset? Why? There are several SQL Server functions that I'd like to utilize that arent supported in Access 2010.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Look at pass through queries
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You could use:
    Code:
    Dim qdf As DAO.QueryDef
    Dim rst As DAO.Recordset
    Dim var AS Variant
    Dim cmdText As String
    Dim strConnection As String
    
    cmdText = "SELECT * from myTable"
    strConnection:="ODBC;DRIVER={SQL Server};SERVER=ServerName;DATABASE=DatabaseName;Trusted_Connection=Yes;"
    '
    ' Create and initialize a nameless query.
    '
    Set qdf = CurrentDb.CreateQueryDef("")
    qdf.Connect = strConnection 
    qdf.SQL = cmdText 
    '
    ' Get the array of values returned by the query (max 9999 rows: change the value if necessary).
    '
    Set rst = qdf.OpenRecordset
    var = rst.GetRows(9999)
    rst.Close
    qdf.Close
    Set rst = Nothing
    Set qdf = Nothing
    You can also create a "permanent" query (i.e. it will appear in the database objects):
    Code:
    Dim qdf As DAO.QueryDef
    Dim strQueryName As String
    Dim cmdText As String
    Dim strConnection As String
    
    cmdText = "SELECT * from myTable"
    strConnection:="ODBC;DRIVER={SQL Server};SERVER=ServerName;DATABASE=DatabaseName;Trusted_Connection=Yes;"
    strQueryName = "SomeQueryName"
    '
    ' Delete strQueryName if it already exists.
    '
    If DCount("*", "MSysObjects", "name='" & strQueryName & "'") > 0 then
        DoCmd.DeleteObject acQuery, strQueryName 
    End If
    '
    ' Create and initialize a named query.
    '
    Set qdf = CurrentDb.CreateQueryDef(strQueryName)
    qdf.Connect = strConnection 
    qdf.SQL = cmdText 
    Set qdf = Nothing
    Have a nice day!

  4. #4
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    thanks for the response Sinndho. I'll give it a shot.

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  6. #6
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Ok, not working for some reason. I am not getting an error, just nothing is happening. No recordset pops up.

    i changed the query and connection string as needed.

    Code:
    Sub querytest()
        Dim qdf As DAO.QueryDef
        Dim rst As DAO.Recordset
        Dim var As Variant
        Dim cmdText As String
        Dim strConnection As String
        
        cmdText = "SELECT top 100 * from vwfsalesview"
        strConnection = "ODBC;DRIVER={SQL Server};SERVER=bd-dwprod;DATABASE=proddw;Trusted_Connection=Yes;"
        '
        ' Create and initialize a nameless query.
        '
        Set qdf = CurrentDb.CreateQueryDef("")
        qdf.Connect = strConnection
        qdf.SQL = cmdText
        '
        ' Get the array of values returned by the query (max 9999 rows: change the value if necessary).
        '
        Set rst = qdf.OpenRecordset
        var = rst.GetRows(9999)
        rst.Close
        qdf.Close
        Set rst = Nothing
        Set qdf = Nothing
        
    End Sub

    EDIT: The second set of code does work though. It creates the query in my objects and I can run it fine.
    Last edited by clawlan; 02-19-14 at 17:00.

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    When you write that nothing happens, did you check the contents of the variable var after the line:
    Code:
     var = rst.GetRows(9999)
    Have a nice day!

  8. #8
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Quote Originally Posted by Sinndho View Post
    When you write that nothing happens, did you check the contents of the variable var after the line:
    Code:
     var = rst.GetRows(9999)
    Perhaps i am not doing this right, but when I try to display the results using Debug.Print var, I get a "Type Mismatch" error.

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    That's because var is an array, not a scalar function. Select the name of the variable (var) in the code window then select "Add watch..." from the Debug menu.
    Have a nice day!

  10. #10
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Quote Originally Posted by Sinndho View Post
    That's because var is an array, not a scalar function. Select the name of the variable (var) in the code window then select "Add watch..." from the Debug menu.
    Thanks for your patience. The result from that is: Value: <out of context>

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If you add awatch in a procedure (sub or function) you can only examine the related variable inside that procedure, except if you edit the watch and extend its range to all procedures. This is part of the debug skills you need to acquire when working with VBA, be it in Access or in Excel.
    Have a nice day!

  12. #12
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Quote Originally Posted by Sinndho View Post
    If you add awatch in a procedure (sub or function) you can only examine the related variable inside that procedure, except if you edit the watch and extend its range to all procedures. This is part of the debug skills you need to acquire when working with VBA, be it in Access or in Excel.
    It should be set up correctly. Here is a screenshot.
    Attached Thumbnails Attached Thumbnails Capture.PNG  

  13. #13
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Did you set a breakpoint at the end of the procedure querytest and examine the variable when the code stops? It cannot be out of context there !
    Have a nice day!

  14. #14
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Quote Originally Posted by Sinndho View Post
    Did you set a breakpoint at the end of the procedure querytest and examine the variable when the code stops? It cannot be out of context there !
    OK! I have learned a ton about VBA debugging, thank you. So i see that there are values in the array as expected. But they are just not being ouput. i am researching the DAO syntax. i think I need to add something like OpenRecordset or similar.

  15. #15
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It's all about what you want to do with the data extracted from the server. If it's for displaying the data set in a grid (as in Excel), use the "permanent" query syntax then create a form in continuous or datasheet view and assign the name of the query to its RecordSource property. Once the query is created, you can use the Access wizards to create the form.
    Have a nice day!

Posting Permissions

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