2

I'm using SQL Server 2008 R2 and I've created a User Defined Type:

create type dbo.UDT as table (FirstName varchar(50) null, LastName varchar(50) null)

I've create a stored procedure that has this UDT as a parameter:

create procedure dbo.InsertIntoMyTableUsingUDT (@udt dbo.UDT readonly)
as
begin
    set nocount on;
    insert into dbo.MyTable (FirstName, LastName)
    select FirstName, LastName from @udt;
    return;
end

I would like to use VBA's ADO (ADO.COM/OLE DB) to bulk upload 10,000+ records using this stored procedure.
I've tried passing an ADODB.Recordset as a parameter in an ADODB.Command:

<code that creates ADODB.Recordset 'rst' here...>

Dim com as ADODB.Command
Set com = new ADODB.Command
With com
    .ActiveConnection = "Provider=SQLOLEDB.1;Data Source=localhost;Initial Catalog=TestDB;Integrated Security=SSPI;"
    .CommandText = "dbo.InsertIntoMyTableUsingUDT"
    .CommandType = adCmdStoredProc
    .Parameters.Refresh
    .Parameters(1).Value = rst '<----the ADODB.Recordset created above
    .Execute
End With

but I get the runtime error:

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

Checking the parameter's type in the Immediate Window I get:

?com.Parameters(1).Type
143

which I can't see in MSFT's list of OLE DB DataTypeEnums:

http://msdn.microsoft.com/en-gb/library/windows/desktop/ms675318(v=vs.85).aspx

So I tried passing in a 2-dimensional array instead of the ADODB.Recordset but then I got the error:

Bad variable type.

Both attempts failed and it's got me stumped.
I know it can be done in VB.NET using ADO.NET and the SqlParameterCollection.AddWithValue method.
I would like to know if there is a way of doing it in VBA using ADO.COM. Has anyone done it?
Alternatively, is there a different way of achieving the same thing (bulk upload rather than calling an ADODB.Command 10,000+ times in a loop)?

Cheers

PingPing
  • 899
  • 6
  • 15
  • 28
  • It is not difficult to write a query (eg http://stackoverflow.com/questions/14653324/insert-into-access-from-sql-server/14653388#14653388 ) or a pass-through query in MS Access. – Fionnuala Aug 28 '14 at 10:21
  • Thanks for the quick response. I'm trying to pass a VBA object into an SQL Server stored procedure as the DBAs don't give write access on tables. – PingPing Aug 28 '14 at 10:33
  • I do not think you can do that, but perhaps things have changed over the past few years. – Fionnuala Aug 28 '14 at 10:37
  • 3
    you are trying to send adodb.recordset as parameter value where your stp takes udp type as parameter. adodb.recordset object has more than just firstname and lastname hence your sql server will not know how to extract or understand the object. What you can do, create another stored procedure accepting string, send your records as strings with a delimiter. in the stored procedure split the string using the delimiter and insert?? – Krish Aug 28 '14 at 10:58
  • @Fionnuala you can definitely call a SQL Server stored proc to insert bulk data. When we do it, we don't pass a recordset. Instead, we ammend the QueryString of the command object to contain all the necessary arguments for the stored proc. This probably doesn't help much, but don't get discouraged! It can be done! – CodeJockey Oct 03 '14 at 20:56

0 Answers0