Programmatically import text file into Access 2

[brainmetz]

Like the title says i need to programmatically import a text file into access. I found that I can do this from File->Get External Data->Import. I also found the macro code for transfer text. Transfer text would work except that my file name that I import from changes. For instance the name of the file has a date appended to it. This name changes all the time...So I wanted to know if anyone knew how to bring up a browse window to select the file name. I would like it to work just like the import feature from the file menu. If it is possible to just write a macro/module to bring up that menu it would be great.

Thanks for everything,

Shane

P.S. I just wanted to thank everyone that has replied to all of my silly/stupid questions. I have learned alot.

Thanks again.

 

[FancyPrairie]

Insert the ActiveX control Common Dialog Controls. Then right click on the control, select properties, and fill them in. Then place a command button on the form and in OnClick event of the button, put something like this in there (assume dlg is the name of the ActiveX control)

dlgBrowse.ShowOpen

If (Len(dlgBrowse.FileName) > 0) Then _
txtFileName = dlgBrowse.FileName

 

[cruford]

You could add the Microsoft common dialog box. Set it on your form and set your filter(sets what files can be opened (*.txt, etc...) and your initdir (sets the initial opening directory). Code for the button to open the file might look like this:

dim strFileName as string ' you could make this global if need be
dlgCommon.ShowOpen
strFileName = dlgCommon.FileName


Now just reference strFileName in the rest of your code.

Hope this helps.

 

[brainmetz]

I wanted to thank both of you for your responses...They helped out a great deal...I now have the open dialog box, but how do I call the import function, so I can import the file that I can now select?

I need to import the file that is selected into table1...

Thanks,

Shane

 

[FancyPrairie]

Not copping out, but... The best way to explain it is to goto OnLine Help and check out the TransferText method.

 

[brainmetz]

First of all I dont think you are copping out...I believe in reading/doing research before asking also.

But, I guess I do not truly understand the TransferText method. I tried the TransferText but I had to specify an exact name. I tried to call the strFileName but it said that i must use an appropriate extension...I am really trying to understand all of this and I do appriciate your help.

Thanks

 

[FancyPrairie]

What is the value (contents) of strFileName?

 

[brainmetz]

Just so that everyone can see I am trying here is my code:

Dim strFileName As String ' you could make this global if need be
dlgCommon.ShowOpen
strFileName = dlgCommon.FileName

DoCmd.TransferText acImportDelim, , "Activity_File", strFileName


The DoCmd.TransferText is suppose to import the file...As I understand it:
expression.TransferText(TransferType, SpecificationName, TableName, FileName, HasFieldNames, HTMLTableName, CodePage)

TransferType:
acImportDelim -> Tells TransferText to import

SpecificationName:
-> Do not need one

TableName:
"Activity_File" -> Name of my table, unsure if it needs to be in quotes or not.

FileName:
strFileName -> Variable of file I wish to import.

The rest of the fields are not needed...Is this correct...What am I missing? It does not import.

Thanks

Shane

 

[brainmetz]

The contents of strFileName is:

C:\Program Files\mydirectory\export\activity\myfile.txt

myfile.txt is the file I wish to import...

It is reading it in correctly.

Thanks for everything

 

[brainmetz]

The error I recieve is:

Run-time error 3066
Query must have at least one destination field.

BTW...I don't have a query named Activity_File just a table.

 

[brainmetz]

if I put strFileName in quotes, I recieve:

you can not import this file.

Thanks.