Results 1 to 14 of 14
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Connect to SQL Server via VBA (2003 SP 3)

    I should know how to do this, but have struggled for hours without success. I have stored procedure on SQL Server 2005 that updates six fields, It runs fine in SQL Server query window. However, I need to call the stored procedure from VBA.

    The following code snippet is where I am getting an error:
    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDefs
    'Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim strUID As String
    Dim strPWD As String
    Dim strConnect As String
    Dim strDATABASE As String

    Set dbs = CurrentDb
    Set qdf = dbs.QueryDefs
    'Set qdf = dbs.QueryDef

    strSQL = "EXEC SDUpdate @SID = " & Me.SID & _
    ", @SName = " & Me.txtName & _
    ", @SAddress1 = " & Me.txtAddress1 & _
    ", @SClosedDate = " & Me.txtClosed & _
    ", @SClosed = " & Me.chkClosed & _
    ", @SInactive = " & Me.chkInactive & _
    ", @S_Pkey = " & Me.txtPkey & ";"


    strUID = "KWVH"
    strPWD = "kwvh"
    strDATABASE = "KWVH_SQL"

    strConnect = "ODBC;DRIVER={SQL Server}" _
    & ";SERVER = LAPTOP " _
    & ";DATABASE = " & strDATABASE _
    & ";UID =" & strUID _
    & ";PWD =" & strPWD & ";"

    qdf.Connect = strConnect


    qdf.SQL = strSQL
    qdf.ReturnsRecords = False
    qdf.Execute


    Set qdf = Nothing
    Set dbs = Nothing

    End Sub

    I get the following error on the "qdf.Connect = strConnect" line
    Method or data member not found
    The ".Connect" is highlighted each time.

    I am also not 100% certain I have the Dim qdf correct. Should it be 'QueryDef' or 'Querydefs'? Any ideas are greatly appreciated.

    Ken

    Even if you are on the right track, you'll get run over if you just sit there.
    Will Rogers

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

    Re: Connect to SQL Server via VBA (2003 SP 3)

    Your variable qdf is of type QueryDefs, i.e. it represents the collection of *all* queries in the database. Connect is not a property of the QueryDefs collection as a whole, it is a property of (among others) a single QueryDef. So you should declare qdf as a QueryDef and create it:

    Dim qdf As DAO.QueryDef
    Set qdf = dbs.CreateQueryDef

  3. #3
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Connect to SQL Server via VBA (2003 SP 3)

    Hans,

    Thank you. I made the changes, and now it compiles in the de######, which is a HUGE step in the right direction. LOL

    Now I get a run-time error '3420': Object invalid or no longer set. on "qdf.Execute". I know I have a good connection string as I can see the table in the database window and can open it and see records. I am running Microsoft Jet 4.0.9511.0, which I believe to be the latest.

    Any ideas where I should look?

    Thanks,

    Ken

  4. #4
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Connect to SQL Server via VBA (2003 SP 3)

    This isn't the answer to your problem, but noting your EXEC string, I wonder if you need quotes. The following is just a sample of a call I make. I pass a procedure and a starter date into a module, and then in the module, create the connection and a DAO recordset to hold the rows received. I think if you pass string (or date) parameters, quotes are required -- I am not sure.

    Set PatSet = PatsConnection.Execute("EXEC " & Chr$(34) & WhatProc & Chr$(34) & " " & Chr$(39) & CDate(WhatStarterDate) & Chr$(39))

    Pat

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

    Re: Connect to SQL Server via VBA (2003 SP 3)

    Check out Patricia's reply - I don't know anything about stored procedures in SQL Server.

  6. #6
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Connect to SQL Server via VBA (2003 SP 3)

    Patricia,

    Thanks for the response. I placed the Chr$(34) on either side of the procedure name so it now looks like:
    strSQL = "EXEC " & Chr$(34) & SDUpdate & Chr$(34) & " @SID = " & Me.SID & _

    Unfortunately, I get the same results. I'll go back in and check the stored procedure again.

    THANKS!

    Ken


    with the same results. Ugh!

  7. #7
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Connect to SQL Server via VBA (2003 SP 3)

    Ken -- I think your "EXEC SDUpdate @SID = " is fine, if SDUpdate is the actual name of your procedure. I have the Chr(34) around the procedure name because it is a variable containing the name of the procedure.

    I think if you just skip to your first string variable that you are passing -- "txtName" -- if you wrap a sinqle quote (chr(39)) around that one and the other text or date variables, that may be all you need.

    thx
    Pat

  8. #8
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Connect to SQL Server via VBA (2003 SP 3)

    Pat,

    I'll give it a go and let you know.

    Thanks!

    Ken

  9. #9
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Connect to SQL Server via VBA (2003 SP 3)

    Hans,

    As usual you were correct, in that it was nothing in Access VBA world. Thanks

    Pat,
    I am still troubleshooting the way the data is passed to the stored procedure. I am working today on modifiying the stored procedure to take a single parameter, and once successful will add the others.

    Thanks,

    Ken

  10. #10
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Connect to SQL Server via VBA (2003 SP 3)

    Patricia,

    After several attempts, I modified the stored procedure to update a single field. I tried executing the following inserting the variables in the code instead of the the controls on the form, and I still get the same error.

    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDefs
    Dim strSQL As String
    Dim strUID As String
    Dim strPWD As String
    Dim strConnect As String
    Dim strDATABASE As String

    Set dbs = CurrentDb
    Set qdf = dbs.QueryDefs
    'Set qdf = dbs.QueryDef

    strSQL = "EXEC @SID = 'Ken 777' " & _
    ", @S_Pkey = 1"


    strUID = "KWVH"
    strPWD = "kwvh"
    strDATABASE = "KWVH_SQL"

    strConnect = "ODBC;DRIVER={SQL Server}" _
    & ";SERVER = LAPTOP " _
    & ";DATABASE = " & strDATABASE _
    & ";UID =" & strUID _
    & ";PWD =" & strPWD & ";"

    qdf.Connect = strConnect


    qdf.SQL = strSQL
    qdf.ReturnsRecords = False
    qdf.Execute


    Set qdf = Nothing
    Set dbs = Nothing

    End Sub


    I can execute ==> exec SiteDyncorpUpdate @S_PKey = '1', @SID ='Ken 777' <== from a new query window in SQL Server and it works fine.

    Any ideas what I am doing incorrectly?

    Thanks!

    Ken

  11. #11
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Connect to SQL Server via VBA (2003 SP 3)

    Ken -- it's possible that the query window is more forgiving. I know from experience here, that sometimes happens.

    I am not that much of a virtuoso with connection code, and like so many things, there are a lot of ways to do it. I don't know if there are other issues with your code, but will just focus on the string you send to your stored procedure.

    Here is a stored procedure, and what the procedure expects to get:
    CREATE PROCEDURE PatsProc @StartDate as datetime, @EndDate as Datetime

    Here's a modification of what I already sent you, to send information to that stored proc. I have already created the connection (PatCn), and want to return a recordset: Set PatSet = PatCn.Execute("EXEC " & Chr$(34) & WhatProc & Chr$(34) & " " & Chr$(39) & StartDt & Chr$(39) & ", " & Chr$(39) & EndDt & Chr$(39))

    You are including proc variables in the string in the form of "@StartDate =", which I am sure is a good way to do it, I am just sending the variables serially.

    (All of a sudden, I don't see the stored procedure 'SiteDyncorpUpdate ' you are calling in the code below -- that might be a big part of it ... )
    Since you have the actual name of the procedure, you don't need a string wrap for that:

    I recommend changing your strSQL from "EXEC @SID = 'Ken 777' , @S_Pkey = 1" To:
    "EXEC SiteDyncorpUpdate @SID = " & chr$(39) & "Ken 777" & chr$(39) & ", @S_Pkey = 1" (if S_Pkey is a number) or
    "EXEC SiteDyncorpUpdate @SID = " & chr$(39) & "Ken 777" & chr$(39) & ", @S_Pkey = " & chr$(39) & "1" & chr$(39) (if S_Pkey is a string)

    Or, you could simply try "EXEC SiteDyncorpUpdate " & chr$(39) & "Ken 777" & chr$(39) & ", 1"

    You've got to explicitly show strings. Instead of the "chr 34 or 39" that I like to use, you could pile on the quotes ( ""' or """ -- or " " ' or " " " ) -- but I have never opted to go that way, so I do not know if I got that correct. Also, you can probably drop the "$" sign, it's "voodoo" on my part. Also, not important for your question, but I also usually wrap the date fields with a "cdate" function.

  12. #12
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Connect to SQL Server via VBA (2003 SP 3)

    Patricia,

    Thanks for ALL your help. And you too Hans. After what seems like weeks of trying, and using Patricia's ideas (and I may not have been following your instructions correctly), I made the plunge today and contacted Microsoft. After an hour or attempts, modify and attempt again, the solution ended up being a generic pass through query and the following code:
    Dim dbs As DAO.Database
    Dim strSQL As String
    strSQL = "exec SDUpdate " & Me.txtPkey & ", '" & Me.SID & "'"
    CurrentDb.QueryDefs("MyPassThrough").SQL = strSQL
    DoCmd.OpenQuery "MyPassThrough"
    Exit Sub

    Where the MyPassThrough query has "stuff" in the SQL window, that gets replaced by the above strSQL string each time it is run. But I suspect the main thing is it contained the ODBC Connect String("ODBC;DSN=AIRR_TEST;Description=AIRR_TEST;U ID=AIRR;PWD=airr;").

    I don't claim to know why or how this worked, but it did.

    THANKS Hans and Patricia for all your time and help.

    Ken

  13. #13
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Connect to SQL Server via VBA (2003 SP 3)

    . Thanks, Ken, I'm glad you got it working.
    . There were really two parts to this question -- the SQL connect and exec, and the actual build of the string.
    . As far as the sql connect & exec, there are variations on how it can be done, and it was informative for me to see how you did it.
    . As far as the string build goes, however, I think you could use some practice in just figuring out how to build that. A good, basic way to start learning that is to create code in a form which opens a report based on a large recordset, which you will limit by sending it a criteria or "where" clause in the "docmd.openreport" VBA call, based on selected information on the form (or, the current record on the form). Building a "criteria" string for your "where" clause, would have given you the knowledge to build the string that sent the parameters to the stored procedure -- they are both based on the same formatting principles.
    . I would urge you to search on the word, "criteria," in Access help -- but keep in mind that sending out search strings in Access are slightly different than in MSSql server -- dates and text accept single quotes in SQL, and in Access, the date fields take "pound" signs and text fields take double quotes. None of the help files in Access use my formula (chr(34 or 39)), but you'll get the picture. Once you've mastered sending criteria out, then, you are freer to concentrate on the other issues (like, making the connections to your sql server).
    . Don't neglect your local Access education opportunities. There is usually some place offering Advanced-level classes, and these instructors are often available to help on specific issues you might have.
    Pat

  14. #14
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Connect to SQL Server via VBA (2003 SP 3)

    Patricia,

    Thanks for the additional followup. I plan on getting plenty of practice, although it may be as a part of completing this and two other Access/SQL Server. I will also continue to play with the various connection options. I would really like to find an ADO connection resource, as I think it may be easier than DAO. I am still trying to get my arms around the DAO and ADO models, and most everything I have done in the past has been DAO.

    As a side note, one of the kewl things about the approach is that by modifying the MyPassThrough query each time in VBA, it was a GREAT troubleshooting tool. Each time I run it I can open the MyPassThrough in design view and see ALL my quotations within context. He didn't mention that, but it sure helped me quickly locate an "ID10T" issue. After I put all 11 parameters into the strSQL statement, I had included an extra single quote which was VERY OBVIOUS when I looked at the query in design view.

    Thank you again for all your help. I am also seeking local resources, but "local" here means an hour drive and $ for fuel. LOL

    Ken

Posting Permissions

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