VB6 - DAO - Stored procedures

0
votes

I know that it's not 1998, but for various reasons, I'm required to change a VB6 app's database interface from ADO to DAO. I haven't worked with DAO before, and all of our database transactions take place on SQL Server 2008 using stored procedures. How would I go about executing stored procedures with input and output parameters using DAO within VB6?

2
stored-proceduresvb6dao
Ouch! I think the first thing you should look at is data type mapping because DAO doesn't have many data types :( – onedaywhen
No, I think the first thing he should do is look for another job. – John Saunders

2 Answers

1
votes

You can Execute a QueryDef, for example:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim scn As String

Set db = CurrentDb

''http://connectionstrings.com
scn = "ODBC;Description=test;DRIVER=SQL Server;SERVER=server;" _
    & "Trusted_Connection=Yes;DATABASE=test"

''For this example
SiteID = 3
MachineID = "abc"

Set qdf = db.CreateQueryDef("")
qdf.Connect = scn
''This is important, if you wish to execute the query
qdf.ReturnsRecords = False
qdf.SQL = "dbo.sproc_auditlog @siteid=" & SiteID & ", @machine='" & MachineID & "'"
qdf.Execute dbFailOnError
1
votes

Apparently this is the Microsoft way:

http://support.microsoft.com/kb/168210

The code template that I am using is roughly as follows:

Dim rs As Recordset
Dim ws As Workspace
Dim db As Database
Dim sproc As String

Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase("", False, False, conString)

sproc = "EXEC sproc_AuditLog @site=" + siteID + ", @machine='" + MachineID + "'"
Set rs = db.OpenRecordset(sproc, dbOpenSnapshot, dbSQLPassThrough)
rs.Close

As you can see, I am essentially building up the EXEC statement in a string, then using dbSqlPassThrough to send it directly to ODBC, since DAO has no inherent way to execute stored procedures (such as ADO's Execute function).