Adding records to MS Access table from command line

[WillChapman]

Hi,
I have text files that are created with comma-delimited records inside them. I am trying to figure out how I can construct a command-line method of importing the records to a MSA table. The end result will be that I can right-click on the .txt file, there will be an option on the context menu that says something like "Add records to 'Modules' table", and that will bring up a command window that will execute the import command. I have no problem with the context menu (and working with the registry), but I'm not very familiar with the macros, specifically - how would I transfer the file name from the command line to the macro in MSA? Would anyone know of a better method?

[TheSmileyCoder]

Im unsure how that would be done, but could you not just open a filebrowser from within access then select teh files to import? Or create a form showing the directory structure, with import buttons?

[WillChapman]

The text files are generated by another program, and they are constantly being created. I would like to be able to add the records from the text file without having to manually open MS Access.

[Broomberg]

I think I have a solution but it's a bit of a mess, and it only works if the incoming files are put into the same folder every time

Use a batch file to rename all files within the incoming directory to a standardized format.

Use the same batch file to open an access database where you have set the startup (under tools) to a form with a single field that has on Gotfocus set to run a macro. (I am sort of new to access and I am unsure if there is another way to run a macro on startup.)

Within the macro use the Transfertext action and in the settings use Import Delimited and set the file name to the standardized file name set with the batch file.

Add the Runcommand action with Exit as the command to your macro if you just want to import the data and nothing else.

You can now either manually move the imported files to an archive folder or add a line in the batch file to do so. (to avoid the macro pulling in old files)

I know this still opens access, but takes away the need to manually import the data and cleans up when it's done.

[WillChapman]

Ok. I understand what you are doing with the import text command with the macro, and there is a way to run it at startup from a command prompt. The only thing I am looking for is how to pass the name of the file into the macro, such as this manner:

c:\>"C:\Program Files\Microsoft Office\Office\msaccess.exe" "C:\Program Files\Microsoft Office\Office\samples\northwind.mdb" /X AddProducts

The above command looks for the msaccess.exe executable, then opens the northwind.mdb database and runs a macro (/X switch) called "AddProducts".
But what if I want to pass it a text field, such as one that happens to contain an absolute path to a file, such as "c:\temp\newdata.txt"? That way I could work with the file from within the macro.

[ADezii]

1. Create a Batch File that accepts a Replaceable Parameter representing the Path to the File.
2. Create an Environmental Variable equal to the Replaceable Parameter.
3. Within the Batch File, Open the Database within Microsoft Access and automatically execute the AddProducts Macro on Open.
4. The AddProducts Macro will execute a Public Function that will read the Value of the newly created Environmental Variable using Environ() Function.
5. Execute the Import Code via VBA now that you have the File's location.
6. Sample Batch File (Import.bat):
   

   @echo off
   cls
   Set FILE_PATH =  %1
   "C:\Program Files\Microsoft Office\Office10\Msaccess.exe" "C:\Program Files\Microsoft Office\Office10\Samples\Northwind.mdb" /X AddProducts

7. Batch File Execution Sample:
   

   Import C:\Stuff\MyFile.txt

8. This is only Theory and hasn't actually been tested, however, I see no reason whatsoever why it shouldn't work.

[NeoPa]

Invoking a Database From the Command Line may provide some general answers that could be used for this.

[WillChapman]

ADezii, That sounds like a great idea!
I think that will work, and I think it's what I've been waiting for. You really put some good ideas together with the environmental variable concept. I am absolutely blown away! Many thanks!

[NeoPa]

There is no need to create an Environment Variable to pass a value into an Access database instance. Check out the link in my last post for full details.

Alternatively, there's no reason it wouldn't work for you. Just not necessary really.

[WillChapman]

Thanks, NeoPa. I didn't see that part about passing in the variable.

[NeoPa]

Not a problem Will. We all have different sets of knowledge and experience. Also, there's probably a lot in there to take in with a quick browse.