import text files to mdb with vb.net

Hello,
I need to import text files to an access database and cant seem to get it right, I am no great programmer by any strethch of the imagination adn lots of topics like this i see use a adodb.connection and adodb.recordset calls that are not working in my vb.net app.. thanks for the help!
John
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.

gsansomCommented:
It's hard to help without seeing a listing of the code you have so far.  There are plenty of resources out there for this sort of stuff, and presumably you've already looked at the examples out there, so if you post snippets of your code up we'll be able to see where you are having problems.
0
Leo EikelmanDirector, IT and Business DevelopmentCommented:
possibly this code here

'VB:--------------------------------------------------------------------------------

strSrcFile = "MyData.TXT"

Set db = New ADODB.Connection
db.CursorLocation = adUseClient
db.Open "Provider=Microsoft.Jet.OLEDB.4.0;data source=" & _
strMainPath & ";Extended Properties=TEXT"


StrSQL = "INSERT INTO " & tblName & " (tblNumbers, tblNames) IN """ & strMainDb & """ " & _
"select txtNumbers, txtNames " & _
"from [" & strSrcFile & "];"

db.Execute StrSQL

Leo
0
JohnEddinsAuthor Commented:
I am getting a "Connection is ambiguouse in namespace ADODB error around the adodb.connection (same happens around ADODB.Recordset...
I have encluded some code below as suggested...

    Private Sub btnCLine_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCLine.Click
        OpenFileDialog2.Title = "Select C-Line File (Must have file extension .txt)"
        OpenFileDialog2.Filter = ".txt file (*.txt)|*.txt"
        OpenFileDialog2.ShowDialog()
        Dim ConnectionString As String
        Dim sFilePath2 As String
        sFilePath2 = OpenFileDialog2.FileName
        If sFilePath2 = "" Then Exit Sub
        txtCline.Text = sFilePath2
        ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=c:\" & ";" & "Extended Properties=""Text;HDR=No;"""

        Dim TextConnection As New System.Data.OleDb.OleDbConnection(ConnectionString)
        TextConnection.Open()


        Dim da As New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM " & sFilePath2 & "", TextConnection)
        ' Try
        Dim ds As New DataSet("TextFiles")
        da.Fill(ds, "ta")
        DataGrid2.DataSource = ds.Tables(0).DefaultView
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Leo EikelmanDirector, IT and Business DevelopmentCommented:
I don't understand.  You said you are getting ambiguouse error's around ADODB connection and record set (which actually means that there are two libraries that have the same name for an object and it doesn't know which one to use.  Never got this error before when using ADODB)

In your code thoughm u are using System.Data.OleDb.OleDbConnection and not ADODB.connection

Leo
0
JohnEddinsAuthor Commented:
I actually have both as import statments, do think this could be the problem?

Option Explicit On
Imports System.Data.odbc
Imports ADOX
Imports ADODB
Imports System.Data
Imports System.Data.OleDb
Imports System
Imports System.IO
Imports System.Collections
Imports System.Text
Imports VB = Microsoft.VisualBasic
Imports System.Math
0
Leo EikelmanDirector, IT and Business DevelopmentCommented:
yes that is the problem. : )

Leo
0
JohnEddinsAuthor Commented:
I changed over to the oledb approach since I was getting nowhere with the ado approach due to the adodb.recordset and .connection ambiguouse errors... any thoughts
0
JohnEddinsAuthor Commented:
How should I correct it?
0
Leo EikelmanDirector, IT and Business DevelopmentCommented:
did u try using this code

strSrcFile = "MyData.TXT"

Set db = New ADODB.Connection
db.CursorLocation = adUseClient
db.Open "Provider=Microsoft.Jet.OLEDB.4.0;data source=" & _
strMainPath & ";Extended Properties=TEXT"


StrSQL = "INSERT INTO " & tblName & " (tblNumbers, tblNames) IN """ & strMainDb & """ " & _
"select txtNumbers, txtNames " & _
"from [" & strSrcFile & "];"

db.Execute StrSQL

Instead of the way u are doing it

Private Sub btnCLine_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCLine.Click
        OpenFileDialog2.Title = "Select C-Line File (Must have file extension .txt)"
        OpenFileDialog2.Filter = ".txt file (*.txt)|*.txt"
        OpenFileDialog2.ShowDialog()
        Dim ConnectionString As String
        Dim sFilePath2 As String
        sFilePath2 = OpenFileDialog2.FileName
        If sFilePath2 = "" Then Exit Sub
        txtCline.Text = sFilePath2
        ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=c:\" & ";" & "Extended Properties=""Text;HDR=No;"""

        Dim TextConnection As New System.Data.OleDb.OleDbConnection(ConnectionString)
        TextConnection.Open()


        Dim da As New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM " & sFilePath2 & "", TextConnection)
        ' Try
        Dim ds As New DataSet("TextFiles")
        da.Fill(ds, "ta")
        DataGrid2.DataSource = ds.Tables(0).DefaultView


If you want to use ADODB then I think u need to get rid of

Imports System.Data.odbc
Imports System.Data
Imports System.Data.OleDb

from imports


Leo
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:
what shoudl I dim db as in your code (and the points on this one just wnet up) I have actually learned a good bit from you on this one...
0
Leo EikelmanDirector, IT and Business DevelopmentCommented:
I need to go to the airport, I will get back to you later tonight
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
Programming

From novice to tech pro — start learning today.