Bulk insert into a sql server table via VBA

[marser]

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

 

[mdlueck]

I have an application that downloads records from an iSeries / AS/400 server running DB2 and publishing to SQL Server. Passing through an Access table is the mid state of the data.... download to Access, then publishing to SQL Server.

Perhaps successfully import the CSV to an Access table, then publish the imported records to SQL Server.

Also... I got really aggravated with CSV handling in Office 2007. Between Access and Excel, MS is inconsistent when fields will / will not be quoted. I finally wrote my own CSV import code in Access / VBA processing the CSV data one character at a time. Now I have no more CSV read problems.