Import CSV file to SQL Server Database

[blinton25]

Hello,

How are you today?

I have an Access application which uses linked tables on
SQL Server 2K. I have the task of importing data from
a .csv file to a database table.
I currently accomplish this using the following:



Public Function AppendFile2()
Dim FileString As String
Dim Fpath, Fname As String
Dim tbl As TableDef
Dim tdfLinked As TableDef
Dim fnstart As Integer

Dim dbs As Database

Set dbs = CurrentDb()

FileString = FileOpen()

If FileString = "" Then
Exit Function
End If

fnstart = InStr(ReverseString(FileString), "\")

Fpath = Left$(FileString, Len(FileString) - fnstart)
Fname = Right$(FileString, fnstart - 1)

On Error Resume Next
dbs.TableDefs.Delete "Input Table Temp"
On Error GoTo 0

Set tdfLinked = dbs.CreateTableDef("Input Table Temp")

tdfLinked.Connect = "Text;DATABASE=" & Fpath
tdfLinked.SourceTableName = Fname
dbs.TableDefs.Append tdfLinked

On Error GoTo 0

DoCmd.RunMacro "Append Input Table Macro1"
Exit Function



Once the file is imported I then append the contents to an
existing table:



INSERT INTO [Input table] ( C_NUMBER, F_NUMBER )
SELECT [Input Table Temp].CARD, [Input Table Temp].F_NO
FROM [Input Table Temp];



I am now converting my queries to passthrough ones to take
advantage of the power of my database server, but since
[Input Table Temp] is a temporary table in my Access
database, my append query is not working.

I know Bulk Copy is an option, but I want my users to be
able to carry out a procedure similar to what they do now,
namely select a file, hit ok and it is imported into the
database table.

Any ideas how this can be done?