* NEWBIE * Help needed

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Here is my scenario and objectives:

Scenario:
I have a dynamic number of log files with varying file names but of the same
general, textual format.
I create a database using ADOX via a VB.NET or C# app. (works fine...)
I programmtically create the DB log entry table in the new DB via the
OleDbCommand (ExecuteNonQuery method) (works fine...)

Primary Objective:
I need to programmtically import each log file's contents into my created
Access DB via my VB.NET or C# application.

Secondary Objective:
I need to handle log files with different textual formats (reform log
entries on-the-fly to conform to the required fields for the Access DB
import.)

I am a newbie as far as MS Access is concerned, so please provide code
examples if possible rather than just stating generally how to go about
meeting my objectives.

-Matt
 
Little confused here, you say you want to import "via my VB.NET or C#
application". If you will be doing the import from one of those apps, why do
you need Access help? It is only your data repository, right? Or, are you
asking how you can use Access to import the text files created via the other
apps?

Assuming the later, I will try to be more than general, but without more
detail, I can't be detail specific. Here are some things to look into that
may help. If you are familiar with VB, then it should not be much of a
scramble:

The method for importing or linking to text files is the TransferText method
of the DoCmd object. You can get the details on syntax and arguments in VBA
Help. One thing I will share with you is the use of an Import Specification.
An Import Specification gives the TransferText method information on the
format of the text file so it will import the way you want it. Finding where
to create a Spec, however, is not intuitive. So, to create one, do an import
manually so you will get to the ??? wizard. Here is the procedure from the
main toolbar:
File-->Get External Date-->Import
You will get an Import Dialog box. Change Files of Type to Text
Navigate to the file you want to import and Click Import
You will get the Import Text Wizard. On the lower left will be an
"Advanced" command button. Click it. Once there, you can set up all the
options you want. Then Click Save As. You will give it a unique name. This
name will be the SpecificationsName argument for your TransferText.

Now, you have two options after you select Get External Data - Import and Link
My preference is to Link. The reason being is you don't take up space in
your database and it reduces "bloat", when you import a file, manipulate it,
then delete it, the space does not go away until you do a Compact and Repair.
With a Link, you avoid this and you can still treat it like any other table.
My practice is to Link to the file, do whatever I need to append the data or
update the data in existing tables, then delete the link. For this you use
the DeleteObject method of the DoCmd object. This method does not delete an
external file, it only deletes the link to it.

Now, one more item. Finding the file you want. For that, I like the Common
Dialog box, but not the cranky, flakey ActiveX version. Here is a link to a
site where there is a very good API version:
http://www.mvps.org/access/api/api0001.htm

Well, I hope this will be enough to get you started. If you have more
questions, please post back.
 
I have the import spec setup (as described) completed. If possible, could
you show code in VB.NET or C# how I would programmatically perform the import
or linking?

-Matt
 
No, I can't. You might get a better answer in another news group for one of
those products. This site is for Access questions. I understand you are
using an Access database for your data, but the actual manipulation is in
something other than Access.
 
How about a code example on how to perform import or linking via a Access
module or macro? I can programmatically call a macro or module in .NET, but
I have no clue what to code the macro or module in Access. I guess the only
parameter I would need to pass-in is the name of the file to import or link.

-Matt
 
Since I don't use Macros, I will have to give you a VBA answer. It will
require a function that will need the following parameters:

1. full name and path of the file to import

2. type of import (See Help in the VBA Editor TransferText Method for types
of imports and values to pass) or if you want to always do it the same, you
can hard code it.

3. Name of the table you want to put the data in.

As to differing formats, If you import into a new table, that wont matter.
Access will create the the fields based on the text coming in. If you want
to use existing tables, then you would need another paramter to to tell it
which table to put the data in.

Public Function ImportTextFile(strFileName as String, lngImportType as Long,
& _
strTableName as String) as Long
'strFileName is full path and file name of file to import
'lngImportType is the Transfer type
'strTableName is table to import data into
'Returns 0 if successful or Access Error number if not

On Error Goto Err_ImportTextFile

DoCmd.TransferText lngImportType, ,strTableName, strFileName

Err_ImportTextFile:
ImportTextFile = err.number

End Function

There are a couple of other considerations, but I think if you read up on
the TransferText method, you can modify the code to get what you need.
 
I will try this out, thanks!

Klatuu said:
Since I don't use Macros, I will have to give you a VBA answer. It will
require a function that will need the following parameters:

1. full name and path of the file to import

2. type of import (See Help in the VBA Editor TransferText Method for types
of imports and values to pass) or if you want to always do it the same, you
can hard code it.

3. Name of the table you want to put the data in.

As to differing formats, If you import into a new table, that wont matter.
Access will create the the fields based on the text coming in. If you want
to use existing tables, then you would need another paramter to to tell it
which table to put the data in.

Public Function ImportTextFile(strFileName as String, lngImportType as Long,
& _
strTableName as String) as Long
'strFileName is full path and file name of file to import
'lngImportType is the Transfer type
'strTableName is table to import data into
'Returns 0 if successful or Access Error number if not

On Error Goto Err_ImportTextFile

DoCmd.TransferText lngImportType, ,strTableName, strFileName

Err_ImportTextFile:
ImportTextFile = err.number

End Function

There are a couple of other considerations, but I think if you read up on
the TransferText method, you can modify the code to get what you need.
Click to expand...
 
Back
Top