inserting text file into mdb not working... help please

This has not been my day:-(... I am having problems updating an access table (Points) with text data that was pulled in (Open file dialog 1). Below is the code... I have gotten help with this part of the code but am once again, neck deep in the mud. Thanks for the help

Code:
    Private Sub btnConvert_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnConvert.Click
        Dim cn As New ADODB.Connection
        'Dim PathtoTextFile As String
        'Dim PathtoMDB As String
        Dim sFilePath2 As String
        sFilePath2 = OpenFileDialog2.FileName
        'PathtoTextFile = "& sFilePath2 """
        'PathtoMDB = "& sFilePath2 """
        Dim da As New OleDbDataAdapter
        Dim ds As New DataSet
        Dim sAppPath As String
        Dim dbPath As String
        Dim sDBName As String
        sAppPath = System.Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData)
        sDBName = "//PntCon_V2.mdb"
        dbPath = "" & sAppPath & sDBName & ""
        'cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
        '          "Data Source=" & sAppPath & sDBName & "")

        'cn.Execute("INSERT INTO Points SELECT * FROM " & _
        '             "[Text;Database=" & sFilePath2 & ";HDR=YES],")

        'cn.Close()
        'Private Sub DefineAdapter()
        Dim sFilePath1 As String
        sFilePath1 = OpenFileDialog1.FileName
        'Dim dbPath As String
        'Dim sDBName As String
        'Dim sAppPath As String
        sAppPath = System.Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData)
        sDBName = "//PntCon_V2.mdb"
        dbPath = "" & sAppPath & sDBName & ""
        With da
            .SelectCommand = New OleDb.OleDbCommand
            With .SelectCommand
                .Connection = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sFilePath1 & ";Extended Properties=text")  'either define sfilepath1 prior to calling this procedure or create the connection object separately
                .CommandText = "SELECT * FROM " & sFilePath1 & ""
            End With

            .InsertCommand = New OleDb.OleDbCommand
            With .InsertCommand
                .Connection = New OleDb.OleDbConnection(("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sAppPath & sDBName & ""))
                .CommandText = "INSERT INTO Points(Field1,field2,field3,field4,field5) VALUES (ds.f1, ds.f2, ds.f3, ds.f4, ds.f5)"
                'With .Parameters
                '    .Add("Field1", OleDbType.Double, 20, "f1") ' field 1 is the column in the points table that i want the f1 values to go to
                '    .Add("field2", OleDbType.Double, 20, "f2") ' field 2 is the column in the points table that i want the f2 values to go to
                '    .Add("field3", OleDbType.Double, 20, "f3")
                '    .Add("field4", OleDbType.Double, 20, "f2")
                '    .Add("field5", OleDbType.VarChar, 25, "f3")
                '    'repeat for other 4 columns
                'End With
            End With

            .AcceptChangesDuringFill = False 'maintains their rowstate as new so that they can immediately inserted with the update method
            .TableMappings.Add("ds", "Points") 'Not sure about here
            da.Fill(ds)
            ' ''define connection for .insertcommand
            ' ''create the access database
            da.Update(ds)
        End With
JohnEddinsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JohnEddinsAuthor Commented:
Thanks for looking but the above links are not helping me out a much...I am still having problems, it seems from everything I have read that this should be easy, but I must be even bumber then I thought cause i can not get this thing to work!
0
JohnEddinsAuthor Commented:
Below is my code: I am getting an error at the cn.execute line... Any thoguhts?    

Private Sub btnConvert_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnConvert.Click
        Dim cn As New ADODB.Connection
        'Dim PathtoTextFile As String
        'Dim PathtoMDB As String
        Dim sFilePath2 As String
        sFilePath2 = OpenFileDialog2.FileName
        'PathtoTextFile = "& sFilePath2 """
        'PathtoMDB = "& sFilePath2 """
        Dim da As New OleDbDataAdapter
        Dim ds As New DataSet
        Dim sAppPath As String
        Dim dbPath As String
        Dim sDBName As String
        sAppPath = System.Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData)
        sDBName = "//PntCon_V2.mdb"
        dbPath = "" & sAppPath & sDBName & ""
        cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                  "Data Source=" & sAppPath & sDBName & "")

        cn.Execute("INSERT INTO Points SELECT * FROM " & _
                     "[Text;Database=" & sFilePath2 & ";HDR=YES],")

        cn.Close()
        'Private Sub DefineAdapter()
        Dim sFilePath1 As String
        sFilePath1 = OpenFileDialog1.FileName
        sAppPath = System.Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData)
        sDBName = "//PntCon_V2.mdb"
        dbPath = "" & sAppPath & sDBName & ""
        With da
            .SelectCommand = New OleDb.OleDbCommand
            With .SelectCommand
                .Connection = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sFilePath1 & ";Extended Properties=text")  'either define sfilepath1 prior to calling this procedure or create the connection object separately
                .CommandText = "SELECT * FROM " & sFilePath1 & ""
            End With

            .InsertCommand = New OleDb.OleDbCommand
            With .InsertCommand
                .Connection = New OleDb.OleDbConnection(("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sAppPath & sDBName & ""))
                .CommandText = "INSERT INTO Points(F1,f2,f3,f4,'f5') VALUES (ds.f1, ds.f2, ds.f3, ds.f4, 'ds.f5')"
                With .Parameters
                    .Add("F1", OleDbType.Double, 20, "f1") ' field 1 is the column in the points table that i want the f1 values to go to
                    .Add("F2", OleDbType.Double, 20, "f2") ' field 2 is the column in the points table that i want the f2 values to go to
                    .Add("F3", OleDbType.Double, 20, "f3")
                    .Add("F4", OleDbType.Double, 20, "f2")
                    .Add("F5", OleDbType.VarChar, 25, "f3")
                    '    'repeat for other 4 columns
                    'End With
                End With
                da.AcceptChangesDuringFill = False 'maintains their rowstate as new so that they can immediately inserted with the update method
                da.TableMappings.Add("ds", "Points") 'Not sure about here
                da.Fill(ds)
                ' ''define connection for .insertcommand
                ' ''create the access database
                da.Update(ds)
            End With
        End With

    End Sub
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

JRossi1Commented:
Knowing what the error message your getting would help.

Thanks.
0
JRossi1Commented:
Try this:

Sub ImportTextToAccess()

      Dim AccessConn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=d:\My Documents\db1.mdb")

      AccessConn.Open()

      'New table - This will create the table if it doesn't exist
      Dim AccessCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO [tbl1] FROM " & _
      "[Text;DATABASE=d:\My Documents\TextFiles].[CSV.txt]", AccessConn)

      'Existing table
      Dim AccessCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO [urTable] (F1, F2, F3, F4,
      F5)  SELECT F1, F2, F3, F4, F5 FROM [Text;DATABASE=d:\My Documents\TextFiles;].[CSV.txt]",  AccessConn)

      AccessCommand.ExecuteNonQuery()
      AccessConn.Close()

End Function


0
JohnEddinsAuthor Commented:
Sorry for the delay....
I modified what you gave me above to look like this.... and it generated an error message at the accessCommand.executeNonQuery Line: Error says there is a problem in the from statment... Abyhow, below is wha ti chang ewhat you provided to look like, and thanks for your time with this::
        Dim sAppPath As String
        Dim dbPath As String
        Dim sDBName As String
        Dim sfilepath1 As String
        sAppPath = System.Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData)
        sDBName = "//PntCon_V2.mdb"
        dbPath = "" & sAppPath & sDBName & ""
        Dim AccessConn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & sAppPath & sDBName & "")

        AccessConn.Open()

        'New table - This will create the table if it doesn't exist
        'Dim AccessCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO [tbl1] FROM " & _
        '"[Text;DATABASE=" & sfilepath1 & "].[CSV.txt]", AccessConn)
  ' Above line commented out beacuse i create the database and the table on the form load event. So I know its there
        'Existing table
        Dim AccessCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO [Points] (F1, F2, F3, F4, F5)  SELECT F1, F2, F3, F4, F5 FROM [Text;DATABASE=" & sfilepath1 & "", AccessConn)

        AccessCommand.ExecuteNonQuery()
        AccessConn.Close()
    End Sub
0
JRossi1Commented:
Try hardcoding the path to the text file:

 Dim AccessCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO [Points] (F1, F2, F3, F4, F5)  SELECT F1, F2, F3, F4, F5 FROM [Text;DATABASE=C:\MytextFile.txt", AccessConn)

Want to see if the sFilePath variable is being correctly populated.
0
JohnEddinsAuthor Commented:
I was traveling last night, but Im back now... i just gave what you suggest a shot 9hasrd code the text path,a dn I am sitll getting an error at the AccessCommand.Excute string for an error in the form clause...
0
Bob LearnedCommented:
What exception are you getting now?

Bob
0
JRossi1Commented:
I was able to get this to work by changing the SQL string to this:

 Dim AccessCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO TableName (F1, F2, F3 ) SELECT * FROM [Book1.txt] IN 'C:\Data'", AccessConn)

I think you have to separate the file name from the path.  
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JohnEddinsAuthor Commented:
JRoss1
That got it... thanks for both your help and time!
John
0
JRossi1Commented:
No problem.  That was a bit tricky.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.