Getting data into SQL Server?

I am using a code to get a text file with ~delimitted and then i am removing the ~, making a WS, DB, Table and RS to put all the seperated feilds into that access database file. Now this method only works with .mdb files (Microsoft Jet Engine) but now i want to implement the same routine for SQL Server database. Can anybody help me do it. thanx
Aasem
the code starts here:


Option Explicit
Function ReadFile(FileName As String) As String
' Read whole file.
Dim wlfn As Long
wlfn = FreeFile
Open FileName For Input As #wlfn
ReadFile = Input(LOF(wlfn), #wlfn)
Close wlfn
End Function
Private Sub Command1_Click()
ReDim FieldSplit(0) As String
ReDim countries(0) As String
ReDim States(0) As String
ReDim Flds(0) As String

'STEP:1
'Deleting old database, setting up WS, DB and TD and finally open Table

' Deleting old databse
If Len(Dir("C:\dest.mdb")) Then
   Ok = MsgBox("Delete old database", vbYesNo + vbExclamation, "Confirm Delete")
   If Ok = vbNo Then End
   Kill "c:\dest.mdb"
End If
Screen.MousePointer = vbHourglass
DoEvents
' Create a new database
Set WS = Workspaces(0)
' the syntax of this may change dependent what access version you have:
Set DB = WS.CreateDatabase("c:\dest.mdb", dbLangGeneral, DAO.DatabaseTypeEnum.dbVersion40)
' Create table
Set TD = DB.CreateTableDef("Addresses")
'Go to Create Fields Procedure
CreateFields

' Save new table spec.
DB.TableDefs.Append TD
Set TD = Nothing
DoEvents
' Open open the new table to receive data
Set RS = DB.OpenRecordset("Select * from Addresses", dbOpenDynaset)

'===========================================

'Step 2:
'Read Data from text files of countries and states and save
'into arrays after splitting
' read data
AllCountries = UCase(ReadFile("c:\countries.txt"))
countries = Split(AllCountries, vbCrLf)
AllStates = UCase(ReadFile("c:\states.txt"))
States = Split(AllStates, vbCrLf)
ReDim statedetails(UBound(States))

For fc = 0 To UBound(States)
   statedetails(fc) = Split(States(fc), ",")
   ' Make sure no silly spaces in data
   For cc = 0 To UBound(statedetails(fc))
       statedetails(fc)(cc) = Trim$(statedetails(fc)(cc))
       DoEvents
   Next cc
Next fc

'===========================================

'Step: 3 Getting US counties, US Cities and Cities of the
'world into respective arrays

'===========================================

' Step 4: Opening the original file, removing tildas
' and splitting all the fields

' Open Source data

wlfn = FreeFile
Open "C:\file_del2.txt" For Input As #wlfn
' keep reading until end of file
Recs = 0
Do While Not EOF(wlfn)
   Line Input #wlfn, l$
   Recs = Recs + 1
   If Len(l$) > 0 Then
      Flds = Split(l$, "~")
ReDim FieldSplit(0) As String
FieldSplit = Split(FieldNames, ",")
WS.BeginTrans
RS.AddNew

Dim I As Integer
For I = 0 To 29
    RS(FieldSplit(I)) = Flds(I)
Next
       ' Add the address fields together
       
       Address = ""
       For fc = 5 To 9
           If Len(Flds(fc)) > 0 Then
               If Len(Address) > 0 Then
                   Address = Address + " "
               End If
               Address = Address + Flds(fc)
           End If
       Next fc
'===========================================
'Steps for parsing the address field
'===========================================

RS.Update
WS.CommitTrans ' free locks

End If
   DoEvents
Loop

'===========================================

' 5  Close up

Close wlfn
RS.Close
DB.Close
WS.Close
Set RS = Nothing
Set DB = Nothing
Set WS = Nothing
Screen.MousePointer = vbDefault
MsgBox CStr(Recs) + " records imported!"
Exit Sub
'===========================================
End Sub
Public Sub CreateFields()
' Create fields
'ID
Set Fld = TD.CreateField("ID", DAO.DataTypeEnum.dbText, 40)
Fld.AllowZeroLength = True
TD.Fields.Append Fld

'Parent
Set Fld = TD.CreateField("Parent", DAO.DataTypeEnum.dbText, 40)
Fld.AllowZeroLength = True
TD.Fields.Append Fld

'Name
Set Fld = TD.CreateField("Name", DAO.DataTypeEnum.dbText, 40)
Fld.AllowZeroLength = True
TD.Fields.Append Fld

'DBA
Set Fld = TD.CreateField("DBA", DAO.DataTypeEnum.dbText, 40)
Fld.AllowZeroLength = True
TD.Fields.Append Fld

'SICode
Set Fld = TD.CreateField("SICode", DAO.DataTypeEnum.dbText, 40)
Fld.AllowZeroLength = True
TD.Fields.Append Fld

'Address
Set Fld = TD.CreateField("Address", DAO.DataTypeEnum.dbText, 200)
Fld.AllowZeroLength = True
TD.Fields.Append Fld

'City
Set Fld = TD.CreateField("City", DAO.DataTypeEnum.dbText, 40)
Fld.AllowZeroLength = True
TD.Fields.Append Fld

'State
Set Fld = TD.CreateField("State", DAO.DataTypeEnum.dbText, 40)
Fld.AllowZeroLength = True
TD.Fields.Append Fld

'Zip
Set Fld = TD.CreateField("Zip", DAO.DataTypeEnum.dbText, 40)
Fld.AllowZeroLength = True
TD.Fields.Append Fld

'Country
Set Fld = TD.CreateField("Country", DAO.DataTypeEnum.dbText, 40)
Fld.AllowZeroLength = True
TD.Fields.Append Fld

'Telephone
Set Fld = TD.CreateField("Telephone", DAO.DataTypeEnum.dbText, 40)
Fld.AllowZeroLength = True
TD.Fields.Append Fld

'Email
Set Fld = TD.CreateField("Email", DAO.DataTypeEnum.dbText, 40)
Fld.AllowZeroLength = True
TD.Fields.Append Fld

'TTN
Set Fld = TD.CreateField("TTN", DAO.DataTypeEnum.dbText, 40)
Fld.AllowZeroLength = True
TD.Fields.Append Fld

'PassportNo
Set Fld = TD.CreateField("PassportNo", DAO.DataTypeEnum.dbText, 40)
Fld.AllowZeroLength = True
TD.Fields.Append Fld

'DateofBirth
Set Fld = TD.CreateField("DOB", DAO.DataTypeEnum.dbText, 40)
Fld.AllowZeroLength = True
TD.Fields.Append Fld

'Type of Business
Set Fld = TD.CreateField("TOB", DAO.DataTypeEnum.dbText, 40)
Fld.AllowZeroLength = True
TD.Fields.Append Fld


'Source of Funds
Set Fld = TD.CreateField("SOF", DAO.DataTypeEnum.dbText, 40)
Fld.AllowZeroLength = True
TD.Fields.Append Fld

'Acct Offrs
Set Fld = TD.CreateField("AcctOffr", DAO.DataTypeEnum.dbText, 40)
Fld.AllowZeroLength = True
TD.Fields.Append Fld

'AcctOffrTel
Set Fld = TD.CreateField("AcctOffrTel", DAO.DataTypeEnum.dbText, 40)
Fld.AllowZeroLength = True
TD.Fields.Append Fld

'AcctOffrEmail
Set Fld = TD.CreateField("AcctOffrEmail", DAO.DataTypeEnum.dbText, 40)
Fld.AllowZeroLength = True
TD.Fields.Append Fld

'CompOffrs
Set Fld = TD.CreateField("CompOffrs", DAO.DataTypeEnum.dbText, 40)
Fld.AllowZeroLength = True
TD.Fields.Append Fld

'CompOffrTel
Set Fld = TD.CreateField("CompOffrTel", DAO.DataTypeEnum.dbText, 40)
Fld.AllowZeroLength = True
TD.Fields.Append Fld

'CompOffrEmail
Set Fld = TD.CreateField("CompOffrEmail", DAO.DataTypeEnum.dbText, 40)
Fld.AllowZeroLength = True
TD.Fields.Append Fld

'List10
Set Fld = TD.CreateField("List10", DAO.DataTypeEnum.dbText, 40)
Fld.AllowZeroLength = True
TD.Fields.Append Fld

'Notes
Set Fld = TD.CreateField("Notes", DAO.DataTypeEnum.dbText, 40)
Fld.AllowZeroLength = True
TD.Fields.Append Fld

'ExampStamp
Set Fld = TD.CreateField("ExampStamp", DAO.DataTypeEnum.dbText, 40)
Fld.AllowZeroLength = True
TD.Fields.Append Fld

'OwnerBranch
Set Fld = TD.CreateField("OwnerBranch", DAO.DataTypeEnum.dbText, 40)
Fld.AllowZeroLength = True
TD.Fields.Append Fld

'OwnerDept
Set Fld = TD.CreateField("OwnerDept", DAO.DataTypeEnum.dbText, 40)
Fld.AllowZeroLength = True
TD.Fields.Append Fld

'OwnerOp
Set Fld = TD.CreateField("OwnerOp", DAO.DataTypeEnum.dbText, 40)
Fld.AllowZeroLength = True
TD.Fields.Append Fld

'RiskClass
Set Fld = TD.CreateField("RiskClass", DAO.DataTypeEnum.dbText, 40)
Fld.AllowZeroLength = True
TD.Fields.Append Fld

' Confidence Factor
Set Fld = TD.CreateField("Confidence", DAO.DataTypeEnum.dbLong)
TD.Fields.Append Fld

Set Fld = Nothing
End Sub
Start Free Trial