VB School Assignment? Project Due Tomorrow? Chat LIVE With A Programming Expert!

Welcome to Dream.In.Code
Become a VB Expert!

Join 300,337 VB Programmers for FREE! Get instant access to thousands of VB experts, tutorials, code snippets, and more! There are 1,665 people online right now. Registration is fast and FREE... Join Now!




, delimited text file to access db

 

, delimited text file to access db, Put text file data into a vb created access db

petersoj

31 May, 2009 - 06:03 PM
Post #1

New D.I.C Head
*

Joined: 17 May, 2009
Posts: 12

I need to take a text file with data separated by commas and convert the files into a access 2000 database. I have not been able to find anything close enough to what I need.

Data looks like : 19990101,111,AZ,e

Thanks,


User is offlineProfile CardPM
+Quote Post


vb5prgrmr

RE: , Delimited Text File To Access Db

31 May, 2009 - 08:15 PM
Post #2

D.I.C Regular
***

Joined: 21 Mar, 2009
Posts: 459



Thanked: 30 times
My Contributions
In VB's help, on the index tab, look up the following...

Open Statement
FreeFile Function
Line Input
Split Function

Then search this site or look up at the top of this forum if I remember correctly for the tutorual on ADO.

Good Luck


User is offlineProfile CardPM
+Quote Post

Ghostwolf

RE: , Delimited Text File To Access Db

1 Jun, 2009 - 06:46 AM
Post #3

New D.I.C Head
*

Joined: 1 May, 2009
Posts: 20



Thanked: 2 times
My Contributions
If you're using VBA inside Access, another option might be to use a schema.ini file to define your incoming data.

The ini file layout is pretty easy:
CODE

     [TMPFILE.TXT]
     ColNameHeader=FALSE
     CharacterSet=OEM
     Format=CSVDelimited
     Col1=Program Char
     Col2=RunDate Char
     Col3=County Char
     Col4=CountyName Char
     Col5=ForDate Char


This file says that when TMPFILE.TXT is encountered, there will be no header row in the incoming data, which is CSVDelimited, and it identifies the five columns of incoming data.


User is offlineProfile CardPM
+Quote Post

petersoj

RE: , Delimited Text File To Access Db

3 Jun, 2009 - 05:36 AM
Post #4

New D.I.C Head
*

Joined: 17 May, 2009
Posts: 12

QUOTE(vb5prgrmr @ 31 May, 2009 - 08:15 PM) *

In VB's help, on the index tab, look up the following...

Open Statement
FreeFile Function
Line Input
Split Function

Then search this site or look up at the top of this forum if I remember correctly for the tutorual on ADO.

Good Luck


I tried the following code, but ended up gettign this error "Multiple-step operation genated errors. Check each status value"

CODE

Private Sub Command1_Click()
  Dim conConnection As New ADODB.Connection
  Dim cmdCommand As New ADODB.Command
  Dim rstRecordSet As New ADODB.Recordset
  Dim AccPath As String
  
  AccPath = App.Path & "\AccessFiles\"
  
   conConnection.ConnectionString "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _App.Path & "\" & "database.mdb;Mode=Read|Write"
conConnection.CursorLocation = adUseClient

   conConnection.Open

  With cmdCommand
    .ActiveConnection = conConnection
    .CommandText = "SELECT * FROM tabTestTable;"
    .CommandType = adCmdText
  End With

    With rstRecordSet
    .CursorType = adOpenStatic
    .CursorLocation = adUseClient
    .LockType = adLockOptimistic
    .Open cmdCommand
  End With

  Dim Acc As String
  Dim AccDate
  Dim AccNum
  Dim AccSt
  Dim AccLot
  Open AccPath & "Access.txt" For Input As #1
    Do While Not EOF(1)
        Line Input #1, Acc
        AccDate = Mid$(Acc, 1, 8)
        AccNum = Mid$(Acc, 10, 3)
        AccSt = Mid$(Acc, 14, 2)
        AccLot = Mid$(Acc, 17, 1)
        
        With rstRecordSet
          .AddNew
          .Fields(0) = AccDate
          .Fields(1) = AccNum
          .Fields(2) = AccSt
          .Fields(3) = AccLot
        End With
    Loop
    Close #1
  conConnection.Close

  Set conConnection = Nothing
  Set cmdCommand = Nothing
  Set rstRecordSet = Nothing
End Sub


I am stuck and not sure if I am going i the right direction here.

MOD EDIT: Code Tags Added.
User is offlineProfile CardPM
+Quote Post

vb5prgrmr

RE: , Delimited Text File To Access Db

3 Jun, 2009 - 09:29 PM
Post #5

D.I.C Regular
***

Joined: 21 Mar, 2009
Posts: 459



Thanked: 30 times
My Contributions
Instead of .addnew/.update method. I would suggest a command object with an insert string...
CODE

strSQL = "INSERT INTO tableName(field1name,field2name) VALUES ('" & textvalue & "'," & numericvalue & ")"
conn.execute strSQL


However, I just noticed that you do not have an .Update statement in your loop. So add it after you set the field=value part and test.

Good Luck
User is offlineProfile CardPM
+Quote Post

born2c0de

RE: , Delimited Text File To Access Db

3 Jun, 2009 - 11:35 PM
Post #6

printf("I'm a %XR",195936478);
Group Icon

Joined: 26 Nov, 2004
Posts: 4,542



Thanked: 98 times
Dream Kudos: 2825
Expert In: J2ME, 80x86 Assembly, C/C++, VB6, VB.NET, C#, J2SE, Win32 API, Reversing

My Contributions
Read this tutorial to learn how to deal with file handling in VB6 and once you read it, you'll be able to handle and split text from files as you require.
User is offlineProfile CardPM
+Quote Post

petersoj

RE: , Delimited Text File To Access Db

4 Jun, 2009 - 03:44 AM
Post #7

New D.I.C Head
*

Joined: 17 May, 2009
Posts: 12

QUOTE(vb5prgrmr @ 3 Jun, 2009 - 09:29 PM) *

Instead of .addnew/.update method. I would suggest a command object with an insert string...
CODE

strSQL = "INSERT INTO tableName(field1name,field2name) VALUES ('" & textvalue & "'," & numericvalue & ")"
conn.execute strSQL


However, I just noticed that you do not have an .Update statement in your loop. So add it after you set the field=value part and test.

Good Luck


I assume you wanted me to try the .Update first? ( did not work). I am confused as to where to put the insert string in the code. Do I replace the .AddNew/.Update section? the values part of the Insert string is not working for me. I keep getting a compile errror (Expected expression). So I am clear on this, where you have textvalue and numericvalue that would be my variables:
Dim Acc As String
Dim AccDate As String
Dim AccNum As String
Dim AccSt As String
Dim AccLot As String
Incidently, I forgot to delcare these properly when I first started. See code at the beginning.

Thanks for continuing to help.
User is offlineProfile CardPM
+Quote Post

vb5prgrmr

RE: , Delimited Text File To Access Db

4 Jun, 2009 - 11:45 PM
Post #8

D.I.C Regular
***

Joined: 21 Mar, 2009
Posts: 459



Thanked: 30 times
My Contributions
>Do I replace the .AddNew/.Update section?

Yes.

strSQL = "INSERT INTO YourTableName(YourFieldName) VALUES ('" & variable & "')"

Where YourFileName is you will need to list the fields of the database seperated by a comma (Field1, Field2) and where variable is "('" & var1 & "," & var2 & "')"

Good Luck


User is offlineProfile CardPM
+Quote Post

petersoj

RE: , Delimited Text File To Access Db

5 Jun, 2009 - 05:29 AM
Post #9

New D.I.C Head
*

Joined: 17 May, 2009
Posts: 12

QUOTE(vb5prgrmr @ 4 Jun, 2009 - 11:45 PM) *

>Do I replace the .AddNew/.Update section?

Yes.

strSQL = "INSERT INTO YourTableName(YourFieldName) VALUES ('" & variable & "')"

Where YourFileName is you will need to list the fields of the database seperated by a comma (Field1, Field2) and where variable is "('" & var1 & "," & var2 & "')"

Good Luck


Here is what I have at this point:
Private Sub Command1_Click()

Dim conConnection As New ADODB.Connection
Dim cmdCommand As New ADODB.Command

Dim AccPath As String

AccPath = App.Path & "\AccessFiles\"

conConnection.ConnectionString "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\" & "database.mdb;Mode=Read|Write"
conConnection.CursorLocation = adUseClient

conConnection.Open

With cmdCommand
.ActiveConnection = conConnection
.CommandText = "SELECT * FROM tabTestTable;"
.CommandType = adCmdText
End With

Dim Acc As String
Dim AccDate
Dim AccNum
Dim AccSt
Dim AccLot
Open AccPath & "Access.txt" For Input As #1
Do While Not EOF(1)
Line Input #1, Acc
AccDate = Mid$(Acc, 1, 8)
AccNum = Mid$(Acc, 10, 3)
AccSt = Mid$(Acc, 14, 2)
AccLot = Mid$(Acc, 17, 1)
strSQL = "INSERT INTO tabTestTable(Date,LotNum,State,LotType) VALUES ('" & AccDate & "," & AccNum & "," & AccSt & "," & AccLot & "')"
con.Execute strSQL
Loop
Close #1
conConnection.Close

Set conConnection = Nothing
Set cmdCommand = Nothing
End Sub

After I run it I get this error "Invalid use of property" and the code highlights the conConnection.ConnectionString. I did not find anything out on the internet to give me a hint. Anything you see or know could be causing the error?

Thanks,

User is offlineProfile CardPM
+Quote Post

vb5prgrmr

RE: , Delimited Text File To Access Db

5 Jun, 2009 - 10:24 PM
Post #10

D.I.C Regular
***

Joined: 21 Mar, 2009
Posts: 459



Thanked: 30 times
My Contributions
remove...

Mode=Read|Write

and see...

http://www.connectionstrings.com/access#85

Good Luck


User is offlineProfile CardPM
+Quote Post

Fast ReplyReply to this topicStart new topic

Time is now: 11/7/09 05:10PM

Live VB Help!

Be Social

Dream.In.Code RSS Feed Dream.In.Code LinkedIn Group Follow Us On Twitter Fan Us On Facebook

VB Tutorials

Reference Sheets

VB Snippets

DIC Chatroom

Bye Bye Ads

Monthly Drawing

Thumb Drive

Top Contributors

Top 10 Kudos This Month