3

The process is run in the environment of Excel VBA 2010 and MS SQL Server 2008.

Assume that there is a simple one column data with 1500 rows in an Excel-sheet and we want to export it to the database with SQL-queries in VBA code (SQL procedure in VBA exports maximum 1000 rows at once in default mode).

There is one limitation in this problem: the export procedure must be with dbclass-connection instead of ADODB connection. (The code-owner is not me. The code-owner is using dbclass for a quite big VBA code, so probably he wouldn't accept to change the whole code).

I found an option like lngRecsAff for ADODB.Connection which is used like:

Sub test() 
   Dim cn As ADODB.Connection 
   Dim strSQL As String 
   Dim lngRecsAff As Long 
   Set cn = New ADODB.Connection 
  cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\test.xls; Extended Properties=Excel 8.0" 

  strSQL = "Insert INTO [odbc;Driver={SQL Server};Server=SQL09;Database=Tom;UID=userID;PWD=password].tbl_test1 Select * FROM [Sheet1$]" 
  cn.Execute strSQL, lngRecsAff
  cn.Close 
  Set cn = Nothing 
End Sub

I tried to implement that lngRecsAff in my dbclass execution like:

Sub test()
  Dim connOk As Boolean
  Dim rs As New ADODB.Recordset
  Set dbclass = New clsDB
  Dim Value1() As Variant
  Dim lngRecsAff As Long
  Dim strSQL as String
  Dim mstrErr as Boolean

  dbclass.Database = "database_name"
  dbclass.ConnectionType = SqlServer
  dbclass.DataSource = "server_name"
  dbclass.UserID = Application.UserName

  connOk = dbclass.OpenConnection(False, True)
    If connOk = False Then
       MsgBox "Unsuccessful connection!"
    Else
       MsgBox "Successful connection"
    End If

  strSQL = "INSERT INTO [dbo].[table1](Column1) Values('" & Value1 & "')"
  mstrErr = dbclass.ExecuteSQL(strSQL, lngRecsAff) ' The result mstrErr is a Boolean 
   ' Some closing options here
End Sub

I got en error like lngRecsAff is not suitable for my ExecuteSQL procedure. Normally my execution mstrErr = dbclass.ExecuteSQL(strSQL) works without any problem.

Maybe I can do the SQL-procedure with a for-loop, then I can send the data in small pieces. But I want to find a more efficient, "nicer" solution which sends the whole array at once.

So is there any special option for dbclass which can send more than 1000 rows from Excel to the database?

user3714330
  • 679
  • 12
  • 32
  • Your insert statement doesn't make any sense at all. You don't insert into a connection string. You use a connection string to establish a connection, then you issue an insert into a table. – Sean Lange Dec 10 '15 at 14:52
  • 3
    Could you not use a more appropriate tool for importing data than insert queries embedded within VBA? Such as SSIS? – GarethD Dec 10 '15 at 14:53
  • I am not familiar with SSIS, but now I goggled that. I think there is another option like `OPENROWSET `, I will check it out now. Do you know if `OPENROWSET` option can import long dataset? – user3714330 Dec 10 '15 at 15:01
  • If you *have* to use a custom DB class (and not ADO directly) to do the insert, then you really need to discuss with the owner of that class how best to use it for what you need to do. Since we have no idea how the class is implemented there's not much point in asking here... – Tim Williams Dec 10 '15 at 18:56
  • @tim-williams I edited my code which shows how the code opens the connection with the server. Should I show more information about how the connection class is implemented? – user3714330 Dec 14 '15 at 08:35
  • Your sql concatenation is wrong. `"INSERT INTO [dbo].[table1](Column1) Values(" & Value1 & ")"` – Tim Williams Dec 14 '15 at 16:03
  • @tim-williams ok, sorry, i missed the quotation mark, i wrote the code from my memory. There is no error in the run-time, this code sends the tables to the server without problem. I just wonder if there is a way to edit this connection to send more than 1000 rows at once without changing the connection style. As I mentioned, the code-owner probably woulldn't change the connection from dbclass to `ADODB.Connection`, – user3714330 Dec 14 '15 at 17:21
  • If you wanted to bulk-insert then you could use a recordset object, insert all the records into it, and then call its `UpdateBatch` method. Or check here for other approaches: http://boards.straightdope.com/sdmb/showthread.php?t=289518 – Tim Williams Dec 14 '15 at 18:01

1 Answers1

3

You can query the Excel-Sheet directly using liked server.

In your Management Studio click to "Server Objects" and then right click onto "linked server". There you'll be able to add a new linked server.

If you need further help you can find tuorials easily. One is here: https://www.mssqltips.com/sqlservertip/2018/using-a-sql-server-linked-server-to-query-excel-files/

Shnugo
  • 66,100
  • 9
  • 53
  • 114
  • it seems nice, however, the name of the Excel file is always changing during the "development" process. So it wouldn't work to link to one specific Excel name. – user3714330 Dec 10 '15 at 15:35
  • 1
    You can link (and unlink) a server with T-SQL, find some examples here: http://stackoverflow.com/q/189422/5089204 – Shnugo Dec 10 '15 at 15:42
  • @user3714330, Hi, I'm curious... Did my answer help you to solve your issue? – Shnugo Dec 11 '15 at 09:57
  • Now I am not at work, so I couldn't try it yet. But still it feels like it would be better to solve it inside the vba code. I can present this solution to the code owner, but I don't if he finds this practicle either. – user3714330 Dec 11 '15 at 12:57
  • @user3714330, As we do not know this `clsDB`-thing you cannot expect someone here on SO to know the solution... One good argument should be, that it's always better to involve only tecnologies you really need. It is a question of your rights in the database. If they suffice, it's easys to create a linked server dynamically with the actual Excel name, than do the transfer, then drop the linked server... Best would be a Stored Procedure taking the filename as parameter... – Shnugo Dec 11 '15 at 16:54