Port -> Access to VB (import and parse multiple text files)

[zenenigma]

I have a program in Access that will currently:

1) Import all text files (logs) from a directory into tables
2) Strip the file name for the date and put that date into a field.
3) Do an Instr() function on all tables to determine if they have a certain value. If they do, move those records with the Instr() value into one table. Discard all other transactions.
4) Have all transactions with that Instr() value in one place, as to easily do calculations on it.

My problem is, I'd like to share this program with others, but they may not have MS Access. How would one do such a thing in VB?

I am able to search the directory and put each text file name individually into a variable (using a loop where I would call the import function after each file)

I'm fuzzy on the import and how I would put them into recordsets. There could be anywhere between 1 - 200 of these log files, max file size being around 1mb.

Any help would be appreciated.

I've tried a few import techniques,

 

[ChiHappens]

you should be able to distribute the mdb file and if you use package and deployment wizard, it will know to include the adodb objects.

Chi Happens
"When you know and respect your own inner nature, you know where you belong. You also know where you don't belong" - Benjamin Hoff

 

[zenenigma]

you should be able to distribute the mdb file and if you use package and deployment wizard, it will know to include the adodb objects.

------------------------------------

The package & deployment wizard must be in the newer versions of Access. I'm currently using Access 97 and can't find anything on that feature.

 

[johnwm]

P&DW is a feature of VB - use it on the ported version after you have compiled the VB to an exe

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[CCLINT]

This is the VB 5 & 6 forum, and not the MS ACCESS forum.
You need to go over there for questions like this.

Anyways, I believe you will need MOD (Microsoft Office Developers Kit) in order to distribute the MS ACCESS runtime files, and it's not free...

 

[johnwm]

I assumed from the thread title that we were talking about porting the app to VB....

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[zenenigma]

johnwm is correct. I'm trying to create the program in Visual Basic. I have a better knowledge of MS Access, so my first attempt was created there, to see which features I would need.

Since I haven't received any comments about actually programming this in Visual Basic, I assume it would be too difficult.

I responded to Chi because it was the only response I received, and I thought it might be a good backup plan.

-ZE

 

[johnwm]

If you're not familiar with data access techniques in VB, I suggest you run through the FAQ section in this forum. There are quite a number giving starter examples. Look in FAQs for Database, Access and ADO

If you finish up with a more specific query, post back here.

See also faq222-2244 for guidance on getting better answers by asking better questions

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[ChiHappens]

My answer was about VB, i assumed that you were already building it in VB (from your first post) so i did not go into detail (I thought you meant that you did not know how to distribute an mdb file).

Here is how you do ADO:

Dim CD As ADODB.Connection
Dim RS As ADODB.RecordSet
Dim SQL As String

Set CN = CreateObject("ADODB.Connection&quot
Set RS = CreateObject("ADODB.RecordSet&quot

CN.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyDatabase.mdb;Persist Security Info=False"
SQL = "SELECT * FROM MyTable"
RS.Open SQL, CN
Do While RS.EOF = False
' Do something with the records using RS("FieldName&quot.Value
RS.MoveNext
Loop
RS.Close

CN.CLose

Set CN = Nothing
Set RS = Nothing

(Use this method instead of Dim CN as New ADODB.Connection because that can cause memory leaks as per microsoft's msdn site)

To open a file you do this:
Dim FSO as Object
Dim FP as Long

Set FSO = CreateObject("Scripting.FileSystemObject&quot
If FSO.FileExists("C:\MyFile.dat&quot Then ' This can be ANY file
FP = FreeFile() ' get free file handle
Open "C:\MyFile.dat" For Input As #Fp
' THEN TWO WAYS TO GET FILE DATA:
' 1)
FileBody = Input(EOF(FP),#FP) ' get the whole thing
' 2)
Do While EOF(FP) = False
Line Input #Fp, FileLine
FileBody = FileBody & FileLine & vbCrlf
Loop

' Both use this next:
Close #FP
Else
MsgBox "File Not FOund.", vbOkOnly, "Error"
End If

' then you can parse the filebody and use
SQL = "INSERT INTO MyTable (FieldName1,FieldName2...) VALUES ('" & Value1 & "','" & Value2 & "',...)"

' and Use
CN.Execute SQL
' to put it into the db

Hope this helps you

Chi Happens



Chi Happens
"When you know and respect your own inner nature, you know where you belong. You also know where you don't belong" - Benjamin Hoff

 

[ChiHappens]

Oh one more thing for CCLINT,
You don't need anything to distribute an access database file, especially if you create an application that uses ADO for accessing it (since that is included with windows and downloadable for free from microsoft)

When you create a program that accesses an access database file, you DO NOT need to distribute ANY copyrighted run-time files.

Chi Happens
"When you know and respect your own inner nature, you know where you belong. You also know where you don't belong" - Benjamin Hoff

 

[ChiHappens]

One last thing...i made a typo:

The line that reads:

Dim CD as ADODB.Connection

Should say

Dim CN As ADODB.Connection.



Chi Happens
"When you know and respect your own inner nature, you know where you belong. You also know where you don't belong" - Benjamin Hoff

 

[ChiHappens]

Jeezus I'm full of mistakes tonight.

Always remember, and never forget to delete your objects you create.

So when you type

Set objThis = CreateObject("whatever&quot

you need to type

Set objThis = Nothing

when you no longer need it.

Therefore, you need to include one last line within the file access stuff i wrote:

Set FSO = Nothing

Ok, I am done...No really...any other bugs someone else can fix. lol

BTW, if you need additional help, you can contact me directly maslow_malo@hotmail.com (go ahead bots...spam me, like I care)

Later,

Chi Happens
"When you know and respect your own inner nature, you know where you belong. You also know where you don't belong" - Benjamin Hoff

 

[CCLINT]

zenenigma, Some how I read too fast and mis-understood what you wanted to do, so I am very sorry. Must have been too late here.
I thought the program you have in MS ACCESS was to be distributed.
***************************************
>Oh one more thing for CCLINT
ChiHappens, this (comment on distributing an MDB, ADO) I am very aware of, but I understood that the MS ACCESS app was to be distributed, and not just the MDB.
You should have just told me that I need to pay better attention and read the questions more thoroughly!

But, to your code, you do bot need to create an ADO object variable using DIM and then use CreateObject() method.
If you have already a reference to the "Microsoft ADO Object Library" under PROJECT|REFERENCES then you only need to do this:

Dim CN As ADODB.Connection
Dim RS As ADODB.RecordSet
Set CN = New ADODB.Connection
Set RS = New ADODB.RecordSet

Set CN = CreateObject("ADODB.Connection&quot
Set RS = CreateObject("ADODB.RecordSet&quot

And then when you are finished with the objects do this (also in this order):
If Not RS Is Nothing Then
If RS.State = adStateOpen Then RS.Close
Set RS = Nothing
End If

If Not CN Is Nothing Then
If CN.State = adStateOpen Then CN.Close
Set CN = Nothing
End If
Also, if you look closely, you will see you have dimmed a connection as CD but creating an Object using Cn.
***************************************
zenenigma, you may get by on the text file import simply with one insert (INSERT INTO or SELECT INTO - depending if the Mdb table already exist of not) statement. You will have to try it to see if the delimited text file is in a recognizable format (if not, but it is indeed delimited, you can get around this by using a Schema.ini for JET, but then it may be easier to open the text file and loop through it as ChiHappens shows, or if you want to change the data prior to importing).

Dim CN As ADODB.Connection
Dim RS As ADODB.RecordSet
Set CN = New ADODB.Connection
Set RS = New ADODB.RecordSet

CN.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\MyDatabase.mdb;Persist Security Info=False"

Try these threads where I have posted examples:
thread709-292581 thread222-375073

 

[ChiHappens]

CCLINT,

Right you can use NEW keyword to instatiate a COM object.

However, some COM objects do not recognize the new keyword and MUST be instatiated using the CreateObject method.

Since I am a contract programmer, I tend to use the lowest common denominator and create all my object using CreateObject. In addition, I do a heck of a lot of ASP, where new does not work.

And besides what my point was in my post was that you should not use Dim CN As New ADODB.Connection. I guess I should have been more clear. lol

Oh and THANKS for pointing out the obvious, Dim CD as ADODB.Connection. lol You should have seen my first preview of the post, it said Dim CN As ADODB>RecordSet, then Dim CN As ADODB>Connetion. (I was tired, ok?)



Chi Happens
"When you know and respect your own inner nature, you know where you belong. You also know where you don't belong" - Benjamin Hoff