Passing DataSet to MS Access Report

I have many MS Access reports that process recordsets obtained from a MySQL database, based on a user-selected date range. This uses VBA and input boxes. I'm now creating .aspx pages to get the user input via the web, and am successful in creating a DataSet. My .aspx.vb code includes using Automation to open the Access report in Snapshot Viewer (DoCmd.OutputTo). How do I pass the DataSet to MS Access to replace using recordsets?

My VBA code in Access used to be this:

Code Snippet

Dim ws As Workspace
Dim strConnection As String
Dim dbs As Database

Dim rst_chg As Recordset

Set ws = DBEngine.Workspaces(0)
strConnection = "ODBC;DSN=xxx;DATABASE=xxx;" _
& "SERVER=10.1.144.xxx;" _

& "UID=xxx;PWD=xxx;PORT=xxx;OPTION=0;" _

& "STMT=set wait_timeout=100000;;"

Set dbs = ws.OpenDatabase("", True, True, strConnection)

Set rst_chg = dbs.OpenRecordset("SELECT ...")

'process the recordset ...

I'm thinking I should be able to eliminate most of this code and Set rst_chg = DataSet. I've been successful with using WriteXml in the .aspx.vb page and Application.ImportXML in my VBA to pass the data using XML, but this writes to the hard drive, and also creates a database in Access (overhead I would rather not have to deal with). Again, is there a way to open the DataSet directly in my VBA code?

Thanks,

Guy Rivers

By DataSet you mean ado.net object? If so, I'm afraid there is no way to directly access a managed object from a native VBA script.

Passing DataSet to MS Access Report

I have many MS Access reports that process recordsets obtained from a MySQL database, based on a user-selected date range. This uses VBA and input boxes. I'm now creating .aspx pages to get the user input via the web, and am successful in creating a DataSet. My .aspx.vb code includes using Automation to open the Access report in Snapshot Viewer (DoCmd.OutputTo). How do I pass the DataSet to MS Access to replace using recordsets?

My VBA code in Access used to be this:

Code Snippet

Dim ws As Workspace
Dim strConnection As String
Dim dbs As Database

Dim rst_chg As Recordset

Set ws = DBEngine.Workspaces(0)
strConnection = "ODBC;DSN=xxx;DATABASE=xxx;" _
& "SERVER=10.1.144.xxx;" _

& "UID=xxx;PWD=xxx;PORT=xxx;OPTION=0;" _

& "STMT=set wait_timeout=100000;;"

Set dbs = ws.OpenDatabase("", True, True, strConnection)

Set rst_chg = dbs.OpenRecordset("SELECT ...")

'process the recordset ...

I'm thinking I should be able to eliminate most of this code and Set rst_chg = DataSet. I've been successful with using WriteXml in the .aspx.vb page and Application.ImportXML in my VBA to pass the data using XML, but this writes to the hard drive, and also creates a database in Access (overhead I would rather not have to deal with). Again, is there a way to open the DataSet directly in my VBA code?

Thanks,

Guy Rivers

By DataSet you mean ado.net object? If so, I'm afraid there is no way to directly access a managed object from a native VBA script.

Passing by reference an ADO Connection from Excel VBA to C++

I want to pass by reference an ADO Connection which has been already opened in Excel VBA to a C++ DLL, but I get the following error:

"Unhandled exception at 0x4dd5230f in EXCEL.EXE: 0xC0000005: Access violation writing location 0x1775238d."

What am I doing wrong?

The code I am using is:

- VBA:

Declare Function Retrieve_C Lib "xxx.dll" (ByRef conn As ADODB.Connection) As Double
Function Test() As Double
Dim c As ADODB.Connection
Set c = New ADODB.Connection
c.Open "Provider=MSDASQL; Data Source=xxx"
Test = Retrieve_C(c)
End Function

- C++:

#import "xxx\msado15.dll" rename("EOF","ADOEOF")
double __stdcall Retrieve_C(ADODB::_ConnectionPtr conn)
{
CoInitialize(NULL);
ADODB::_RecordsetPtr recordset(__uuidof(ADODB::Recordset));
recordset->Open("SELECT xxx",
conn.GetInterfacePtr(),
ADODB::adOpenForwardOnly,
ADODB::adLockReadOnly,
ADODB::adCmdText);
return recordset->Fields->GetItem("xxx")->GetValue();
recordset->Close();
}

I have moved this thread to the native data access forum. You are more likely to get a response there, since this is native ADO and not ADO.NET.

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=87&SiteID=1

Thanks,

Sarah

|||

Try using "xxx.dll" (ByVal conn As ADODB.Connection) instead.

(Passing conn ByVal instead of ByRef)

Also as a side note, you've placed call to recordset close method after the return statement...

---

This posting is provided "AS IS" with no warranties, and confers no rights.

sql

Passing back more than 1 output parameter to VBA code

I have a stored procedure which has 2 output parameters, namely @.RecCnt and
@.RetCode. In the stored procedure, I am using the SET statements to pass the
data back. I am calling the stored procedure from my VBA code. I use
objCmd.Execute options:=adExecuteNoRecords.
I am able to retrieve only the @.RetCode value and not the @.RecCnt value.
Could any of you tell me what is wrong?
VBA Code:
--
Set objCmd = New ADODB.Command
With objCmd
.CommandText = "sp_addback_selectcount_CorpAcctCtr"
.NAME = "sp_addback_selectcount_CorpAcctCtr"
.CommandType = adCmdStoredProc
'Create parameter list for objCmd
.Parameters.Append .CreateParameter("Corp", adVarChar, adParamInput,
3, vstrCorp)
.Parameters.Append .CreateParameter("Acct", adVarChar, adParamInput,
5, vstrAcct)
.Parameters.Append .CreateParameter("Ctr", adVarChar, adParamInput, 5,
vstrCtr)
.Parameters.Append .CreateParameter("RecCnt", adInteger,
adParamOutput, 4)
.Parameters.Append .CreateParameter("RetCode", adBoolean,
adParamOutput, 1)
.ActiveConnection = objCon
.Execute options:=adExecuteNoRecords
End With
If objCmd.Parameters("RetCode").Value Then
rlngRecCnt = objCmd.Parameters("RecCnt").Value
fnGetAddbackCnt = True
End If
--Store Procedure
CREATE PROCEDURE sp_lotusdata_load_from_recon
@.Corp nvarchar(3),
@.Acct nvarchar(5),
@.Ctr nvarchar(5),
@.RecsAffected int OUTPUT,
@.RetCode bit OUTPUT
AS
-- local variables
DECLARE @.ErrorNum smallint
DECLARE @.RecCnt smallint
-- initialization
SET @.RetCode = 0
SET @.ErrorNum = 0
-- logic
SELECT @.RecCnt = COUNT(*)
FROM RECON
WHERE CORP = @.Corp
AND ACCT = @.Acct
AND CTR = @.Ctr
IF @.RecCnt > 0
BEGIN
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'lotusdata'
AND type = 'U')
BEGIN
DROP TABLE LOTUSDATA
SELECT RECON.*
INTO LotusData
FROM RECON
WHERE CORP = @.Corp
AND ACCT = @.Acct
AND CTR = @.Ctr
END
END
SELECT @.ErrorNum = @.@.ERROR
IF @.ErrorNum = 0
BEGIN
SET @.RecsAffected = @.RecCnt
SET @.RetCode = 1
END
GOSoooorrryyy...Goofed up the code and stored procedure...
Here is the correct one:
VBA Code:
......
.....
Set objCmd = New ADODB.Command
With objCmd
.CommandText = "sp_lotusdata_load_from_recon"
.NAME = "sp_lotusdata_load_from_recon"
.CommandType = adCmdStoredProc
'Create parameter list for oCmd
.Parameters.Append .CreateParameter("Acct", adVarChar, adParamInput,
Len(vstrAcct), vstrAcct)
.Parameters.Append .CreateParameter("Corp", adVarChar, adParamInput,
Len(vstrCorp), vstrCorp)
.Parameters.Append .CreateParameter("Ctr", adVarChar, adParamInput,
Len(vstrCtr), vstrCtr)
.Parameters.Append .CreateParameter("RecsAffected", adInteger,
adParamOutput, 4)
.Parameters.Append .CreateParameter("RetCode", adBoolean,
adParamOutput, 1)
.ActiveConnection = objCon
'-- execute the proc
.Execute options:=adExecuteNoRecords
'-- return success if stored proc is successful
If .Parameters("RetCode").Value = True Then
rintRecsAffected = .Parameters("RecsAffected")
fnLoadLotusData = True
End If
....
...
Stored Procedure Code:
--
CREATE PROCEDURE sp_lotusdata_load_from_recon
@.Corp nvarchar(3),
@.Acct nvarchar(5),
@.Ctr nvarchar(5),
@.RecsAffected int OUTPUT,
@.RetCode bit OUTPUT
AS
-- local variables
DECLARE @.ErrorNum smallint
DECLARE @.RecCnt smallint
-- initialization
SET @.RetCode = 0
SET @.ErrorNum = 0
-- logic
SELECT @.RecCnt = COUNT(*)
FROM RECON
WHERE CORP = @.Corp
AND ACCT = @.Acct
AND CTR = @.Ctr
IF @.RecCnt > 0
BEGIN
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'lotusdata'
AND type = 'U')
BEGIN
DROP TABLE LOTUSDATA
SELECT RECON.*
INTO LotusData
FROM RECON
WHERE CORP = @.Corp
AND ACCT = @.Acct
AND CTR = @.Ctr
END
END
SELECT @.ErrorNum = @.@.ERROR
IF @.ErrorNum = 0
BEGIN
SET @.RecsAffected = @.RecCnt
SET @.RetCode = 1
END
GO
Sorry for the error.
Regards,
Paddy
"Paddy" wrote:

> I have a stored procedure which has 2 output parameters, namely @.RecCnt an
d
> @.RetCode. In the stored procedure, I am using the SET statements to pass t
he
> data back. I am calling the stored procedure from my VBA code. I use
> objCmd.Execute options:=adExecuteNoRecords.
> I am able to retrieve only the @.RetCode value and not the @.RecCnt value.
> Could any of you tell me what is wrong?
> VBA Code:
> --
> Set objCmd = New ADODB.Command
> With objCmd
> .CommandText = "sp_addback_selectcount_CorpAcctCtr"
> .NAME = "sp_addback_selectcount_CorpAcctCtr"
> .CommandType = adCmdStoredProc
> 'Create parameter list for objCmd
> .Parameters.Append .CreateParameter("Corp", adVarChar, adParamInput,
> 3, vstrCorp)
> .Parameters.Append .CreateParameter("Acct", adVarChar, adParamInput,
> 5, vstrAcct)
> .Parameters.Append .CreateParameter("Ctr", adVarChar, adParamInput,
5,
> vstrCtr)
> .Parameters.Append .CreateParameter("RecCnt", adInteger,
> adParamOutput, 4)
> .Parameters.Append .CreateParameter("RetCode", adBoolean,
> adParamOutput, 1)
> .ActiveConnection = objCon
> .Execute options:=adExecuteNoRecords
> End With
>
> If objCmd.Parameters("RetCode").Value Then
> rlngRecCnt = objCmd.Parameters("RecCnt").Value
> fnGetAddbackCnt = True
> End If
> --Store Procedure
> CREATE PROCEDURE sp_lotusdata_load_from_recon
> @.Corp nvarchar(3),
> @.Acct nvarchar(5),
> @.Ctr nvarchar(5),
> @.RecsAffected int OUTPUT,
> @.RetCode bit OUTPUT
> AS
> -- local variables
> DECLARE @.ErrorNum smallint
> DECLARE @.RecCnt smallint
> -- initialization
> SET @.RetCode = 0
> SET @.ErrorNum = 0
> -- logic
> SELECT @.RecCnt = COUNT(*)
> FROM RECON
> WHERE CORP = @.Corp
> AND ACCT = @.Acct
> AND CTR = @.Ctr
> IF @.RecCnt > 0
> BEGIN
> IF EXISTS (SELECT name
> FROM sysobjects
> WHERE name = N'lotusdata'
> AND type = 'U')
> BEGIN
> DROP TABLE LOTUSDATA
> SELECT RECON.*
> INTO LotusData
> FROM RECON
> WHERE CORP = @.Corp
> AND ACCT = @.Acct
> AND CTR = @.Ctr
> END
> END
> SELECT @.ErrorNum = @.@.ERROR
> IF @.ErrorNum = 0
> BEGIN
> SET @.RecsAffected = @.RecCnt
> SET @.RetCode = 1
> END
> GO|||Paddy (Paddy@.discussions.microsoft.com) writes:
> I have a stored procedure which has 2 output parameters, namely @.RecCnt
> and @.RetCode. In the stored procedure, I am using the SET statements to
> pass the data back. I am calling the stored procedure from my VBA code.
> I use objCmd.Execute options:=adExecuteNoRecords.
> I am able to retrieve only the @.RetCode value and not the @.RecCnt value.
> Could any of you tell me what is wrong?
How do you conclude that you can not retriev the RecCnt value?
I don't think it should really matter, but it is a good idea to align
the names in the parameters collection with the actual parameters names.
Thus, the names should be @.Corp, @.Acct, @.Ctr, @.RowsAffected and
@.RetCode. Furthermore there is one parameter missing. That is, each
stored procedure has a return value, which in ADO you declare as a the
first parameter and as adParamReturnValue. Then again, I think it's find
to not include that paraemeter.

> .CommandText = "sp_addback_selectcount_CorpAcctCtr"
The sp_ prefix is reserved for system objects, and SQL Server first
looks in the master database for these. Don't use it, in your own code.

> .Parameters.Append .CreateParameter("RecCnt", adInteger,
> adParamOutput, 4)
> .Parameters.Append .CreateParameter("RetCode", adBoolean,
> adParamOutput, 1)
I think adParamInputOutput are more appropriate as that is what they
are.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||The above code always return RecsAffected value as 0. When I run the sp in
Query Analyser it shows the correct data.
Please help.
Regards,
Paddy
"Paddy" wrote:
> Soooorrryyy...Goofed up the code and stored procedure...
> Here is the correct one:
> VBA Code:
> ......
> .....
> Set objCmd = New ADODB.Command
> With objCmd
> .CommandText = "sp_lotusdata_load_from_recon"
> .NAME = "sp_lotusdata_load_from_recon"
> .CommandType = adCmdStoredProc
> 'Create parameter list for oCmd
> .Parameters.Append .CreateParameter("Acct", adVarChar, adParamInput,
> Len(vstrAcct), vstrAcct)
> .Parameters.Append .CreateParameter("Corp", adVarChar, adParamInput,
> Len(vstrCorp), vstrCorp)
> .Parameters.Append .CreateParameter("Ctr", adVarChar, adParamInput,
> Len(vstrCtr), vstrCtr)
> .Parameters.Append .CreateParameter("RecsAffected", adInteger,
> adParamOutput, 4)
> .Parameters.Append .CreateParameter("RetCode", adBoolean,
> adParamOutput, 1)
> .ActiveConnection = objCon
> '-- execute the proc
> .Execute options:=adExecuteNoRecords
> '-- return success if stored proc is successful
> If .Parameters("RetCode").Value = True Then
> rintRecsAffected = .Parameters("RecsAffected")
> fnLoadLotusData = True
> End If
> ....
> ...
> Stored Procedure Code:
> --
> CREATE PROCEDURE sp_lotusdata_load_from_recon
> @.Corp nvarchar(3),
> @.Acct nvarchar(5),
> @.Ctr nvarchar(5),
> @.RecsAffected int OUTPUT,
> @.RetCode bit OUTPUT
> AS
> -- local variables
> DECLARE @.ErrorNum smallint
> DECLARE @.RecCnt smallint
> -- initialization
> SET @.RetCode = 0
> SET @.ErrorNum = 0
> -- logic
> SELECT @.RecCnt = COUNT(*)
> FROM RECON
> WHERE CORP = @.Corp
> AND ACCT = @.Acct
> AND CTR = @.Ctr
> IF @.RecCnt > 0
> BEGIN
> IF EXISTS (SELECT name
> FROM sysobjects
> WHERE name = N'lotusdata'
> AND type = 'U')
> BEGIN
> DROP TABLE LOTUSDATA
> SELECT RECON.*
> INTO LotusData
> FROM RECON
> WHERE CORP = @.Corp
> AND ACCT = @.Acct
> AND CTR = @.Ctr
> END
> END
> SELECT @.ErrorNum = @.@.ERROR
> IF @.ErrorNum = 0
> BEGIN
> SET @.RecsAffected = @.RecCnt
> SET @.RetCode = 1
> END
> GO
> Sorry for the error.
> Regards,
> Paddy
> "Paddy" wrote:
>|||Hi, Erland,
Please read my 2nd and 3rd message. I copied the wrong code in the message.
I posted the code which has problem in my second message.
I debugged the VBA code and know it is returning 0.
I knew about sp_ prefix, but used it for easily distinguish stored
procedures. I know there is some performance penalties.
Anyway, is there anything wrong in the way I am setting the output parameter
in the stored procedure?
Thanks.
Paddy
"Erland Sommarskog" wrote:

> Paddy (Paddy@.discussions.microsoft.com) writes:
> How do you conclude that you can not retriev the RecCnt value?
> I don't think it should really matter, but it is a good idea to align
> the names in the parameters collection with the actual parameters names.
> Thus, the names should be @.Corp, @.Acct, @.Ctr, @.RowsAffected and
> @.RetCode. Furthermore there is one parameter missing. That is, each
> stored procedure has a return value, which in ADO you declare as a the
> first parameter and as adParamReturnValue. Then again, I think it's find
> to not include that paraemeter.
>
> The sp_ prefix is reserved for system objects, and SQL Server first
> looks in the master database for these. Don't use it, in your own code.
>
> I think adParamInputOutput are more appropriate as that is what they
> are.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Paddy (Paddy@.discussions.microsoft.com) writes:
> The above code always return RecsAffected value as 0. When I run the sp in
> Query Analyser it shows the correct data.
But RetCode is still True then?
When you run from QA, I suspect that you run as as or dbo, but how
do run the application? Does that account have CREATE TABLE permissions?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx