Insert Excel data table into Oracle table using ADO - keeps crashing Excel

[becci.gott]

I am trying to upload a data table (approx 20 columns and 3k rows) directly into an Oracle database using the code below (username & password removed for security), but each time I try to run it, Excel is crashing out when it gets to the .Execute (ssql) line.

Public Sub TransfertoOracle()​

​

Dim cn As New ADODB.Connection​

Dim tblr as string​

Dim tblwb As Workbook​

Dim tblws As Worksheet​

Set tblwb = ThisWorkbook​

Set tblws = tblwb.Sheets("Upload")​

​

tblr = "[" & tblws.Name & "$" & Range("DataTable").Address(False, False) & "]"​

​

With cn​

.Open "POLCOMM", "USERID", "PASSWORD"​

​

ssql = "INSERT INTO polreps.temp_sbs_targets "​

ssql = ssql & "SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=" & tblwb.FullName & "]." & tblr​

​

.Execute ssql​

.Close​

End With​

​

Set cn = Nothing​

​

MsgBox "Upload complete"​

End Sub
​

As a workaround, I have set up a linked table in an Access Database and have used that to create a link between my Excel data table and the Oracle database, but ideally I want to remove the Access link as it seems like a step that could easily be lost/broken in the future if I am no longer in the organisation to manage it. This process does work though, without crashing Excel (runs in about 40 seconds).

I am running on 32-bit windows, but will soon be upgraded to 64-bit, so the code needs to work for both.

Any help would be greatly appreciated. Many thanks in advance.

 

[becci.gott]

As an update, I have just tried running this on a 64-bit virtual machine and it does exactly the same thing - so it isn't to do with trying to run it on a 32-bit.

 

[becci.gott]

Has anyone got any ideas?