Executing and external file. 1

jtlawn · Mar 23, 2000

I am a new user to ACCESS, I am using ACCESS 2000. I want to be able to execute and external file from a button on a form. Our Company was using Approach and just recently swithced, in Approach I had to create a package in an object, I can do this in ACCESS also, but I need two buttons one execute the object and one to import the data. Can't I code something in VBA that will execute an external file and then import the results to a table. HELP!!!! <a href=mailto:jtlawn@hotmail.com>jtlawn@hotmail.com</a> <a href= > </a>

famousb · Mar 23, 2000

What kind of external file are you trying to execute? 
 
this is an example of how to open a say Word, and then a certain file in Word. 
 
Dim WordPath, DocPath As String 
Dim Retval As Variant 
WordPath = "C:\Program Files\Microsoft Office\Office\WinWord.exe" 
DocPath = "c:\My Documents\file1.doc" 
Retval = Shell(WordPath & " " & DocPath, vbMinimizedFocus) 
 
if you give more specifics i may be able to help customize this statement for you. 
 
And then you can put a statement such as this in the same VBA (so you only need one button) to import the data. 
 
 
Dim Tablpath As String 
Dim DocPath2 As String 
Dim FileDef As String 
 
Tablpath = "the table to get the data" 
DocPath2 = "the file that was just created" 
FileDef = "the specs for the file" 
'you have to create and save these through the import wizard 
DoCmd.TransferText acImportFixed, FileDef, TablPath, DocPath2, 0 
 
all of this is dependant on what kind of executable you want to do, and what you want to import, but your theory is very possible. 
 
Brian Famous <a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a> <a href= > </a>

jtlawn · Mar 24, 2000

I am trying to execute a procedure out of a software product called 'application systems' or 'AS'. We are using their command client piece to extract data from our mainframe to a PC. I am using AS, because it gives me the connectivity to the mainframe and allows me to write my code directly into it. That piece works and I drop the file on my PC into a CSV file, which I then can import into ACCESS. What I would like to be able to do is, put a button on a form or switchboad, that will download the file and import the file into ACCESS. We are using IBM personal communication 4.2 & 4.3 as our communication software. <a href=mailto:jtlawn@hotmail.com>jtlawn@hotmail.com</a> <a href= > </a>

famousb · Mar 24, 2000

alright, so CSV is a database file? 
what you would need to do is use the 
 
DoCmd.TransferDatabase 
 
command, and then define the database type as CSV, and then the path and file names, and the table to import it into. 
CSV may not be a regularly recognized database type on your Access version, and would require a quick reinstall in order to be able to import that type of file (you jsut have to specify this part during the installment process). 
 
the procedure is relatively simple, you just create the button and put the DoCmd in the Click procedure for teh button. Brian Famous <a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a> <a href= > </a>

jtlawn · Mar 24, 2000

Okay, I think I can do the import, but I am still not sure of the download part: 
 
Are you saying: DoCmd.TransferDatabase=C:\DAS\WORK\PGM1.as 
 
Where PGM1.as is the name of the job that will actually run the job that will download the file for me. 
 
Thanks <a href=mailto:jtlawn@hotmail.com>jtlawn@hotmail.com</a> <a href= > </a>

DougP · Mar 24, 2000

If you want to run a program from Access then look at the Shell statement 
x = Shell("C:\DAS\WORK\PGM1.as",1) 
DoCmd.TransferText acImportFixed, "Specname", "YourTable", .CSV_Name, True 
 
And to Import it as "famousb" said use Docmd.Tranfer... 
Type in Docmd. and you will see a list of items it can do press TR and you will see transfer Press the Space bar to grab it. 
Then double click on Tranfer.. and press the F1 key to bring up help. Then you can investigate all of it parameters to see how to use it. 
 
I would Import it manually then create a "Specification" and Save it to use in your docmd. 
 
 
 DougP <a href=mailto: dposton@universal1.com> dposton@universal1.com</a> <a href= > </a>

famousb · Mar 24, 2000

actually, the code would look like this for your example: 
 
Dim JobPath As String 
Dim Retval As Variant 
JobPath = "C:\DAS\WORK\PGM1.as" 
Retval = Shell(JobPath) 
 
DoCmd.TransferDatabase acImport, "CSV", "C:\DAS\WORK\????", acTable, "Source Table Name","Destination Table Name", 0 
 
"CSV" describes the type of database you care to import (that's what you described earlier, and i said you may have to reinstall access for) 
"C:\DAS\WORK\????" is your database path and name that was created by PGM1.as 
acTable is saying that you want to import a table 
Then enter the "source table name" (from your database) 
Then enter the "destination table name" (in your Access db) 
 
 
Brian Famous <a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a> <a href= > </a>

jtlawn · Apr 14, 2000

I have been able to run the executable, but now my problem is with trying to import the CSV file.  Would I be better off having my executable create an ODBC file instead? If so how would that code look and how do I actually make set the ODBC connection.  I have tried, but I am not sure what exactly I should be doing? WE have always been a mainframe shop, so this whole process is new. The executable in the first step is calling a program that will execute on the host and create a file on my PC, that is why I am wondering what file type would be best for me to create. Thank You.   <a href=mailto:jtlawn@hotmail.com>jtlawn@hotmail.com</a> <a href= > </a>

famousb · Apr 14, 2000

ok, refresh and update me as to your situation. you get the executable to work and it creates the CSV file automatically, and this works just fine, correct? now you are having trouble importing the CSV into Access using the TransferDatabase procedure, look in a Macro, and check to see if CSV is a type of database that Access recognizes for your installation. if not, you can either re-install as per above, or you could create a .txt file (if your other program can do that) and import that inot Access.  there are actually many options for you. Brian Famous <a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a> <a href= > </a>

jtlawn · Apr 17, 2000

I was able to get the code to finally work.  I was using transferDatabase and I needed to use transferText.  I also needed to use use a command line string to first execute the software, then execute the specific job.  Here is a sample of the code.  Thank You for all your help: Dim Policy As String Dim JobPath As String Dim Retval As Variant JobPath = "D:\das\daszcomm.exe D:\das\work\agtphone.as /q" Retval = Shell(JobPath) DoCmd.DeleteObject acTable, "Agtphone" DoCmd.TransferText acImportDelim, "", "agtphone", "D:\das\work\agtphone.txt", True, "" <a href=mailto:jtlawn@hotmail.com>jtlawn@hotmail.com</a> <a href= > </a>

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Executing and external file. 1

jtlawn

Programmer

famousb

Programmer

jtlawn

Programmer

famousb

Programmer

jtlawn

Programmer

DougP

MIS

famousb

Programmer

jtlawn

Programmer

famousb

Programmer

jtlawn

Programmer

Similar threads

Part and Inventory Search

Sponsor