Bulkcopy in VB to import fixed-length text into table?

[jbradley]

Is there any way to use Bulkcopy in a VB program to import either a fixed-length or CSV file into a SQL table? The following code will import a text file into an Access table but unfortunately the SQLOLEDB provider doesn't have the same text functionality as the Jet provider.

strRetConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
        App.Path & "\Test_Returns.mdb;Persist Security Info=False"

Set cnReturns = New ADODB.Connection
cnReturns.Open strRetConn

cnReturns.Execute "INSERT INTO returns SELECT * FROM " & _
        "[Text;HDR=NO;FMT=Delimited;Database=C:\]." & _
        "[WorkFile.txt]"

cnReturns.Close: Set cnReturns = Nothing

Someone suggested using Bulkcopy but I've been unable to make a start on it. The C example in the SQL Server 7.0 Books Online didn't help.

 

[DougP]

The first problem I see is your conection string is for Access database not SQL

try this
Dim Conn As ADODB.Connection
Dim Rs1 As ADODB.Recordset

Set Conn = New ADODB.Connection
Set Rs1 = New ADODB.Recordset

Conn.Open "driver=SQL Server;server=yourSQLServer;uid=sa;pwd=;database=yourdatabase;"
Dim SQLCode As String
SQLCode = "INSERT INTO returns SELECT * FROM " & _
"[Text;HDR=NO;FMT=Delimited;Database=C:\]." & _
"[WorkFile.txt]"


Rs1.Open SQLCode, Conn, adOpenStatic, adLockOptimistic

'Do some stuff with the recordset

Set Rs1 = Nothing
Set Conn = Nothing
DougP, MCP

 

[jbradley]

I had modified the connection string appropriately. Here is what I discovered elsewhere:

It turns out that the SQLOLEDB provider doesn't have the same text functionality as the Jet provider so there is no way to do what I want, the way I wanted to do it. That being said, I found a function that uses the Microsoft SQLDMO Object Library to use the SQL Bulkcopy function to do the same thing. The link below will take you right to it:

http://www.freevbcode.com/ShowCode.Asp?ID=1629

The only problem I had implementing it is that some of my column names turned out to be SQL keywords and even enclosing them in square brackets in the Schema.ini wouldn't get me around the error that caused, so I ended up renaming them and fixing everything that depended on those columns.