, Delimited Text File To Access Db

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

#1 petersoj

New D.I.C Head

Group: Members
Posts: 12
Joined: 17-May 09

Dream Kudos: 0

Post icon Posted 31 May 2009 - 06:03 PM

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,

Was This Post Helpful? 0

Register for Free to Make These Ads Go Away!

#2 vb5prgrmr

D.I.C Addict

Group: Members
Posts: 645
Joined: 21-March 09

Dream Kudos: 0

Posted 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

Was This Post Helpful? 0

#3 Ghostwolf

New D.I.C Head

Group: Members
Posts: 20
Joined: 01-May 09

Who Cares Favorite Browser Favorite Processor Favorite Gaming Platform Your Car
Dream Kudos: 0

Posted 01 June 2009 - 06:46 AM

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:

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

Was This Post Helpful? 0

#4 petersoj

New D.I.C Head

Group: Members
Posts: 12
Joined: 17-May 09

Dream Kudos: 0

Posted 03 June 2009 - 05:36 AM

vb5prgrmr, on 31 May, 2009 - 08:15 PM, said:

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

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.

Was This Post Helpful? 0

#5 vb5prgrmr

D.I.C Addict

Group: Members
Posts: 645
Joined: 21-March 09

Dream Kudos: 0

Posted 03 June 2009 - 09:29 PM

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

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

Was This Post Helpful? 0

#6 born2c0de

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

Group: Moderators
Posts: 4,566
Joined: 26-November 04

Windows Favorite Browser Favorite Processor Favorite Gaming Platform Your Car
Dream Kudos: 2825
Expert In: J2ME, 80x86 Assembly, C/C++, VB6, VB.NET, C#, J2SE, Win32 API, Reversing

Posted 03 June 2009 - 11:35 PM

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.

Was This Post Helpful? 0

#7 petersoj

New D.I.C Head

Group: Members
Posts: 12
Joined: 17-May 09

Dream Kudos: 0

Posted 04 June 2009 - 03:44 AM

vb5prgrmr, on 3 Jun, 2009 - 09:29 PM, said:

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

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.

Was This Post Helpful? 0

#8 vb5prgrmr

D.I.C Addict

Group: Members
Posts: 645
Joined: 21-March 09

Dream Kudos: 0

Posted 04 June 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

Was This Post Helpful? 0

#9 petersoj

New D.I.C Head

Group: Members
Posts: 12
Joined: 17-May 09

Dream Kudos: 0

Posted 05 June 2009 - 05:29 AM

vb5prgrmr, on 4 Jun, 2009 - 11:45 PM, said:

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,

Was This Post Helpful? 0