• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 670
  • Last Modified:

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
0
JohnEddins
Asked:
JohnEddins
  • 5
  • 5
1 Solution
 
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now