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 a variable to a Linked Query (OPENROWSET for Excel Syntax)

Hello,

I responded to a very old discussion thread & afraid I buried it too deep.

I have studied the article: How to Pass a Variable to a Linked Query (http://support.microsoft.com/default.aspx?scid=kb;en-us;q314520)

but I have not gotten all the ''''' + @.variable syntax right.

Here is my raw openrowset with what I am aiming at.

Code Snippet

-- I want to use some kind of variable, like this to use in the file:

DECLARE @.FIL VARCHAR(65)

SET @.FIL = 'C:\company folders\Documentation\INVENTORY.xls;'

--

SELECT FROM OPENROWSET('MSDASQL', 'Driver=Microsoft Excel Driver (*.xls);DBQ=C:\company folders\Documentation\INVENTORY.xls;', 'SELECT * FROM [Inventory$]')

AS DT

Anyone game? Many thank-yous, in advance.

Kind Regards,

Claudia.

You can make use of the QUOTENAME function to help you out here.

I couldn't get the MSDASQL Excel driver to work on my desktop, but below is an example that uses the same principles but with the Jet Excel driver. Simply modify the values of the provider, connection string, filename and query variables as appropriate.

Chris

Code Snippet

DECLARE @.SQL NVARCHAR(4000)

DECLARE @.Provider NVARCHAR(100)

DECLARE @.FIL NVARCHAR(256)

DECLARE @.ConnectionString NVARCHAR(1000)

DECLARE @.Query NVARCHAR(1000)

SET @.Provider = N'Microsoft.Jet.OLEDB.4.0'

SET @.FIL = N'C:\Company Folders\Documentation\INVENTORY.xls'

SET @.ConnectionString = N'Excel 8.0;DATABASE=' + @.FIL

SET @.Query = N'SELECT * FROM [Inventory$]'

SET @.SQL = N'SELECT *

FROM OPENROWSET(' + QUOTENAME(@.Provider, N'''') + N', '

+ QUOTENAME(@.ConnectionString, N'''') + N', '

+ QUOTENAME(@.Query, N'''') + N')'

EXEC sp_executesql @.SQL