Importing to Access application by code

[JoeCruse]

Hello again,

I've done more searching on the next step of my instrument data project, which involves having a text file that is generated by a spectrometer's software automatically imported into my Access database tables.

I thought there might be a way to run code in a batch file to do the import, WITHOUT having to have the Access database ever opened. After doing some reading here and on other sites, it does NOT appear to be doable.

I plan on writing a batch file that opens an Access database. When this database opens, it will have a Startup form. This Startup form will have in its OnLoad or OnOpen property the code needed to make the text file import to my database tables happen. The code will then delete the text file and then shut the Access database back down again.

I plan on referencing this batch file in my spectrometer's software, which will make the software fire the batch file up every time it fires off a results text file, which I want captured in my Access stuff.

From what I've read, this seems to be the best way to automate the process, which is necessary, since I don't want an analyst manually importing every one of the 60+ analyses we run each day on this instrument.

My question to you good folks is: Does this sound like the way to go, or is there another way to run code to automatically import a text file into MS Access database tables?

Thanks again for your help. I really learned a lot from both Wayne's posts on my first issue, and the searches I've done here. Great site!

Joe

 

[JoeCruse]

Batch file funny

Hi,

Well, I'm almost finished with this project, but I ran into a WEIRD thing running the batch file.

cd
"C:\Databases\PW2400Import.mdb"

That's the line I use in the batch file to open my Access application that imports my text file. The code behind the startup form, which does the import, is set to close the text file, delete it, and then close itself. I get this error everytime I run it:

"Windows cannot find 'C:\Databases\PW2400Import.mdb'. Make sure you typed the name correctly, and then try again"

The really weird thing is, all the coded action in the little database takes place. I check, and the text file has been deleted. I check the database tables, and the desired data from the deleted text file WAS appended.

Would any of you nice folks know why Windows XP does this? Thank you,

Joe

 

[MarkK]

I experience this exact thing opening an .mdb file from a shortcut. Windows XP Pro, Access 2002. I just click OK, but in your case I guess it's mucking with your automation.
But if you can run a .bat file from other software, can't you just run your .mdb directly also?

 

[JoeCruse]

lagbolt,

thanks for the reply. We've never seen this error in opening an Access application on WIN XP, Access 2002 before. I tried making a shortcut on my desktop to open this application, and it opened fine.

I forgot to put

"C:\Program Files\Micrsosoft Office\Office 10\msaccess.exe"

in there, but when I did, I still got the same error. Funny that the batch file actually does open the Access application anyway, and that the only thing going on is the Windows error I posted above. It's almost as if the action is taking place faster than Windows can think, and so it does the action before it "sees" what it's doing.

I want to keep this in a batch file, because this is a repeptitive task (at least 60 times a day) that NEEDS automation. This part is the last step in making the whole shebang automated.

Anyone else catch something I'm not seeing? Is there a certain file I need to keep ANY batch file in? Here's the entire batch file:

REM Batch file to help automate import of PW2400 SuperQ data to the lab's LIMS
REM renames PW2400 .QAN files to .TXT files
REM
@ ECHO OFF
REM
REN *.qan SuperQ.txt
REM
REM Open the Access application that automatically imports the data into the lab LIMS
cd
"C:\Program Files\Micrsosoft Office\Office 10\msaccess.exe"
cd
"C:\Databases\PW2400Import.mdb"
REM
Exit

Thank you,

Joe

 

[ghudson]

Why do you need to use a batch file? You can program your db to do everything the batch file could do. I would avoid having to call DOS and do it all with VBA in your db. I can not give you any specifics for I do now know exactly why you are calling the batch file and everything that the batch file is trying to do.

 

[Sergeant]

I don't know why your batch file throws the error. It works fine when I try it.
This is a good point by Lagbolt:

But if you can run a .bat file from other software, can't you just run your .mdb directly also?

...And another good one from GHudson:

Why do you need to use a batch file? You can program your db to do everything the batch file could do. I would avoid having to call DOS and do it all with VBA in your db. I can not give you any specifics for I do now know exactly why you are calling the batch file and everything that the batch file is trying to do.

If you delete the files after import, there should only ever be one in the folder at any given time (I don't know how often the machine spits them out).
In any case, you could leave the db running with an OnTimer event like this:
(I just threw this together, first thing that came to mind and I'm sure it can be improved upon.)

Private Sub Form_Timer() 'set timer to x milliseconds accordingly
    Dim fs As New Scripting.FileSystemObject
    Dim fldr As Folder
    Dim f As File
    Set fldr = fs.GetFolder("G:\ImportFiles")
    If fldr.Files.Count > 0 Then

        For Each f In fldr.Files
            Name f As "G:\ImportFiles\SuperQ.txt"
        Next f
        For Each f In fldr.Files
            DoCmd.TransferText acImportDelim, _
            "SuperQIS", "SuperQ", f, vbNo 'or use your import sub here
            f.Delete
        Next f
    End If
    Set f = Nothing
    Set fldr = Nothing
    Set fs = Nothing
End Sub

 

[JoeCruse]

Sergeant and ghudson,

I thought to use the batch file to make the spectrometer software automatically trip the import sequence whenever it spit out a new results text file. We already use another batch file with the software to print a copy of the test file over our LAN to the production department every time it spits out a result.

I hadn't really considered keeping the import app open, but there is no reason NOT to add it to our Access LIMS, since we keep it open on the workstations most of the time anyway.

Doing that, I reckon the easiest thing might be to load a timer code behind the LIMS' switchboard form. Every so many seconds, it could check the results folder for the presence of a new file, and if it sees the file, then trigger open a form that, On Load, does the import and deletes the file.

I'll give that one a shot and see what happens. Since we go anywhere from 1 minute to 1 hour in between sample analyses, I'd probably need the timer code to fire every 30 seconds to check for thr presence of the text file. I'll report back how it goes.

Thanks folks!

 

[Sergeant]

Every so many seconds, it could check the results folder for the presence of a new file, and if it sees the file, then trigger open a form that, On Load, does the import and deletes the file.

Why do you need to open a form to do the import? Once you've imported a text file manually and saved the import spec, you can do it in one line of code.

Sarge.

 

[JoeCruse]

Sarge,

see

http://www.access-programmers.co.uk/forums/showthread.php?t=83493

The text file I have to import is not that easy to get the needed data from. With much help from Wayne, I have written the code to pull the data needed from the file, and it's not just one line. An import spec can't do it.

I guess I could load all the code in the switchboard On Timer, but it sure is a lot. May not make any difference to Access though.

Thanks again.

 

[Sergeant]

I see.

Sarge.

 

[JoeCruse]

Sarge,

I got the code set up on the form, and have been able to toss the batch file now, after running a few test result files. Everything is working quite well.

Many thanks to you, Sarge!

Joe

 

[Sergeant]

I'm glad you got it all working correctly. There's nothing like automation.
When I get something down to the "pushbutton" stage, I always start wondering why I have to push the button.

Sarge.

 

[JoeCruse]

You are right! Once I load up everything, we'll actually be better off than we were before. With what I've learned from this, it's time to hook a couple more instruments into this and quit with the manual data entry.

Thanks again to all,

Joe

 

[Pat Hartman]

I would add a safety net to handle the situation where the .mdb is not running for some reason and multiple files end up in the directory. Count the *.qan files and do something if more than one is found. I would copy the file to the standard name rather than renaming it and leave the original or move it to an archive directory. That way you can reimport a file if something went wrong. You will need a cleanup process to run weekly and delete all files older than a week.

 

[JoeCruse]

Pat,

not a bad idea to have a backup plan. The app is "supposed" to be running at all times on the spectrometer computer, but you never know what might take place on off shifts.

I worried at first about missing the import if the network was down (the backend is on the network, for me and other managers to get the data), but I just had the instrument software send the text file to a network file. That way, it won't give an error to deal with in Access, and the software will still keep chugging, but also let me know that a transmission was missed. If that happens, we can tell the software to transmit again when the network is up again.

Thanks again to all on the superb help given. This has helped me learn a good bit.

Joe

 

[acancel]

Opening a Secured Access database from a bat file

Hi All
I figured this was the best thread for this request. I am pretty new at bat files and I need some help. I am running a nightly mdb that refreshes our inventory. I need to have the database opened from a bat file so i can use the task scheduler. In addition, this database requires a user name and password to logon. Can someone please give me the needed code to create this bat file. This is what I have so far:

cd
"F:\Program Files\Microsoft Office\Office\MSACCESS.EXE"
cd
"C:\ARMS\ARMSDEV.mdb" /WRKGRP "C:\ARMS\Secured.mdw"

Your help is greatly appreciated
Anthony

 

[ghudson]

Welcome to the forum!

Try this [all on one line]...

"F:\Program Files\Microsoft Office\Office\MSACCESS.EXE" "C:\ARMS\ARMSDEV.mdb" /WRKGRP "C:\ARMS\Secured.mdw" /user UserName /pwd YourPassword

Using the START command is another DOS command...

START "open db" "C:\ARMS\ARMSDEV.mdb" /WRKGRP "C:\ARMS\Secured.mdw" /user UserName /pwd YourPassword

Note: You must use quotes if the path string contains any spaces. When using the quotes you have to add a program title value [I used "open db"] for Windows XP/NT computers when using the START command.

All of the above is untested.