jarico75@yahoo.
New member
- Local time
- Yesterday, 19:00
- Joined
- Apr 2, 2007
- Messages
- 8
I have a text file with 261 fields that I need to import inot Access. Due to the limitation of 255 columns in an access table I need to import the fields into separate table. I have attempted to do this using the below code. I am receiving an error of "Syntax error in FROM clause" any help will be much appreciated.
Public Sub ImportFilePortion()
Dim cn As New ADODB.Connection
Dim sqlstring As String
Dim rs As New ADODB.Recordset
Dim thefile, thepath, thefilename, themdb As String
thepath = "J:\Data Conversion\Loan Boarding\A_Ben\"
thefilename = "thefile.txt"
thefile = "J:\Data Conversion\Loan Boarding\A_Ben\thefile.txt"
themdb = "C:\Documents and Settings\josepha\My Documents\"
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & thepath & ";" & _
"Extended Properties=""text;HDR=YES;FMT=Delimited"""
' rs.Open "select ARM_INITIAL_RATE_ADJ_PERIOD from thefile.txt", cn, adOpenStatic, adLockReadOnly, adCmdText
sqlstring = "Insert INTO [zWholeSaleData] " & _
"SELECT ARM_PAYMENT_OPTION_INDICATOR,ARM_PAYMENT_OPTION_RATE " & _
"FROM [Text;DATABASE=" & thepath & ";" & _
"HDR=YES;FMT=Delimited].[thefile.txt]"
cn.Execute sqlstring
End Sub
Public Sub ImportFilePortion()
Dim cn As New ADODB.Connection
Dim sqlstring As String
Dim rs As New ADODB.Recordset
Dim thefile, thepath, thefilename, themdb As String
thepath = "J:\Data Conversion\Loan Boarding\A_Ben\"
thefilename = "thefile.txt"
thefile = "J:\Data Conversion\Loan Boarding\A_Ben\thefile.txt"
themdb = "C:\Documents and Settings\josepha\My Documents\"
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & thepath & ";" & _
"Extended Properties=""text;HDR=YES;FMT=Delimited"""
' rs.Open "select ARM_INITIAL_RATE_ADJ_PERIOD from thefile.txt", cn, adOpenStatic, adLockReadOnly, adCmdText
sqlstring = "Insert INTO [zWholeSaleData] " & _
"SELECT ARM_PAYMENT_OPTION_INDICATOR,ARM_PAYMENT_OPTION_RATE " & _
"FROM [Text;DATABASE=" & thepath & ";" & _
"HDR=YES;FMT=Delimited].[thefile.txt]"
cn.Execute sqlstring
End Sub