Connect to SQL Server via VBA (2003 SP 3)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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!

Reply | Quote

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

Reply | Quote

Pat,

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

Thanks!

Ken

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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;UID=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

Reply | Quote

. 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

Reply | Quote

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

Reply | Quote