Hi all,
I am trying to bulk insert a local csv file into a remote SQL Server via ACCESS VBA. The problem is the remote SQL Server is not allowed using "BULK insert". Moreover, The remote SQL server is not able to get the data from the local machine and I am not able to put the data file in the server, so the following code doesn't work
Public Const provStr As String = "Provider=sqloledb;Server=EXAMPLE;Integrated Security=SSPI;Database=PPES; ConnectionTimeout = 900; CursorLocation = adUseClient;"
Sub LoadCSVToSQL()
Dim cn As New ADODB.Connection
Dim strSQL As String
cn.Open provStr
strSQL = "SELECT * INTO [remotetable] FROM [Text;HDR=NO;DATABASE=c:\LocalFolder].[FileToLoad.csv]"
cn.Execute strSQL
cn.Close
End Sub
My idea is to use VBA load the csv file to the local memory(recordset) and then bulk insert these records into remote SQL Server table, but seems that it is impossible. Could someone help me on this? Much appreciated.
Thanks,
Marser
I am trying to bulk insert a local csv file into a remote SQL Server via ACCESS VBA. The problem is the remote SQL Server is not allowed using "BULK insert". Moreover, The remote SQL server is not able to get the data from the local machine and I am not able to put the data file in the server, so the following code doesn't work
Public Const provStr As String = "Provider=sqloledb;Server=EXAMPLE;Integrated Security=SSPI;Database=PPES; ConnectionTimeout = 900; CursorLocation = adUseClient;"
Sub LoadCSVToSQL()
Dim cn As New ADODB.Connection
Dim strSQL As String
cn.Open provStr
strSQL = "SELECT * INTO [remotetable] FROM [Text;HDR=NO;DATABASE=c:\LocalFolder].[FileToLoad.csv]"
cn.Execute strSQL
cn.Close
End Sub
My idea is to use VBA load the csv file to the local memory(recordset) and then bulk insert these records into remote SQL Server table, but seems that it is impossible. Could someone help me on this? Much appreciated.
Thanks,
Marser